Hi all I would like to write SQL command to display as below id Value Result 1 100 100 2 200 100 3 300 110 4 400 129 5 500 156.1 6 600 190.49 Formula for Results =100 for id=1 =100*0.1+100*0.9 for id=2 =200*0.1+100*0.9 for id=3 =300*0.1+110*0.9 for id=4 =400*0.1+...more >>

Here is the code: SELECT u.Username, a.Account, SUM(f.Quantity) AS Quantity, x.Name AS 'Exch', u.Name FROM xtUserExchange RIGHT OUTER JOIN tUser AS u ON xtUserExchange.UserId = u.UserId RIGHT OUTER JOIN tExchange AS x ON xtUserExchange.ExchangeId = x.ExchangeId LEFT OUTER JOIN tFill AS f IN...more >>

Hello, I have the following 3 tables: tbl_dett_can_all, OpenIntervals and tbl_stop_can_all. Each of them has two key fields - IBES_TICKER and ESTIM_ID. Now I have a query: SELECT COALESCE (T1.IBES_TICKER, O.IBES_TICKER) AS IBES_TICKER, COALESCE (T1.ESTIM_ID, O.ESTIM_ID) AS ESTIM_ID, ...more >>

SQL 2000 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ResponseDates]( [TestID] [int] NOT NULL, [NotificationDate] [smalldatetime] NOT NULL, [ResponseDate] [smalldatetime] NOT NULL, CONSTRAINT [PK_ResponseDates] PRIMARY KEY CLUSTERED ( [TestID] ASC )WITH (P...more >>

I have a field in the database of varchar(30). It stores a psudo date format like: 15/09/2006 19.12 so DD/MM/YYYY 24HH.MM I want to update another field with this value and change it to MM/DD/ YYYY HH:MM:00 (AM/PM) I am willing to drop off the 24 hour part and just hard code AM or ...more >>

Hello, A simple view drawn from 3 tables has 10's of 1000's of rows with dates going back 5 years. 2-300 records are added per day. Generally the most recent records are the most popular on any given day. Rather than pulling data from thousands of records each time I'm considering using ...more >>

I have a text file our mainframe spits out 4 times a day. The file is a standard csv file that I can use the import wizard to pick up the file and upload it into my database table with no problems. I have a question though. Every time the file is uploaded from the mainframe, it is possible ...more >>

Hi, I have an orderid field is identity(identity seed:1) , how to get the inserted value in a store procedure? Thansk. ...more >>

CREATE TABLE [dbo].[TableA]( [ClickPositionKey] [int] NOT NULL, [ClickPosition] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [RowCreated] [smalldatetime] NULL, [RowModified] [smalldatetime] NOT NULL) GO Insert into TableA(clickpositionkey, clickposit...more >>

I have a query above my expertise level that I would appreciate some assistance with. I need a list of unique names from all records where the name field is not unique. In other words, given a set of records where some [Name] fields contain unique data and some do not, I need a list of u...more >>

Background: I'm a beginner developer learning when to use the app and when to use the DB to execute what I need. I have two tables in separate databases with employee data. I need to set my asp.net application variable to today so a sp would run based on that. Question: The sp needs to check...more >>

I've been programming in C# for about 3 months now and I've completed a previous project that worked a lot with data, so I know the basics. This is what I need to do, try and see what you would do as an expert C# programmer. I have to make a paired sales analysis which takes the same home sol...more >>

When MSSQL is given a query such as select * from mytable order by col1, col2 on a table with more columns than col1 and col2, and having an index on col1, col2 an execution plan says a sort will be performed. However, given the query select col1, col2 from mytable order by col1, col2 the e...more >>

I am working in sql server 2000. I have tried to use the following sql in a stored procedure, the syntax checks OK but when I try to save I get an error message saying that I have to set the ANSI nulls and warning due to the fact that I am working with a remotely linked query: ************* ...more >>

Table 1: tankID classID freight Table 2: Freight1 Freight2 What I need is a statement to select T1.Freight =T2.Freight1 if T1.classID = 1 ELSE T1.Freight = T2.Freight2 Any help is greatly appreciated. Bill ...more >>

Hi, I need help formulating a query. The basic problem is that I have data in different rows that I need to combine. I'm trying to figure out the time required for different steps. For instance, in the table below: STEP TIME TYPE 1 12:30 start 1 2:00 end 2 3:00 sta...more >>

Hi, I found out in MSSQL\LOG folder, there are some trace files there to trace something like the object change, etc. When did all the trace files get created? is there anyway I can stop all kind of auto-trace? Thanks Ed...more >>

Is it possible to have a Stored Procedure with the WHERE clause built by the results of IF...ELSE statements? If so, how do you do that? Thank you...more >>

Hi I do have a severe problem in creating stored procedures containing local and linked (remote) servers. I have on my server created a linked server - say MylinkedServer. In a store procedure I have following: CREATE PROCEDURE sp_Test AS INSERT INTO MylinkedServer.testDB.dbo.Table1 ...more >>

I've got an SSIS package that takes data from a SS table, and sends it to an Excel destination. When it lands in Excel, the numeric data is being saved as text, which means the charts that I have pointing to those data areas are not showing any values. If the fields are ints in SS, why a...more >>

Hi I wish to translate a table values so that the columns can be the rows on a different table. For example as below: Table: - outcome, status, action abc Y abc Y def N results to be as : New Table:- description (new table co...more >>

There is a table with a lot of fields. An update query is written such as the following lines: update table1 set field1 = table2.field1, field2 = table2.field2, field3 = table2.field3, .. .. .. where sourcefile = 'filename.csv' and table1.field25 = table2.field25 Questions: 1)...more >>

I´m using ado to open connection do sql server and I need to open an exclusive connection. The adModeShareExclusive mode only works for access database. How can I open SqlServer in exclusive mode using visual basic 6?...more >>

Hi, Does anybody know why the REPLACE function is being used in this way? SET @file = REPLACE('C:\Temp\Whatever\Test.txt','/','-') <-- This line SET @bcp = 'bcp "SELECT Column FROM Table WHERE Value LIKE + ' + '''' + 'X%' + '''' + ' " queryout "' SET @bcp = @bcp + @file + '" -U user -P pass...more >>

Hello, Thanks for reviewing my question. I have not been SQL long and this is probably an easy question. I have the following data 1 A 1 B 1 C 2 A 2 B 3 C I would like to display the data in this fashison. 1 2 A A B B C C Can I do this in a simple select state...more >>

I have a query that is taking 9.5 million logical reads in asp, but 100 thousand Logical reads in mgmt studio. It takes 31 seconds in the asp page and .5 seconds in mgmt studio. It normally takes up to 3 seconds to run this sql. (Stored proc). We dropped and recreated it thinking it could h...more >>

Is there a way to specify a weight variable when using PROC SQL in SAS? -- Regards, Dave...more >>

Hi, I got an error on SQL 2000 server. Result: The task completed with an exit code of (12). What does "Exit Code of (12)" mean? Thank you, Michael ...more >>

I have a strange issue on SQL Server 2000.. I have a stored procedure with 5 input parameters and the query with in the body of stored proc uses the parameters and returns data. This stored procedure when executed with the parameters is taking around 45 minutes. If I take the body of sto...more >>

urls/ free downloadbable tutorials Hi, Can anybody send me some useful urls / free downloadable tutorials for learning and deveploment under following topics. (Both in Sql Server 2000/ 2005) Analysis Service (Dimension/ Cubes) MDX Query Reporting Services Regards Rahul ...more >>

Hi There, Is there any easy way to update the field with only the digits i've a table's field called x1 which varchar(50) alphanumeric, it contains abc123xy12z like that, i would like to remove all characters except digits, is it possble to do that in a single update statement. I need only ...more >>

Hello there I've entered to one of my databases about 500 MB Of data And delete it afterword. I made the same thing on Simple and full recovery model. In both of them in the insert the log file has grouth about 5 Mb On the insert part And when i made delete to all data the log file ...more >>

Hello , To read this newsgroup i use outlook express, is there better newsreader client you can recommend ? Thank you Bassam ...more >>

I have a few COUNT queries that i run across several tables eg select count(ID) from table1 where .... select count(ID) from table2 where .... select count(ID) from table3 where .... works fine, and the results are returned as 3 4 5 how can i make the results show as 3,4,5 ?...more >>

Hi All I am trying to out put results from a query formatted as HTML to a file but something seems to be truncating the data. A small bit of background on this is that I am not allowed to change any settings on the live server, I can not even create a View or stored procedure with out...more >>

Basics: the result of a sp is to be written to an xml-file. Integration Services is not an option...in this particular case. So I Google'd and stumpled upon the use of OSQL, but: EXEC master..xp_cmdshell "OSQL -E -S -Q 'SELECT AccessUserName,AccessUserEmail FROM dbo.AccessUser WHERE Acce...more >>