Count Number of Days per month for a date range

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)

Count Number of Days per month for a date range

Author

Topic

GhostX1
Starting Member

6 Posts

Posted - 2013-11-04 : 10:41:20

Hello

I have two dates in a record:

PKID, Start_Date, End_Date
999, 2011-03-05 00:00:00.000, 2011-04-07 00:00:00.000

I need to count how many days in each month for example need it to look like this:

PKID, MyMonth, Number_Of_Days
999, 03, 27
999, 04, 7

The Start_Date and End_Date can vary in range, up to a year. I thought of doing a nested loop, but just confused myself.

Any help would be most grateful.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-04 : 13:45:28

[code]
;With Numbers
AS
(
SELECT 1 AS N
UNION ALL
SELECT N + 1
FROM Numbers
WHERE N + 1 <= 1000
)
SELECT PKID,MONTH(DATEDIFF(dd,1,Start_Date)) AS MyMonth,COUNT(*) AS Number_OF_Days
FROM Table t
CROSS JOIN Numbers n
WHERE N BETWEEN 1 AND DATEDIFF(dd,Start_Date,End_Date)
GROUP BY PKID,MONTH(DATEDIFF(dd,1,Start_Date))
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-11-04 : 15:41:36

[code]

;WITH
cteDigits AS (
SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
SELECT [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS tally
FROM cteDigits [1s]
CROSS JOIN cteDigits [10s]
CROSS JOIN cteDigits [100s]
)
SELECT
PKID,
MONTH(DATEADD(MONTH, tally, Start_Date)) AS MyMonth,
DATEDIFF(DAY,
CASE WHEN tally = 0 THEN Start_Date ELSE DATEADD(MONTH, DATEDIFF(MONTH, 0, Start_Date) + tally, 0) END,
CASE WHEN End_Date < DATEADD(MONTH, DATEDIFF(MONTH, 0, Start_Date) + tally + 1, 0)
THEN DATEADD(DAY, 1, End_Date)
ELSE DATEADD(MONTH, DATEDIFF(MONTH, 0, Start_Date) + tally + 1, 0) END) AS Days
FROM (
SELECT 999 AS PKID, CAST('2011-03-05 00:00:00.000' AS datetime) AS Start_Date,
CAST('2011-04-07 00:00:00.000' AS datetime) AS End_Date UNION ALL
SELECT 1000, '20110416', '20120226'
) AS test_data
INNER JOIN cteTally t ON
t.tally BETWEEN 0 AND DATEDIFF(MONTH, Start_Date, End_Date)
ORDER BY
PKID, tally

[/code]
Edit: Added "<code>" and "</code>" tags to better format the code.

GhostX1
Starting Member

6 Posts

Posted - 2013-11-06 : 05:31:02

Many thanks, works like a charm.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources