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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
52326 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
Aged Yak Warrior

USA
550 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  
 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.07 seconds. Powered By: Snitz Forums 2000