While Loop in SQL Server 2008 Iterating Through a Date-Range and Then Insert

  • Home
  • Languages
  • SQL
  • While Loop in SQL Server 2008 Iterating Through a Date-Range and Then Insert

While loop in SQL Server 2008 iterating through a date-range and then INSERT

SQL is a set based language and loops should be a last resort. So the set based approach would be to first generate all the dates you require and insert them in one go, rather than looping and inserting one at a time. Aaron Bertrand has written a great series on generating a set or sequence without loops:

  • Generate a set or sequence without loops – part 1
  • Generate a set or sequence without loops – part 2
  • Generate a set or sequence without loops – part 3

Part 3 is specifically relevant as it deals with dates.

Assuming you don't have a Calendar table you can use the stacked CTE method to generate a list of dates between your start and end dates. 

DECLARE @StartDate DATE = '2015-01-01',
        @EndDate DATE = GETDATE();

WITH N1 (N) AS (SELECT 1 FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2)
SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate) + 1)
        Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY N) - 1, @StartDate)
FROM N3;

I have skipped some detail on how this works as it is covered in the linked article, in essence it starts with a hard coded table of 10 rows, then joins this table with itself to get 100 rows (10 x 10) then joins this table of 100 rows to itself to get 10,000 rows (I stopped at this point but if you require further rows you can add further joins).

At each step the output is a single column called N with a value of 1 (to keep things simple). At the same time as defining how to generate 10,000 rows, I actually tell SQL Server to only generate the number needed by using TOP and the difference between your start and end date - TOP(DATEDIFF(DAY, @StartDate, @EndDate) + 1). This avoids unnecessary work. I had to add 1 to the difference to ensure both dates were included.

Using the ranking function ROW_NUMBER() I add an incremental number to each of the rows generated, then I add this incremental number to your start date to get the list of dates. Since ROW_NUMBER() begins at 1, I need to deduct 1 from this to ensure the start date is included.

Then it would just be a case of excluding dates that already exist using NOT EXISTS. I have enclosed the results of the above query in their own CTE called dates:

DECLARE @StartDate DATE = '2015-01-01',
        @EndDate DATE = GETDATE();

WITH N1 (N) AS (SELECT 1 FROM (VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),
Dates AS
(   SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate) + 1)
            Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY N) - 1, @StartDate)
    FROM N3
)
INSERT INTO MyTable ([TimeStamp])
SELECT  Date
FROM    Dates AS d
WHERE NOT EXISTS (SELECT 1 FROM MyTable AS t WHERE d.Date = t.[TimeStamp])

Example on SQL Fiddle

If you were to create a calendar table (as described in the linked articles) then it may not be necessary to insert these extra rows, you could just generate your result set on the fly, something like:

SELECT  [Timestamp] = c.Date,
        t.[FruitType],
        t.[NumOffered],
        t.[NumTaken],
        t.[NumAbandoned],
        t.[NumSpoiled]
FROM    dbo.Calendar AS c
        LEFT JOIN dbo.MyTable AS t
            ON t.[Timestamp] = c.[Date]
WHERE   c.Date >= @StartDate
AND     c.Date < @EndDate;

ADDENDUM

To answer your actual question your loop would be written as follows:

DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME
DECLARE @CurrentDate AS DATETIME

SET @StartDate = '2015-01-01'
SET @EndDate = GETDATE()
SET @CurrentDate = @StartDate

WHILE (@CurrentDate < @EndDate)
BEGIN
    IF NOT EXISTS (SELECT 1 FROM myTable WHERE myTable.Timestamp = @CurrentDate)
    BEGIN
        INSERT INTO MyTable ([Timestamp])
        VALUES (@CurrentDate);
    END

    SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate); /*increment current date*/
END

Example on SQL Fiddle

I do not advocate this approach, just because something is only being done once does not mean that I should not demonstrate the correct way of doing it.

FURTHER EXPLANATION

Since the stacked CTE method may have over complicated the set based approach I will simplify it by using the undocumented system table master..spt_values. If you run:

SELECT Number
FROM master..spt_values
WHERE Type = 'P';

You will see that you get all the numbers from 0 -2047.

Now if you run:

DECLARE @StartDate DATE = '2015-01-01',
        @EndDate DATE = GETDATE();

SELECT Date = DATEADD(DAY, number, @StartDate)
FROM master..spt_values
WHERE type = 'P';

You get all the dates from your start date to 2047 days in the future. If you add a further where clause you can limit this to dates before your end date:

DECLARE @StartDate DATE = '2015-01-01',
        @EndDate DATE = GETDATE();

SELECT Date = DATEADD(DAY, number, @StartDate)
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY, number, @StartDate) <= @EndDate;

Now you have all the dates you need in a single set based query you can eliminate the rows that already exist in your table using NOT EXISTS

DECLARE @StartDate DATE = '2015-01-01',
        @EndDate DATE = GETDATE();

SELECT Date = DATEADD(DAY, number, @StartDate)
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY, number, @StartDate) <= @EndDate
AND NOT EXISTS (SELECT 1 FROM MyTable AS t WHERE t.[Timestamp] = DATEADD(DAY, number, @StartDate));

Finally you can insert these dates into your table using INSERT 

DECLARE @StartDate DATE = '2015-01-01',
        @EndDate DATE = GETDATE();

INSERT YourTable ([Timestamp])
SELECT Date = DATEADD(DAY, number, @StartDate)
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY, number, @StartDate) <= @EndDate
AND NOT EXISTS (SELECT 1 FROM MyTable AS t WHERE t.[Timestamp] = DATEADD(DAY, number, @StartDate));

Hopefully this goes some way to showing that the set based approach is not only much more efficient it is simpler too.

Loop/Iterate through date range while inserting

  1. Create a date table and use a JOIN.
  2. Calculate days between startdate and enddate
  3. Divide totalhours and totalwages by calculated days.

Here's my solution:

SELECT a.empid, b.dd AS date, 
  CAST(a.totalhours AS decimal) / (DATEDIFF(day, startdate, enddate) + 1) AS hours,
  CAST(a.totalwages AS decimal) / (DATEDIFF(day, startdate, enddate) + 1) AS wages
FROM wages a
INNER JOIN dates b ON dd BETWEEN a.startdate AND a.enddate

Result

|  EMPID |       DATE |         HOURS |          WAGES |
--------------------------------------------------------
| ABC123 | 2013-01-01 | 5.71428571428 | 64.28571428571 |
| ABC123 | 2013-01-02 | 5.71428571428 | 64.28571428571 |
| ABC123 | 2013-01-03 | 5.71428571428 | 64.28571428571 |
| ABC123 | 2013-01-04 | 5.71428571428 | 64.28571428571 |
| ABC123 | 2013-01-05 | 5.71428571428 | 64.28571428571 |
| ABC123 | 2013-01-06 | 5.71428571428 | 64.28571428571 |
| ABC123 | 2013-01-07 | 5.71428571428 | 64.28571428571 |
| ABC123 | 2013-01-08 | 5.71428571428 | 64.28571428571 |
| ABC123 | 2013-01-09 | 5.71428571428 | 64.28571428571 |
| ABC123 | 2013-01-10 | 5.71428571428 | 64.28571428571 |
| ABC123 | 2013-01-11 | 5.71428571428 | 64.28571428571 |
| ABC123 | 2013-01-12 | 5.71428571428 | 64.28571428571 |
| ABC123 | 2013-01-13 | 5.71428571428 | 64.28571428571 |
| ABC123 | 2013-01-14 | 5.71428571428 | 64.28571428571 |
| ABC123 | 2013-01-15 | 5.71428571428 | 64.28571428571 |
| ABC123 | 2013-01-16 | 5.71428571428 | 64.28571428571 |
| ABC123 | 2013-01-17 | 5.71428571428 | 64.28571428571 |
| ABC123 | 2013-01-18 | 5.71428571428 | 64.28571428571 |
| ABC123 | 2013-01-19 | 5.71428571428 | 64.28571428571 |
| ABC123 | 2013-01-20 | 5.71428571428 | 64.28571428571 |
| ABC123 | 2013-01-21 | 5.71428571428 | 64.28571428571 |
| ABC123 | 2013-01-22 | 5.71428571428 | 64.28571428571 |
| ABC123 | 2013-01-23 | 5.71428571428 | 64.28571428571 |
| ABC123 | 2013-01-24 | 5.71428571428 | 64.28571428571 |
| ABC123 | 2013-01-25 | 5.71428571428 | 64.28571428571 |
| ABC123 | 2013-01-26 | 5.71428571428 | 64.28571428571 |
| ABC123 | 2013-01-27 | 5.71428571428 | 64.28571428571 |
| ABC123 | 2013-01-28 | 5.71428571428 | 64.28571428571 |

See the demo

Loop through range of dates in SQL Server

There's really not enough information here to be sure what you need... but first few observations:

You have a select top 1 from a table, but you're not selecting anything from it

You have quite complex select, which looks like it's the same as this:

convert(dateadd(month, DATEDIFF(month, 0, getdate()), 0), 112)

Which is the first day of current month in YYYYMMDD format

You're assigning that to a varchar(50) -- and passing as a parameter to a procedure. Is the procedure actually using a date or datetime for this?

So, my guess is that you actually need this:

declare @date date

set @date = '20120301'

while (@date < getdate()) begin

    exec SP_DELETE_lIGNE_MOIS_EN_COURS @date

    set @date = dateadd(month, 1, @date)
end

Select Every Date for Date Range and Insert

I think this should do it (DEMO):

;with cte as (
  select
     id
    ,startdate
    ,enddate
    ,value / (1+datediff(day, startdate, enddate)) as value
    ,startdate as date
  from units
  union all
  select id, startdate, enddate, value, date+1 as date
  from cte
  where date < enddate
)
select
   row_number() over (order by date) as ID
  ,date
  ,sum(value) as value
from cte
group by date

The idea is to use a Recursive CTE to explode the date ranges into one record per day. Also, the logic of value / (1+datediff(day, startdate, enddate)) distributes the total value evenly over the number of days in each range. Finally, we group by day and sum together all the values corresponding to that day to get the output:

| ID |                            DATE | VALUE |
|----|---------------------------------|-------|
|  1 |  January, 01 2014 00:00:00+0000 |    11 |
|  2 |  January, 02 2014 00:00:00+0000 |    16 |
|  3 |  January, 03 2014 00:00:00+0000 |    16 |
|  4 | February, 01 2014 00:00:00+0000 |    10 |
|  5 | February, 02 2014 00:00:00+0000 |    10 |

From here you can join with your result table (Table B) by date, and update/insert the value as needed. That logic might look something like this (test it first of course before running in production!):

update B set B.VALUE = R.VALUE from TableB B join Result R on B.DATE = R.DATE
insert TableB (DATE, VALUE)
  select DATE, VALUE from Result R where R.DATE not in (select DATE from TableB)

List dates between two date range

Use recursive CTE:

WITH tmp AS (
    SELECT  id, StartDate AS [Date], EndDate
    FROM    MyTable
    UNION ALL
    SELECT  tmp.id, DATEADD(DAY,1,tmp.[Date]), tmp.EndDate
    FROM    tmp
    WHERE   tmp.[Date] < tmp.EndDate
)

SELECT tmp.ID, tmp.[Date]
FROM tmp
ORDER BY tmp.id, tmp.[Date]
OPTION (MAXRECURSION 0) -- For long intervals

If you have to use cursor/loop, most times you are doing it wrong.

sql while loop with date counter

You can convert the date params to datetime.

SELECT convert(datetime, @StartDate) into datetimevariable

then you can use date functions to add days.

select DATEADD(day,1,datetimevariable) into datetimevariable

As a solution to get a m/d/yyyy format, I C&P this function from some website a couple of weeks ago. Use this code to create a function and call in this way: 

 SELECT dbo.fnFormatDate (@DateTimeVariable, 'M/DD/YYYY') into stringVariable 
CREATE FUNCTION dbo.CustomFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))
RETURNS VARCHAR(32)
AS
BEGIN

    DECLARE @StringDate VARCHAR(32)
    SET @StringDate = @FormatMask
    IF (CHARINDEX (‘YYYY’,@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, ‘YYYY’,
                         DATENAME(YY, @Datetime))
    IF (CHARINDEX (‘YY’,@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, ‘YY’,
                         RIGHT(DATENAME(YY, @Datetime),2))
    IF (CHARINDEX (‘Month’,@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, ‘Month’,
                         DATENAME(MM, @Datetime))
    IF (CHARINDEX (‘MON’,@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)
       SET @StringDate = REPLACE(@StringDate, ‘MON’,
                         LEFT(UPPER(DATENAME(MM, @Datetime)),3))
    IF (CHARINDEX (‘Mon’,@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, ‘Mon’,
                                     LEFT(DATENAME(MM, @Datetime),3))
    IF (CHARINDEX (‘MM’,@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, ‘MM’,
                  RIGHT(‘0′+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
    IF (CHARINDEX (‘M’,@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, ‘M’,
                         CONVERT(VARCHAR,DATEPART(MM, @Datetime)))
    IF (CHARINDEX (‘DD’,@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, ‘DD’,
                         RIGHT(‘0′+DATENAME(DD, @Datetime),2))
    IF (CHARINDEX (‘D’,@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, ‘D’,
                                    DATENAME(DD, @Datetime))   

RETURN @StringDate
END
GO

How do you loop through a class of start and end DateTimes, storing the days between each date range to a list, with each iteration?

I would prefer to return the available dates as a deferred enumerable:

public static IEnumerable<DateTime> GetAvailableDates(int id)
{
    DateTime startDate = DateTime.Today;
    DateTime endDate = new DateTime(2019, 12, 31);
    var bookedDates = GetBookedDates(id);
    for (DateTime date = startDate; date <= endDate; date = date.AddDays(1))
    {
        if (bookedDates.Any(range => date >= range.Arrive && date <= range.Depart)) continue;
        yield return date;
    }
}

Related Topics

Try_Convert Fails on SQL Server 2012

How to Retrieve The Primary Key When Saving a New Object in Anorm

Mod' Is Not a Recognized Built-In Function Name

Sql Server Left Join and Where Clause

How This SQL Injection Works? Explanation Needed

Case Statement in Where Clause - SQL Server

Varchar(255) V Tinyblob V Tinytext

Bigquery Select * Except Nested Column

Sql Server 2012 Random String from a List

How to Concat_Ws Multiple Fields and Remove Duplicate Separators for Empty Slots

Linked Access Db "Record Has Been Changed by Another User"

Select All Parents or Children in Same Table Relation SQL Server

What Is The T-Sql Equivalent of MySQL Syntax Limit X, Y

Postgresql Update Trigger

How to Retrieve Associated Objects/Records That a Set of Users Have in Common Through a Association Table

MySQL Left Join Null Result

Update Statement with Multiple Where Conditions

Weighted Average in T-Sql (Like Excel's Sumproduct)


Leave a reply



Submit