Generate a Resultset of Incrementing Dates in Tsql

Generate a resultset of incrementing dates in TSQL

If your dates are no more than 2047 days apart:

declare @dt datetime, @dtEnd datetime
set @dt = getdate()
set @dtEnd = dateadd(day, 100, @dt)

select dateadd(day, number, @dt)
from 
    (select number from master.dbo.spt_values
     where [type] = 'P'
    ) n
where dateadd(day, number, @dt) < @dtEnd

I updated my answer after several requests to do so. Why?

The original answer contained the subquery

 select distinct number from master.dbo.spt_values
     where name is null

which delivers the same result, as I tested them on SQL Server 2008, 2012, and 2016.

However, as I tried to analyze the code that MSSQL internally when querying from spt_values, I found that the SELECT statements always contain the clause WHERE [type]='[magic code]'.

Therefore I decided that although the query returns the correct result, it delivers the correct result for wrong reasons:

There may be a future version of SQL Server which defines a different [type] value which also has NULL as values for [name], outside the range of 0-2047, or even non-contiguous, in which case the result would be simply wrong.

generate a resultset for incrementing dates in TSQL and use last known value for quantity column

It works with just minor adjustments:

WITH dates AS
(
    SELECT CAST('20140101' AS DATE) 'date'
 UNION ALL
    SELECT DATEADD(day, 1, D.date)
    FROM dates D
    WHERE DATEADD(dd, 1, D.date) <= '20140301'
)
SELECT 
    D.date
    ,(  SELECT TOP 1 qoh
        FROM #t1 T
        WHERE T.postdate <= D.[date]
        ORDER BY T.postdate DESC
    )
FROM 
    dates D

This returns

2014-01-01  10
2014-01-02  10
2014-01-03  10
2014-01-04  10
2014-01-05  30
2014-01-06  30
2014-01-07  30
2014-01-08  30
2014-01-09  50
2014-01-10  50
2014-01-11  60
2014-01-12  60
2014-01-13  60
... ...

http://sqlfiddle.com/#!6/79578/1

Just a sidenote: I prefer to use a Calendar-table (if possible). Just throw in each day for the next ten years and it's still a very small table and you can join on it to return one row per day. It's quite convenient and the queries are easier to read than such recursion.

EDIT:

Set up a calendar table:

CREATE table tblCalendar ([date] date PRIMARY KEY);

DECLARE @n int;
SET @n = 0;

WHILE @n < (365*5)
BEGIN
  INSERT INTO tblCalendar([date]) VALUES(DATEADD(day, @n, '20140101'));
  SET @n = @n +1;
END

Cut down the query to:

SELECT 
    C.[date]
    ,(  SELECT TOP 1 qoh
        FROM @t1 T1
        WHERE T1.postdate <= C.[date]
        ORDER BY T1.postdate DESC
    )
FROM 
    tblCalendar C
WHERE
    C.date BETWEEN '20140101' AND '20140301'

T-SQL Wrapping an increment date around a query

You want one row per client and month. So first cross join the two to get all combinations. Then outer join your figures.

I'm using one WITH clause for the invoices you are interested in (matching the maximum revision) and one for a recursive query to generate the months.

with last_revision_invoices as
(
  select top(1) with ties
    clientcode, 
    year(invdate) as year, 
    month(invdate) as month, 
    tax_credit
  from client_invoices ci 
  order by case when invoice_revision = max(invoice_revision) over (partition by clientcode, invdate, invnumber) then 1 else 2 end
)
, months as (
  select year(@range_start) as year, month(@range_start) as month
  union all
  select case when month < 12 then year else year + 1 end, (month % 12) + 1
  from months
  where year < year(@range_end) or (year = year(@range_end) and month < month(@range_end))
)
select c.clientcode, m.year, m.month, coalesce(cur.total, 0) as total_then, coalesce(prev.total, 0) as total_previous_year
from client_main c
cross join months m
left join
(
  select clientcode, year, month, sum(tax_credit) as total
  from  last_revision_invoices
  group by clientcode, year, month
) cur on cur.clientcode = c.clientcode and cur.year = m.year and cur.month = m.month
left join
(
  select clientcode, year, month, sum(tax_credit) as total
  from  last_revision_invoices
  group by clientcode, year, month
) prev on prev.clientcode = c.clientcode and prev.year = m.year - 1 and prev.month = m.month
order by c.clientcode, m.year, m.month;

Rextester demo: http://rextester.com/NUOM2966

How to generate a range of dates in SQL Server

I would argue that for this specific purpose the below query is about as efficient as using a dedicated lookup table.

DECLARE @start DATE, @end DATE;
SELECT @start = '20110714', @end = '20110717';

;WITH n AS 
(
  SELECT TOP (DATEDIFF(DAY, @start, @end) + 1) 
    n = ROW_NUMBER() OVER (ORDER BY [object_id])
  FROM sys.all_objects
)
SELECT 'Bob', DATEADD(DAY, n-1, @start)
FROM n;

