Monthly grain to a daily grain

Question

Is there a way to easily go from a monthly grain to a daily grain? For example, here is a monthly grain table:

DECLARE @SessionsPerArea TABLE (idSession INT, startDate DATE)
INSERT  @SessionsPerArea VALUES
(1,'2013-01-31'),
(2,'2013-02-28'),
(3,'2013-03-31')

select *
from @SessionsPerArea

Result:

idSession   startDate
1   2013-01-01
2   2013-02-01
3   2013-03-01

I would like the result to be the following:

idSession   startDate
1   2013-01-01
1   2013-01-02
1   2013-01-03
.
.
1   2013-01-30
1   2013-01-31
2   2013-02-01
2   2013-02-01
2   2013-02-02
.
.
2   2013-02-27
2   2013-02-28
3   2013-03-01
3   2013-03-02
3   2013-03-03
.
.
3   2013-03-30
3   2013-03-31

It's not clear what you're asking for. Do you want to take the data in the `@SessionsPerArea` table, and expand it so that for each `idSession` there is one row for each day of the month that the `startDate` is in? Also are you assuming that `startDate` is always the first day of the month or how does one know what dates to include? — Adam Porad, Jun 30 '15 at 00:43
@AdamPorad Yes to your first question. About your second question, I made an update. I am assuming that I have idSession for every last day of a month. In other words, StartDate is always last day of a month. — Paul Kar., Jun 30 '15 at 00:48

score 3 · Accepted Answer · edited May 23 '17 at 12:30

I tested this example on SqlFiddle. It uses a recursive Common Table Expression (CTE) to create a temporary result set that has a record for each day of the month for a given idSession and startDate.

;WITH cteDateTable AS
(
    SELECT idSession, DATEADD(month, DATEDIFF(month, 0, MIN(startDate) OVER (PARTITION BY idSession)), 0) as StartDate, Max(startDate) OVER (PARTITION BY idSession) as EndDate
    FROM SessionsPerArea
    UNION ALL

    SELECT idSession, DATEADD(D, 1, StartDate), EndDate
    FROM cteDateTable
    WHERE DATEADD(D, 1, StartDate) <= EndDate
)
SELECT idSession, StartDate 
FROM cteDateTable
ORDER BY idSession, StartDate

I got some help from these other SO answers (as well as others I browsed).

How can I select the first day of a month in SQL? Answer by LukeH
Get all dates in date range in SQL Server Answer by Joe Daley

score 0 · Answer 2 · edited Nov 13 '18 at 23:13

It easy and efficient if you use a table of numbers. Here is a good article describing how to generate it.

For this example I populate it on the fly, but in production I have a permanent table with 100K rows. It is useful in many reports.

DECLARE @Numbers TABLE (Number int PRIMARY KEY);
INSERT INTO @Numbers (Number)
SELECT TOP(100) 
ROW_NUMBER() OVER(ORDER BY sys.all_objects.object_id) AS Number
FROM sys.all_objects;

Now for each session we return all days of the month using CROSS APPLY. This solution returns all days of the month on or before the startDate. If your startDate is the last day of the month (as you said it is), then there will be a row for each day of the month.

SQLFiddle

DECLARE @SessionsPerArea TABLE (idSession INT, startDate DATE)
INSERT  @SessionsPerArea VALUES
(1,'2013-01-31'),
(2,'2013-02-28'),
(3,'2013-03-31')

SELECT *
FROM 
    @SessionsPerArea AS S
    CROSS APPLY
    (
        SELECT DATEADD(day, 1-N.Number, S.startDate) AS NewDate
        FROM @Numbers AS N
        WHERE N.Number <= DAY(S.startDate)
    ) AS CA
ORDER BY S.idSession, NewDate;

Result set

