I’m trying to break out a date span from a single row into multiple rows, below is how I would like to see it:
let’s say I have the following data:
Name | MemberID | EFF_DATE | TERM_DATE |
---|---|---|---|
John | 1234 | 2020/01/01 | 2020/03/30 |
I would love to see:
Name | MemberID | yearnumb | monthnumb | memberall |
---|---|---|---|---|
John | 1234 | 2020 | 1 | 2020/01/01 |
John | 1234 | 2020 | 2 | 2020/02/01 |
John | 1234 | 2020 | 3 | 2020/03/01 |
My current simplified query is:
<code> SELECT
Name
,[MemberID]
,[EFF_DATE]
,[TERM_DATE]
FROM [SAM].[QHN].[EnrollmentBASE] t
where pat_id = '008243783 10' and rn = 1
</code>
<code> SELECT
Name
,[MemberID]
,[EFF_DATE]
,[TERM_DATE]
FROM [SAM].[QHN].[EnrollmentBASE] t
where pat_id = '008243783 10' and rn = 1
</code>
SELECT
Name
,[MemberID]
,[EFF_DATE]
,[TERM_DATE]
FROM [SAM].[QHN].[EnrollmentBASE] t
where pat_id = '008243783 10' and rn = 1
I found this code sample that works exactly how I’d like it to but using my current table/query but I’m not sure how to incorporate the two.
<code>DECLARE @DateStart DATE = '2021-01-20' , @DateEnd DATE = '2021-01-29';
with Extract_Dates_CTE (MyDate) as (
select @DateStart
Union ALL
select DATEADD(day, 1, MyDate)
from Extract_Dates_CTE
where MyDate < @DateEnd
)
select ROW_NUMBER() OVER(ORDER BY a.MyDate) AS RowDateID, a.MyDate AS ExtractedDates
from Extract_Dates_CTE a;
</code>
<code>DECLARE @DateStart DATE = '2021-01-20' , @DateEnd DATE = '2021-01-29';
with Extract_Dates_CTE (MyDate) as (
select @DateStart
Union ALL
select DATEADD(day, 1, MyDate)
from Extract_Dates_CTE
where MyDate < @DateEnd
)
select ROW_NUMBER() OVER(ORDER BY a.MyDate) AS RowDateID, a.MyDate AS ExtractedDates
from Extract_Dates_CTE a;
</code>
DECLARE @DateStart DATE = '2021-01-20' , @DateEnd DATE = '2021-01-29';
with Extract_Dates_CTE (MyDate) as (
select @DateStart
Union ALL
select DATEADD(day, 1, MyDate)
from Extract_Dates_CTE
where MyDate < @DateEnd
)
select ROW_NUMBER() OVER(ORDER BY a.MyDate) AS RowDateID, a.MyDate AS ExtractedDates
from Extract_Dates_CTE a;
Instead of using data variables, I would like to use the columns EFF_DATE and TERM_DATE contained in the table that is in my simplified query. Any help with this is greatly appreciated.
1