Results:

Bob     2011-07-14
Bob     2011-07-15
Bob     2011-07-16
Bob     2011-07-17

---

Presumably you'll need this as a set, not for a single member, so here is a way to adapt this technique:

DECLARE @t TABLE
(
    Member NVARCHAR(32), 
    RegistrationDate DATE, 
    CheckoutDate DATE
);

INSERT @t SELECT N'Bob', '20110714', '20110717'
UNION ALL SELECT N'Sam', '20110712', '20110715'
UNION ALL SELECT N'Jim', '20110716', '20110719';

;WITH [range](d,s) AS 
(
  SELECT DATEDIFF(DAY, MIN(RegistrationDate), MAX(CheckoutDate))+1,
    MIN(RegistrationDate)
    FROM @t -- WHERE ?
),
n(d) AS
(
  SELECT DATEADD(DAY, n-1, (SELECT MIN(s) FROM [range]))
  FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
  FROM sys.all_objects) AS s(n)
  WHERE n <= (SELECT MAX(d) FROM [range])
)
SELECT t.Member, n.d
FROM n CROSS JOIN @t AS t
WHERE n.d BETWEEN t.RegistrationDate AND t.CheckoutDate;
----------^^^^^^^ not many cases where I'd advocate between!

Results:

Member    d
--------  ----------
Bob       2011-07-14
Bob       2011-07-15
Bob       2011-07-16
Bob       2011-07-17
Sam       2011-07-12
Sam       2011-07-13
Sam       2011-07-14
Sam       2011-07-15
Jim       2011-07-16
Jim       2011-07-17
Jim       2011-07-18
Jim       2011-07-19

As @Dems pointed out, this could be simplified to:

;WITH natural AS 
(
  SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) - 1 AS val 
  FROM sys.all_objects
) 
SELECT t.Member, d = DATEADD(DAY, natural.val, t.RegistrationDate) 
  FROM @t AS t INNER JOIN natural 
  ON natural.val <= DATEDIFF(DAY, t.RegistrationDate, t.CheckoutDate);

Combine the list of intervals from two tables

I'd suggest that you union all start and endtimes and then build upon intervals using INNER JOIN or LEAD (in newer versions of SQL server)
See live demo

; with inputs  as
( select t=starttime from table1
 union 
 select t=endtime from table1
 union 
 select t=starttime from table2
 union
 select t=endtime from table2
 ),
  map as 
 (
     select starttime=t, endtime=lead(t) over ( order by t) 
     from inputs
  )

 select * from map 
 where endtime is not null
 order by starttime

Generate missing dates + Sql Server (SET BASED)

The following uses a recursive CTE (SQL Server 2005+):

WITH dates AS (
     SELECT CAST('2009-01-01' AS DATETIME) 'date'
     UNION ALL
     SELECT DATEADD(dd, 1, t.date) 
       FROM dates t
      WHERE DATEADD(dd, 1, t.date) <= '2009-02-01')
SELECT t.eventid, d.date
  FROM dates d 
  JOIN TABLE t ON d.date BETWEEN t.startdate AND t.enddate

It generates dates using the DATEADD function. It can be altered to take a start & end date as parameters. According to KM's comments, it's faster than using the numbers table trick.

Need to get dates between two date columns

A recursive CTE is a pretty easy method:

with dates as (
      select effdate as dte, enddate
      from t
      union all
      select dateadd(day, 1, dte), enddate
      from dates
      where dte < enddate
     )
select dte
from dates
option (maxrecursion 0);

Often a tally table has somewhat better performance:

with digits as (
      select v.n
      from (values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v(n)
     ),
     n as (
      select (d1*1000 + d2*100 + d3*10 + d4*1) n
      from digits d1 cross join
           digits d2 cross join
           digits d3 cross join
           digits d4 
     )
select dateadd(day, n.n, t.effdate)
from t join
     n
     on dateadd(day, n.n, t.effdate) <= t.enddate  ; 

You have to be sure that your table of numbers -- however generated -- is big enough for your timespan.

generate days from date range

This solution uses no loops, procedures, or temp tables. The subquery generates dates for the last 10,000 days, and could be extended to go as far back or forward as you wish.

select a.Date 
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as Date
    from (select 0 as a 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) as a
    cross join (select 0 as a 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) as b
    cross join (select 0 as a 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) as c
    cross join (select 0 as a 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) as d
) a
where a.Date between '2010-01-20' and '2010-01-24' 

Output:

Date
----------
2010-01-24
2010-01-23
2010-01-22
2010-01-21
2010-01-20

Notes on Performance

Testing it out here, the performance is surprisingly good: the above query takes 0.0009 sec.

If we extend the subquery to generate approx. 100,000 numbers (and thus about 274 years worth of dates), it runs in 0.0458 sec.

Incidentally, this is a very portable technique that works with most databases with minor adjustments.

SQL Fiddle example returning 1,000 days

---

Related Topics