idSession    startDate     NewDate
1            2013-01-31    2013-01-01
1            2013-01-31    2013-01-02
1            2013-01-31    2013-01-03
1            2013-01-31    2013-01-04
1            2013-01-31    2013-01-05
1            2013-01-31    2013-01-06
1            2013-01-31    2013-01-07
1            2013-01-31    2013-01-08
1            2013-01-31    2013-01-09
1            2013-01-31    2013-01-10
1            2013-01-31    2013-01-11
1            2013-01-31    2013-01-12
1            2013-01-31    2013-01-13
1            2013-01-31    2013-01-14
1            2013-01-31    2013-01-15
1            2013-01-31    2013-01-16
1            2013-01-31    2013-01-17
1            2013-01-31    2013-01-18
1            2013-01-31    2013-01-19
1            2013-01-31    2013-01-20
1            2013-01-31    2013-01-21
1            2013-01-31    2013-01-22
1            2013-01-31    2013-01-23
1            2013-01-31    2013-01-24
1            2013-01-31    2013-01-25
1            2013-01-31    2013-01-26
1            2013-01-31    2013-01-27
1            2013-01-31    2013-01-28
1            2013-01-31    2013-01-29
1            2013-01-31    2013-01-30
1            2013-01-31    2013-01-31
2            2013-02-28    2013-02-01
2            2013-02-28    2013-02-02
2            2013-02-28    2013-02-03
2            2013-02-28    2013-02-04
2            2013-02-28    2013-02-05
2            2013-02-28    2013-02-06
2            2013-02-28    2013-02-07
2            2013-02-28    2013-02-08
2            2013-02-28    2013-02-09
2            2013-02-28    2013-02-10
2            2013-02-28    2013-02-11
2            2013-02-28    2013-02-12
2            2013-02-28    2013-02-13
2            2013-02-28    2013-02-14
2            2013-02-28    2013-02-15
2            2013-02-28    2013-02-16
2            2013-02-28    2013-02-17
2            2013-02-28    2013-02-18
2            2013-02-28    2013-02-19
2            2013-02-28    2013-02-20
2            2013-02-28    2013-02-21
2            2013-02-28    2013-02-22
2            2013-02-28    2013-02-23
2            2013-02-28    2013-02-24
2            2013-02-28    2013-02-25
2            2013-02-28    2013-02-26
2            2013-02-28    2013-02-27
2            2013-02-28    2013-02-28
3            2013-03-31    2013-03-01
3            2013-03-31    2013-03-02
3            2013-03-31    2013-03-03
3            2013-03-31    2013-03-04
3            2013-03-31    2013-03-05
3            2013-03-31    2013-03-06
3            2013-03-31    2013-03-07
3            2013-03-31    2013-03-08
3            2013-03-31    2013-03-09
3            2013-03-31    2013-03-10
3            2013-03-31    2013-03-11
3            2013-03-31    2013-03-12
3            2013-03-31    2013-03-13
3            2013-03-31    2013-03-14
3            2013-03-31    2013-03-15
3            2013-03-31    2013-03-16
3            2013-03-31    2013-03-17
3            2013-03-31    2013-03-18
3            2013-03-31    2013-03-19
3            2013-03-31    2013-03-20
3            2013-03-31    2013-03-21
3            2013-03-31    2013-03-22
3            2013-03-31    2013-03-23
3            2013-03-31    2013-03-24
3            2013-03-31    2013-03-25
3            2013-03-31    2013-03-26
3            2013-03-31    2013-03-27
3            2013-03-31    2013-03-28
3            2013-03-31    2013-03-29
3            2013-03-31    2013-03-30
3            2013-03-31    2013-03-31

Thank you for your reply Vladimir. To be honest I really liked your answer and explanation, but I prefer CTE on cross apply. That's the only reason I picked the other answer. Ny vse ravno spasibo. :) — Paul Kar., Jun 30 '15 at 14:33
No problem. I guess your data set is small, so performance-wise any solution would be OK. — Vladimir Baranov, Jun 30 '15 at 22:45

Monthly grain to a daily grain

2 Answers2