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.
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
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.
With this table, you can do join between tables and use the date column.
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.
WHERE clause. In example from answer, it would be something like AND dtvalue <= GETDATE()