query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

query help

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-10-10 : 15:12:47

I have a table that has

registrants
name
startdate
enddate

now I want to take in a month and year
and say for September 2011

loop through the whole month and list each day and for each day which users were registered on that day (so any user that startdate was before or equal to date and end date = to or after date)

so my final shouls show for example

septemer 1 user 1
september 2 user 1
september 2 user2
september 2 user 3
september 4 user 1

duplicate rows for each date based on the users for that date.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-10 : 15:49:16

can you change your inputs to a datetime value representing the any day within your target month? If so, this would work:


declare @startDate datetime
set @startDate = '2011-09-01'

select DATENAME(month, calender.dt)
       ,datepart(day, calender.dt)
       ,yt.name
from   (
       select DATEADD(day, number, @startDate) dt
       from   master..spt_values
       where  type = 'P'
       and    number < 31
       and    datediff(month,@startDate, dateadd(day, number, @startdate)) = 0
       ) calender 
JOIN   YourTable yt
       on yt.startDate <= calender.dt
       and yt.enddate >= calender.dt

This uses an adhoc calender derived table. If you want the Cadillac of calender functions:
Date Table Function F_TABLE_DATE from MVJ would certainly do the trick.

Be One with the Optimizer
TG

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-10-11 : 04:45:41

thanks so much for your help

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-10-11 : 08:46:01

you're welcome.

Be One with the Optimizer
TG

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-10-24 : 14:40:20

Maybe you can help me
i'm trying to do a similiar query where I want
a list of all dates of the month and for each date to count different criteria from the registrant table

so to show

september 1 count(registrants) count registrants where country is US count where country is UK etc.
september 2
sepetember 3

what's the best way to do this

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources