Do while in do while SQL Server

Question

I just create a schedule project and need help..

I have a schedule data as

ID      Programme        Start      End         Division
----------------------------------------------------------
M001    Math             1/1/2017   20/01/2017  Math
M002    Aljabar          2/2/2017   20/02/2017  Math
E001    Conversation     3/1/2017   25/01/2017  English
E002    Vocabs           3/1/2017   20/02/2017  English

I need to make it look like this:

ID        Date     
------------------
M001     1/1/2017
M001     2/1/2017
M001     3/1/2017
so on until 20/1/2017
M002     2/2/2017
M002     3/2/2017
so on until 20/02/2017
E001     3/1/2017
E001     4/1/2017
so on until 25/01/2017
E002     3/1/2017
E002     4/1/2017
so on until 20/02/2017

Thanks

Search for "sql server expand date range". This question has been asked and answered a thousand times. One of the results: https://stackoverflow.com/questions/26910398/expand-from-to-date-columns-to-1-row-per-day-within-that-range — Alex, Jul 17 '17 at 02:34
Possible duplicate of [Expand "From" & "To" date columns to 1 row per day within that range](https://stackoverflow.com/questions/26910398/expand-from-to-date-columns-to-1-row-per-day-within-that-range) — Alex, Jul 17 '17 at 02:35

Gordon Linoff · Answer 1 · 2017-07-18T11:22:57.377

3

One way to do this is with a recursive CTE:

with cte as (
      select id, start as dte, end
      from t
      union all
      select id, dateadd(day, 1, dte), end
      from t
      where dte < end
     )
select id, dte
from cte
order by id, dte;

If you have more than 100 days, then you will need to use the MAXRECURSION option.

Note: The above keeps the columns as specified in the question, under the assumption that these are not the real names. Obviously, end is a reserved word (and start might be one in the future), so these should be escaped if they are the actual column names.

edited Jul 18 '17 at 11:22

answered Jul 17 '17 at 02:28

Gordon Linoff

1,242,037
58
646
786

there's an error said "Incorrect syntax near the keyword 'end'. please help – Joe'Tans Jul 18 '17 at 03:27
it is possible to use the do while option? I try to use it but don't success to switch into another record.. – Joe'Tans Jul 18 '17 at 03:38
it's working thank's a lot , can this option can be use on insert into table? what should I add for the script ..thx – Joe'Tans Jul 18 '17 at 06:58
@Joe'Tans . . . Yes. `INSERT` can use CTEs as well as `SELECT`s. – Gordon Linoff Jul 18 '17 at 11:24
does ..dte .." select id, dateadd(day, 1, dte), end from t " is taken from start field variable? – Joe'Tans Jul 19 '17 at 07:39

Pரதீப் · Answer 2 · 2017-07-18T03:54:11.090

You need a calendar table to do this. I would prefer to create a physical calendar table in my database and use it in situation like this. Here is one way to generate dates on the fly using tally table approach

DECLARE @minDate DATETIME

SELECT @minDate = Min([Start])
FROM   Yourtable

;WITH lv0 AS (SELECT 0 g UNION ALL SELECT 0)
    ,lv1 AS (SELECT 0 g FROM lv0 a CROSS JOIN lv0 b) -- 4
    ,lv2 AS (SELECT 0 g FROM lv1 a CROSS JOIN lv1 b) -- 16
    ,lv3 AS (SELECT 0 g FROM lv2 a CROSS JOIN lv2 b) -- 256
    ,lv4 AS (SELECT 0 g FROM lv3 a CROSS JOIN lv3 b) -- 65,536
    ,Tally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM lv3)
SELECT ID,
       Dateadd(DD, n - 1, @minDate)
FROM   Tally t
       JOIN Yourtable a
         ON Dateadd(DD, t.n - 1, @minDate) BETWEEN [Start] AND [End]
ORDER  BY ID,n;

If you want to know more about tally tables check this link Tally Tables in T-SQL

Do while in do while SQL Server

2 Answers2