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.
Author |
Topic |
jmhc101
Starting Member
3 Posts |
Posted - 2013-01-08 : 12:05:31
|
I have a table of employees with start and end dates. The end date is null if the employee is active. I need to summarise this data to show me the number of active employees at a given point in the month (eg 1st).Source Data:employeeID, startDate, endDate1,20/12/2010,20/03/20112,17/01/2011,NULL3,01/02/2011,20/02/2011Desired output:Date, volumeActive01/01/2011,101/02/2011,301/03/2011,2What's the most efficient method of querying this data in TSQL? The source table is large (c.1m records). |
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2013-01-08 : 12:13:49
|
select *from tblwhere d >= startdate and (d < enddate or enddate is null)then you need the dateswith cte as(select d = convert(datetime,20110101')union allselect d=dateadd(mm,1,d) from cte where d < '20110103')select d, count(*)from tbljoin cteon d >= startdate and (d < enddate or enddate is null)group by d==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-08 : 23:46:06
|
use a calendar table likehttp://visakhm.blogspot.in/2010/02/generating-calendar-table.htmlthen do query likeSELECT f.[Date],t.cntFROM dbo,CalendarTable(@StartDate,@EndDate,0,1)fCROSS APPLY (SELECT COUNT(*) AS cnt FROM table WHERE f.[Date]>= startDate AND (f.[Date] <= endDate OR endDate IS NULL) )t ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
jmhc101
Starting Member
3 Posts |
Posted - 2013-01-18 : 08:37:10
|
Thanks - both solutions worked and I've learnt something new. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-18 : 13:09:58
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|