May 19, 2015 at 1:11 pm
Good day,
I need some with selecting the number of days, in a month, between a date range. For example, my data looks like:
FileNumb | startdate | enddate
1 04/25/2015 05/02/2015
2 05/01/2015 05/10/2015
The output I am looking for would be:
FileNumb | Year | Month | Days
1 2015 4 6
1 2015 5 2
2 2015 5 10
My SQL skills fall short so I greatly appreciate any help. Thank you in advance!!
May 19, 2015 at 2:19 pm
You could create a function that uses a calendar table.
Here's an example.
CREATE FUNCTION DaysBetweenInMonths(
@StartDate date,
@EndDate date
)RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
WITH E AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E(n)
),
cteCalendar AS(
SELECT TOP(DATEDIFF(dd, @StartDate, @EndDate) + 1) --Return only the needed rows
DATEADD( dd, ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) - 1, @StartDate) calDate --Create the dates starting from @StartDate
FROM E a, E b, E c -- Cross join to generate 1000 rows, enough for about 3 years
)
SELECT YEAR( calDate) AS Year,
MONTH( calDate) AS Month,
COUNT(*) AS Days
FROM cteCalendar
GROUP BY YEAR( calDate),
MONTH( calDate);
GO
DECLARE @StartDate date = '20150425',
@EndDate date = '20150502';
SELECT *
FROM DaysBetweenInMonths(@StartDate, @EndDate);
GO
DROP FUNCTION DaysBetweenInMonths;
Please analyse this code and ask any questions that you have.
May 19, 2015 at 2:22 pm
And an example on how to use it with a table.
DECLARE @Sample TABLE(
FileNumb int,
startdate date,
enddate date);
INSERT INTO @Sample
VALUES
(1, '04/25/2015', '05/02/2015'),
(2, '05/01/2015', '05/10/2015');
SELECT s.FileNumb,
d.Year,
d.Month,
d.Days
FROM @Sample s
CROSS APPLY DaysBetweenInMonths(startdate, EndDate) d;
May 19, 2015 at 3:11 pm
I don't like table overhead when it can be avoided by simple mathematical calcs. Calendar tables have their uses, but to me they can sometimes become the proverbial hammer looking for a nail, i.e., trying to use a calendar table to solve things they aren't best suited or most efficient for. And, rarely, calendar tables get human-induced errors whereas calendar computations do not.
I've referenced a "tally" table below: that's a "standard" table of sequential numbers from 0 to however high you want to go (1 million is typical). You can also use a CTE to generate an in-line table, as in the solution code.
DECLARE @Sample TABLE(
FileNumb int,
startdate date,
enddate date);
INSERT INTO @Sample
VALUES
(1, '04/25/2015', '05/02/2015'),
(2, '05/01/2015', '05/10/2015'),
(3, '02/07/2015', '07/14/2015')
SELECT s.FileNumb, YEAR(aan.this_rows_month) AS Year, MONTH(aan.this_rows_month) AS Month,
CASE WHEN t.tally = DATEDIFF(MONTH, s.startdate, s.enddate) --last month
THEN CASE WHEN t.tally = 0 THEN DATEDIFF(DAY, s.startdate, s.enddate) + 1 ELSE DAY(s.enddate) END
WHEN t.tally = 0 --first month
THEN DATEDIFF(DAY, s.startdate, DATEADD(MONTH, DATEDIFF(MONTH, 0, this_rows_month) + 1, 0))
ELSE DATEDIFF(DAY, this_rows_month, DATEADD(MONTH, 1, this_rows_month)) --inbetween month(s)
END AS Days
FROM @Sample s
INNER JOIN dbo.tally t ON
t.tally BETWEEN 0 AND DATEDIFF(MONTH, s.startdate, s.enddate)
CROSS APPLY (
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, t.tally, startdate)), 0) AS this_rows_month
) AS aan --assign_alias_names
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 19, 2015 at 3:20 pm
Hi Luis,
Thank you for the reply - and all your work. Unfortunately, it appears I do not have permission to create functions. I apologize, if I would have known, I would have stated this limitation in my original post. Hopefully I did not waste your time.
May 19, 2015 at 3:25 pm
Hi Scott,
When I try to run your code, I get the following error: Invalid object name 'dbo.tally'. Is there anything else I need to do that I might be missing?
Thank you for your help.
May 19, 2015 at 3:37 pm
Edit: Was able to post an inline CTE this time; guess they fixed the bug at work that was blocking posting any CTEs ... Hallelujah!
DECLARE @Sample TABLE(
FileNumb int,
startdate date,
enddate date);
INSERT INTO @Sample
VALUES
(1, '04/25/2015', '05/02/2015'),
(2, '05/01/2015', '05/10/2015'),
(3, '02/07/2015', '07/14/2015');
WITH
cteTally10 AS (
SELECT 0 AS tally UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
),
cteTally100 AS (
SELECT 1 AS tally
FROM cteTally10 c1
CROSS JOIN cteTally10 c2
),
cteTally10K AS (
SELECT ROW_NUMBER() OVER(ORDER BY c1.tally) AS tally
FROM cteTally100 c1
CROSS JOIN cteTally100 c2
)
SELECT s.FileNumb, YEAR(aan.this_rows_month) AS Year, MONTH(aan.this_rows_month) AS Month,
CASE WHEN t.tally = DATEDIFF(MONTH, s.startdate, s.enddate) --end month
THEN CASE WHEN t.tally = 0 THEN DATEDIFF(DAY, s.startdate, s.enddate) + 1 ELSE DAY(s.enddate) END
WHEN t.tally = 0 --start month
THEN DATEDIFF(DAY, s.startdate, DATEADD(MONTH, DATEDIFF(MONTH, 0, this_rows_month) + 1, 0))
ELSE DATEDIFF(DAY, this_rows_month, DATEADD(MONTH, 1, this_rows_month)) --inbetween month(s)
END AS Days
FROM @Sample s
INNER JOIN cteTally10K t ON
t.tally BETWEEN 0 AND DATEDIFF(MONTH, s.startdate, s.enddate)
CROSS APPLY (
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, t.tally, startdate)), 0) AS this_rows_month
) AS aan --assign_alias_names
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 19, 2015 at 3:52 pm
CORRECTION: Previous code has bug because the tally table does not include a 0.
DECLARE @Sample TABLE(
FileNumb int,
startdate date,
enddate date);
INSERT INTO @Sample
VALUES
(1, '04/25/2015', '05/02/2015'),
(2, '05/01/2015', '05/10/2015'),
(3, '02/07/2015', '07/14/2015');
WITH
cteTally10 AS (
SELECT 0 AS tally UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
),
cteTally100 AS (
SELECT 1 AS tally
FROM cteTally10 c1
CROSS JOIN cteTally10 c2
),
cteTally10K AS (
SELECT ROW_NUMBER() OVER(ORDER BY c1.tally) - 1 AS tally
FROM cteTally100 c1
CROSS JOIN cteTally100 c2
)
SELECT s.FileNumb, YEAR(aan.this_rows_month) AS Year, MONTH(aan.this_rows_month) AS Month,
CASE WHEN t.tally = DATEDIFF(MONTH, s.startdate, s.enddate) --end month
THEN CASE WHEN t.tally = 0 THEN DATEDIFF(DAY, s.startdate, s.enddate) + 1 ELSE DAY(s.enddate) END
WHEN t.tally = 0 --start month
THEN DATEDIFF(DAY, s.startdate, DATEADD(MONTH, DATEDIFF(MONTH, 0, this_rows_month) + 1, 0))
ELSE DATEDIFF(DAY, this_rows_month, DATEADD(MONTH, 1, this_rows_month)) --inbetween month(s)
END AS Days
FROM @Sample s
INNER JOIN cteTally10K t ON
t.tally BETWEEN 0 AND DATEDIFF(MONTH, s.startdate, s.enddate)
CROSS APPLY (
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, t.tally, startdate)), 0) AS this_rows_month
) AS aan --assign_alias_names
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 19, 2015 at 3:59 pm
Appears to work! Thank you so much. Truly appreciate your help.
May 19, 2015 at 9:17 pm
Many ways to do this, some of which may be a little more obvious than others. Here's another:
DECLARE @Sample TABLE(
FileNumb int,
startdate date,
enddate date);
INSERT INTO @Sample
VALUES
(1, '04/25/2015', '05/02/2015'),
(2, '05/01/2015', '05/10/2015'),
(3, '03/31/2015', '05/01/2015'),
(4, '02/29/2004', '03/01/2004');
WITH Months (n) AS
(
SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a (n)
CROSS JOIN (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b (n)
)
SELECT FileNumb, [Year]=YEAR(d), [Month]=MONTH(d)
,[Days] = 1 + DATEDIFF(day
,CASE WHEN startdate >= sm AND enddate <= em THEN startdate
WHEN startdate >= sm THEN startdate
ELSE sm
END
,CASE
WHEN startdate >= sm AND enddate <= em THEN enddate
WHEN enddate <= em THEN enddate
ELSE em
END
)
,d, sm, em -- intermediate results for show
FROM @Sample a
CROSS APPLY -- CA needs to only return sm and em
(
SELECT d = DATEADD(month, b.n, startdate) -- Just a date (not really needed but used below)
-- Start of the month generated in this CA
,sm = DATEADD(month, DATEDIFF(month, 0, DATEADD(month, b.n, startdate)), 0)
-- End of the month generated in this CA
,em = DATEADD(month, 1, DATEADD(month, DATEDIFF(month, 0, DATEADD(month, b.n, startdate)), 0))-1
FROM Months b
WHERE b.n <= DATEDIFF(month, startdate, enddate)
) b
ORDER BY FileNumb, d;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 21, 2015 at 2:56 pm
Hi Dwain. I tried your solution and it works very well, and fast. Thank you for posting it.
I have learned a lot this week. Again, thank you to everyone who helped.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply