November 16, 2006 at 11:11 pm
Hello, once again ijaz, sorry for disturbing again, but i need little bit help as i used ur query it reurns the result accurate but not 100 % , i think some problme exists with the time (midnigt time conflict some problem as i run ur query it displays following result ::
17/11/06 16/11/06 15/11/06 14/11/06 13/11/06 12/11/06 11/11/06
0 8 1 1 0 0 0
but accoding to my data, the date 16/11/06 returns 10 recors not 8 so plz check ur query n tell me where is something wrong
here is my Registrationdate column's data::
2006-11-16 12:42:36.000
2006-11-16 12:42:36.000
2006-11-16 12:42:36.000
2006-11-16 12:42:36.000
2006-11-16 12:42:36.000
2006-11-16 12:42:36.000
2006-11-16 12:42:36.000
2006-11-16 12:42:36.000
2006-11-16 15:04:48.997
2006-11-16 14:52:18.200
see the last 2 dates n time i think ur query not support this time plz tell me how i get these ones also in my query result plz check this in ur query n reply me asap.
thanx in advance
November 17, 2006 at 2:12 am
Hi,
I have just login and see your message. I check the query and the problem with the group by class... you need to change the group by clause.
Here is query with changed group by clause
DECLARE @SQLHead VARCHAR(8000)
DECLARE @SQLBody VARCHAR(8000)
DECLARE @SQLFoot VARCHAR(8000)
DECLARE @SQLOut VARCHAR(8000)
DECLARE @LoopCounter INT
--===== Populate the variables using info from the table
SET @SQLHead = 'SELECT'
SET @LoopCounter = 0
WHILE @LoopCounter <=6
BEGIN
SELECT @SQLBody = ISNULL(@SQLBody+',','') + CHAR(13)
+ 'CASE WHEN CONVERT(varchar(10),RegistrationDate,112) = '''
+ CONVERT(varchar(10),dateadd(dd,-@LoopCounter,getdate()),112) + ''' THEN COUNT(RegistrationDate) ELSE 0 END AS Col'
+ CAST( @LoopCounter As Varchar)
SET @SQLOut = ISNULL(@SQLOut+',','') + CHAR(13)
+ ' MAX (Col'+ CAST( @LoopCounter As Varchar)+') AS ' + ''''+ CONVERT(varchar(10),dateadd(dd,-@LoopCounter,getdate()),103) +''''
SET @LoopCounter =@LoopCounter + 1
END
SELECT @SQLFoot = CHAR(13) + 'FROM Suppliers GROUP BY CONVERT(varchar(10),RegistrationDate,112)'
--===== Print the command we formed and execute it
SET @SQLOut = @SQLHead + @SQLOut + ' FROM ( ' + @SQLHead + +@SQLBody+@SQLFoot +') CrossTable'
PRINT @SQLOut
EXEC (@SQLOut)
cheers
cheers
November 17, 2006 at 10:56 pm
Heelo Ijaz, thankyou so much for ur reply,now its working fine, i mreally thankfull n greatfull to you.
Thanx again,
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply