query problem

Question

Post reply

query problem

isa-369599

SSCommitted

Points: 1602
More actions
November 15, 2006 at 3:09 am

#92700

Hello everyone, i have a table in sqlserver 2000,
Suppliers:
SupplierID
SupplierName
Address
Login
Password
RegistrationDate
now i want to give any date then my query returns the data including of that date + last 6 days also
e.g,i give todays date 14/11/06, my query gives me how many suppliers added from last 6 days and included this date in this format:
9/11/06 10/11/06 11/11/06 12/11/06 13/11/06 14/11/06
20 5 8 8 9 10
plz tell me how to trach how many suppliers added in last six days + included given date in this format.
plz help me its very urgent for me .

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Sureshkumar Ramakrishnan Ten Centuries Points: 1200 More actions · Answer 1

If I have understood your question correctly,then summing the data would give u the desired results

Kindest Regards,

Sureshkumar Ramakrishnan

ijaz SSCommitted Points: 1912 More actions · Answer 2

Let me know that you need the query like cross tab for each date should be come the column and the count become the coulmn value

cheers

isa-369599 SSCommitted Points: 1602 More actions · Answer 3

yes ijaz u right , plz tell me the query how i get this ?

wait for reply?

ijaz SSCommitted Points: 1912 More actions · Answer 4

CREATE TABLE Suppliers (SupplierID Int, RegistrationDate Datetime)

INSERT INTO Suppliers(SupplierID,RegistrationDate)

SELECT 1, Getdate() UNION ALL

SELECT 2, Getdate() UNION ALL

SELECT 3, Getdate()-1 UNION ALL

SELECT 4, Getdate()-1 UNION ALL

SELECT 5, Getdate()-2 UNION ALL

SELECT 6, Getdate()-4

Go

DECLARE @SQLHead VARCHAR(8000)

DECLARE @SQLBody VARCHAR(8000)

DECLARE @SQLFoot 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 '

+''''+ CONVERT(varchar(10),dateadd(dd,-@LoopCounter,getdate()),103) +''''

SET @LoopCounter =@LoopCounter + 1

END

SELECT @SQLFoot = CHAR(13) + 'FROM Suppliers GROUP BY RegistrationDate'

--===== Print the command we formed and execute it

PRINT @SQLHead+@SQLBody+@SQLFoot

EXEC (@SQLHead+@SQLBody+@SQLFoot)

Here is the result.

15/11/2006 14/11/2006 13/11/2006 12/11/2006 11/11/2006 10/11/2006 09/11/2006

----------- ----------- ----------- ----------- ----------- ----------- -----------

0 0 0 0 1 0 0

0 0 1 0 0 0 0

0 2 0 0 0 0 0

2 0 0 0 0 0 0

Cheers

cheers

ijaz SSCommitted Points: 1912 More actions · Answer 5

If this slove your problem then cheers otherwise let me know what should need more

Thanks

cheers

isa-369599 SSCommitted Points: 1602 More actions · Answer 6

thanx ijaz, u got my point but not 100 % actually i already have a suppliers table, now i want to display how many suppliers login in 1 day .

as if i give todays date it give me the result of this day ke how many suppliers added in this date (check from registrationdate)+ tell me the this info for last 6 days also

my query return the above result in one row with column names of the dates of 7 days.

as i want to make this query for my graph. and shows in each day how many suppliers login to the system

hope u get my point now

wait for ur reply wiith some solution

thanx in advance.

ijaz SSCommitted Points: 1912 More actions · Answer 7

Hi,

I have create the table to test my query and you can use the table you already have. There is no need to change the the query. If you want to make it generic just use your date parameter inplace of getdate function and the @LoopCounter value 6 replace you days parameter how many days you want to get the data out.

Here is the final Script. which give the result in one row as well

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 RegistrationDate'

--===== Print the command we formed and execute it

SET @SQLOut = @SQLHead + @SQLOut + ' FROM ( ' + @SQLHead + +@SQLBody+@SQLFoot +') CrossTable'

PRINT @SQLOut

EXEC (@SQLOut)

Cheers

cheers

isa-369599 SSCommitted Points: 1602 More actions · Answer 8

select top 1 (select count(*) from suppliers where RegistrationDate='12/11/2006') as '7',

(select count(*) from suppliers where RegistrationDate='11/11/2006') as '6',

(select count(*) from suppliers where RegistrationDate='10/11/2006') as '5',

(select count(*) from suppliers where RegistrationDate='9/11/2006') as '4',

(select count(*) from suppliers where RegistrationDate='8/11/2006') as '3',

(select count(*) from suppliers where Registrationdate='7/11/2006') as '2',

(select count(*) from suppliers where Registrationdate='6/11/2006') as '1'

from suppliers

plz used this query , and jahan where clause main main ne dates hud enter ki hain yahan per mujhe koi function bataye jo day minus ker ke leta rahe

as ur query is too much complex so me not used this u plz check this one and tell me jahan main ne dates hud di hain kisi tarah kisi func ke through woh dates ley but month and year will be same only days minus hon

wait for reply.

ijaz SSCommitted Points: 1912 More actions · Answer 9

I just say you cheers .... and plz have a proper english in your message it would be helpful for you as well to the person who answer you...

U can use the DATEADD function to get the date as in my query is used.. the only difference is that I have give you an example how to build the generic one and used not only for this problme but also for some other one. and there is nothing todo yourslef just spend some time on it it will improve you logic and understanding to TSQL as well

Cheers...

cheers

isa-369599 SSCommitted Points: 1602 More actions · Answer 10

Hi Ijaz, thanx for ur reply, actually at that time i m stucked in this problem and can't find any solution actually my dev is waiting for this SP so thats y i used the simple one ,You right 100 % ur logic is too good and now i check ur query as i m beginner so for me understand this is a big thing so plz dont mind it. and if u dont mind can u explain me a little bit abt this especially this line:

DateAdd()...

isa-369599 SSCommitted Points: 1602 More actions · Answer 11

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 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