Date Range to Actual Dates by Month

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

Transact-SQL (2000)

Date Range to Actual Dates by Month

Author

Topic

ker9
Starting Member

2 Posts

Posted - 2011-02-15 : 13:29:19

Hi, I have employee, start date, end date, Hours. I need a query that will tell me how many of those hours are in each month Beginning 5/1/10 through 4/30/11. If the date range is within the same month, no problem. How do I pull the information out for those ranges that span multiple months? I would have to divide the hours by the number of days to get hours per day. I just don't know how to get to where I need to be.
Thank you.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-15 : 14:33:20

Can you provide few rows for the employees table as per specified structure and the desired output format according to that info?

ker9
Starting Member

2 Posts

Posted - 2011-02-15 : 14:49:29

emp_lastName sch_clientID sch_fromDateTime sch_toDateTime sch_hours sch_AddMode sch_Monday
TEST 1162443281 01/24/2011 01/28/2011 47 1 01/24/2011
TEST 1162436390 01/28/2011 01/28/2011 8 2 01/24/2011
TEST 1162411004 01/31/2011 02/04/2011 15 1 01/31/2011
TEST 28860 01/31/2011 02/04/2011 14 1 01/31/2011
TEST 29641 01/31/2011 02/04/2011 24 1 01/31/2011
TEST 1162436390 01/31/2011 01/31/2011 2 2 01/31/2011
TEST 1162438956 02/07/2011 02/11/2011 55 1 02/07/2011
TEST 1162438956 02/14/2011 02/18/2011 55 1 02/14/2011
TEST 1162410999 02/21/2011 02/25/2011 40 1 02/21/2011
TEST 2385 02/28/2011 03/04/2011 55 1 02/28/2011
TEST 2385 03/07/2011 03/11/2011 55 1 03/07/2011
TEST 2385 03/14/2011 03/18/2011 55 1 03/14/2011
TEST 1162436390 03/18/2011 03/18/2011 2 2 03/14/2011
TEST 2385 03/21/2011 03/25/2011 52 1 03/21/2011
TEST 1162436390 03/25/2011 03/25/2011 3 2 03/21/2011
TEST 2385 03/28/2011 04/01/2011 55 1 03/28/2011
TEST 27435 04/04/2011 04/08/2011 40 1 04/04/2011
TEST 211427 04/11/2011 04/15/2011 55 1 04/11/2011
TEST 211427 04/18/2011 04/22/2011 39 1 04/18/2011
TEST 1162416861 04/25/2011 04/29/2011 40 1 04/25/2011

TOTAL 711
Desired Results
Jan Feb Mar Apr Total
47 47
8 8
3 12 15
2.8 11 14
4.8 19 24
2 2
55 55
55 55
40 40
11 44 55
55 55
55 55
2 2
52 52
3 3
44 11 55
40 40
55 55
39 39
40 40

559 53 88 11 711

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources