Ok, to be fair, you've been doing the equivalent of the old fashioned "BUMP" post to draw attention to your question, after just an hours time each. Please be a little more patient.
Create a NUMBERS table (that's what most people call it), with 4 rows, just containing the integers 1, 2, 3, and 4. Join your table to that NUMBERS table, so that the end result will be 4 million rows (you said there were a million rows, so after joining
on a 4 row table, you'll have 4 million, containing your 1 million rows, repeated 4 times, with a 1, a 2, a 3, and a 4.
Here's an example. My formulas don't match yours, I still wasn't 100 percent sure what you meant in the formulas, but you should be very close to the answer with this example, just adjust the formulas in the two CASE statements. (Note that you
can't have two outputs from one case statement, so you just have to have two case statements).
SETUP_EXAMPLE_TABLE:
Declare @Example table (ID int, D1 date, D2 date, D3 date, d4 Date, otherstuff varchar(99))
insert @Example Select 1, getdate(), getdate() + 9999, getdate()+365, getdate()+ 720, 'first'
UNION Select 2, getdate(), '9999-12-31', getdate() + 30, getdate() + 60, 'second'
QUERY_INCLUDING_SMALL_NUMBERS_TABLE_VIA_CROSS_APPLY:
Select *
, case When Num = 1 Then D1 when NUM = 2 then D2 when NUM = 3 then D3 when NUM = 4 then D4 End as startdate
, case When NUM = 1 then D2 when NUM = 2 then DateAdd(day, -1, D2) when NUM = 3 then Dateadd(Day, -1, d3) when NUM = 4 then d4 End as EndDate
From @EXAMPLE
Cross Apply (Select 1 as Num UNION Select 2 UNION Select 3 UNION Select 4) as Numbers
order by ID, Num
