SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Count Number of Days per month for a date range
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

GhostX1
Starting Member

6 Posts

Posted - 11/04/2013 :  10:41:20  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 11/04/2013 :  13:45:28  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
411 Posts

Posted - 11/04/2013 :  15:41:36  Show Profile  Reply with Quote


;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


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

Edited by - ScottPletcher on 11/06/2013 09:55:40
Go to Top of Page

GhostX1
Starting Member

6 Posts

Posted - 11/06/2013 :  05:31:02  Show Profile  Reply with Quote
Many thanks, works like a charm.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000