CODEWITHSUNDEEP
sql-server
user2811136
user2811136

Reputation: 23

Create dates in SQL Server

I have the following:

select distinct
    loannumber, activedt, inactivedt
from 
    smd..TABLE_NAME
where 
    loannumber = '12345678'

I want to be able to create a column that contains a value for every month/date between the activedt and inactivedt. I have seen ideas where the dates are set as variables, but in this case, the dates are coming from the table for each record. Please help. Thanks.

Upvotes: 0

Views: 54

Answers (3)

Jason W
Jason W

Reputation: 13179

You won't beat the performance of a physical date table, but if you have less than about 2000 days in between the inactive and active dates and just need basic queries ad-hoc against it, then consider CROSS APPLY against the spt_values table.

Since I wasn't sure if if active and inactive dates where always on or before (some cases with active > inactive, and others with inactive <= active), this should work for either case:

select distinct
    loannumber, activedt, inactivedt, dtvalue
from 
    smd..TABLE_NAME
    CROSS APPLY (
        SELECT DATEADD(DD, N.number, activedt) FROM spt_values N
            WHERE N.number BETWEEN 0 AND DATEDIFF(DD, activedt, inactivedt) AND activedt <= inactivedt
        UNION ALL SELECT DATEADD(DD, N.number, inactivedt) FROM spt_values N
            WHERE N.number BETWEEN 0 AND DATEDIFF(DD, inactivedt, activedt) AND activedt > inactivedt
    ) N (dtvalue)
where 
    loannumber = '12345678'

This produced the results below for a single record loan number from 1/3 to 1/5:

loannumber activedt   inactivedt dtvalue
---------- ---------- ---------- ----------
12345678   2017-01-03 2017-01-05 2017-01-03
12345678   2017-01-03 2017-01-05 2017-01-04
12345678   2017-01-03 2017-01-05 2017-01-05

If you need to get beyond 2000, there are additional techniques to UNION ALL to generate appropriate row counts quickly.

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81930

This will give you MONTH increments. However, if you want DAY, just change MM to DD within the CROSS APPLY

We're creating an ad-hoc tally table with master..spt_values , but any table of appropriate size will do

Declare @YourTable table (loannumber varchar(25),activedt date,inactivedt date)
Insert Into @YourTable values
('12345678','2016-01-01','2017-12-31')

Select A.LoanNumber
      ,B.Date
 From  @YourTable A
 Cross Apply (
       Select Top (DateDiff(MM,A.activedt,A.inactivedt)+1) Date=DateAdd(MM,Row_Number() Over (Order By (Select null))-1,A.activedt) 
         From master..spt_values 
       ) B

Returns

LoanNumber  Date
12345678    2016-01-01
12345678    2016-02-01
12345678    2016-03-01
12345678    2016-04-01
12345678    2016-05-01
12345678    2016-06-01
12345678    2016-07-01
12345678    2016-08-01
12345678    2016-09-01
12345678    2016-10-01
12345678    2016-11-01
12345678    2016-12-01
12345678    2017-01-01
12345678    2017-02-01
12345678    2017-03-01
12345678    2017-04-01
12345678    2017-05-01
12345678    2017-06-01
12345678    2017-07-01
12345678    2017-08-01
12345678    2017-09-01
12345678    2017-10-01
12345678    2017-11-01
12345678    2017-12-01

Upvotes: 1

Geovanny Hernandez
Geovanny Hernandez

Reputation: 237

I suggest using Date Tables, in this article, there is an extensive explanation, the idea is that you can use for this kind of scenario.

https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

With this table, you can do join between tables and use the date column.

Upvotes: 0

Related Questions