# how do i calculate the dates in this scenario

• ### Question

• I have a situation here and i am not able to think through it

there are 4 date columns in a table and i need to do something like below

First Record start date is the start date, and end date is the next start date -1
Next start date is next start date, and end date is the first end date of the record set of the end dates
Next start date pervious end date + 1 and end date is the current end date
Next start date is the current date and next end date is the current end date

for example lets say we have a table like below

CustomerID CustomerType CustomerEffDt   CustomerTrmDt CustNationalBgnDt CustNationalEndDt
1                  National           2013-08-05        9999-12-31       2010-11-08            2011-11-02

Now my question is, we would have to calculate the customer effective start and begin dates and national begin and end dates based on the above criteria
we would have to create 4 rows based on the above scenario

I am going mad and not able to think through this
Can somebody help me

Thanks

Monday, March 17, 2014 5:09 PM

• ```Declare @Example table  (CustomerID int,   CustomerEffDt date,  CustomerTrmDt date, CustNationalBgnDt date, CustNationalEndDt date, otherstuff varchar(99))

insert @Example Select 1, '3/19/2014', '9999-12-31', '3/19/2015',  '2/19/2016', 'first'
UNION Select 2,  '3/19/2014', '9999-12-31',  '4/19/2014',  '5/28/2014', 'second'
UNION Select 3, '2013-08-05', '9999-12-31', '2010-11-08', '2011-11-02', 'first example by OP'

Select CustomerID,Num
, case When Num = 1  Then '1/1/1900'
when NUM = 2 then CustNationalBgnDt
when NUM = 3 then DateAdd(day, 1, CustNationalEndDt )
when NUM = 4 then CustomerEffDt End  as startdate
, case When NUM = 1 then DateAdd(day, -1, CustNationalBgnDt)
when NUM = 2 then CustNationalEndDt
when NUM = 3 then Dateadd(Day, -1, CustomerEffDt)
when NUM = 4 then CustomerTrmDt End as EndDate
From @EXAMPLE
Cross Apply (Select 1 as Num UNION Select 2 UNION Select 3 UNION Select 4) as Numbers
order by CustomerID, Num

1	1	1900-01-01	2015-03-18
1	2	2015-03-19	2016-02-19
1	3	2016-02-20	2014-03-18
1	4	2014-03-19	9999-12-31
2	1	1900-01-01	2014-04-18
2	2	2014-04-19	2014-05-28
2	3	2014-05-29	2014-03-18 ---***
2	4	2014-03-19	9999-12-31 --****
3	1	1900-01-01	2010-11-07
3	2	2010-11-08	2011-11-02
3	3	2011-11-03	2013-08-04
3	4	2013-08-05	9999-12-31

*/

/*
CustomerID	StartDate	EndDate
1	1900-01-01	2014-03-18
1	2014-03-19	2015-03-18
1	2015-03-19	2016-02-19
1	2016-02-20	9999-12-31
2	1900-01-01	2014-03-18
2	2014-03-19	2014-04-18
2	2014-04-19	2014-05-28
2	2014-05-29	9999-12-31
3	1900-01-01	2010-11-07
3	2010-11-08	2011-11-02
3	2011-11-03	2013-08-04
3	2013-08-05	9999-12-31
*/

;with mycte as
(
select CustomerID, dt,col,row_number() Over(partition by CustomerID Order by dt) rn
,row_number() Over(partition by CustomerID Order by dt DESC) rn2  from @Example

cross apply (
values( CustomerEffDt,'CustomerEffDt'), (CustomerTrmDt,'CustomerTrmDt')
, (CustNationalBgnDt,'CustNationalBgnDt'), (CustNationalEndDt,'CustNationalEndDt'))
d(dt,col)
)

Select  m1.CustomerID,
Case when m1.rn=1 Then  '1/1/1900'
When m2.col='CustNationalEndDt' Then  dateadd(day,1,m2.dt)
Else m2.dt  end as StartDate,
Case
when m1.rn2=1 Then m1.dt
When m1.col='CustNationalEndDt' Then m1.dt
as EndDate

From mycte m1 LEFT Join mycte   m2 on m1.CustomerID=m2.CustomerID AND m1.rn=m2.rn+1
Order by m1.CustomerID, m1.dt

```

• Proposed as answer by Wednesday, March 26, 2014 1:17 AM
• Marked as answer by Wednesday, March 26, 2014 7:17 PM
Wednesday, March 19, 2014 2:53 PM

### All replies

• the dates should be something like this including other columns

Start Date    End Date
1/1/1900    11/7/2010
11/8/2010    11/2/2011
11/3/2011    8/4/2013
8/5/2013    12/31/9999

Thanks

Monday, March 17, 2014 5:21 PM
• What techniques should i use to approach this problem?

Basically i am working on SCD for the above column

Thanks

• Edited by Monday, March 17, 2014 5:48 PM
Monday, March 17, 2014 5:48 PM
• Jack,

check this:

```select customerid, customertype, '1/1/1900' as startdate,datediff(dd,-1,CustNationalBgnDt) as enddate
from tbl_name
union all
select customerid, customertype, CustNationalBgnDt, CustNationalEndDt
from tbl_name
union all
from tbl_name
union all
select customerid, customertype, CustomerEffDt, CustomerTrmDt
from tbl_name
order by Customerid```

Thanks,
Jay
<If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

Monday, March 17, 2014 6:02 PM
• Can you explain what would your output for single customer record given above?

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

Monday, March 17, 2014 6:02 PM
• Hello Jay,

The ssis package should be smart enough to calculate the begin and end dates and the national begin and end dates and create new rows for the slowly changing dimension

do you know how can i implement that?

Thanks

Monday, March 17, 2014 6:18 PM
• CustomerID NationalIDAsWs CustomerEffDt   CustomerTrmDt  OpCo CustKey
1                  null                     1900-01-01           2010-11-07        5    52454
2                  FCMD                  2010-11-08           2011-11-02       5     52454
3                  nulll                     2011-11-03           2013-08-04       5    52454
4                  null                      2013-08-05           9999-12-31      5     52454

Thanks

Monday, March 17, 2014 6:26 PM
• ```create table test (CustomerID int, CustomerType varchar(50), CustomerEffDt date,  CustomerTrmDt date, CustNationalBgnDt date, CustNationalEndDt date)

insert into test values (1,'National','2013-08-05','9999-12-31','2010-11-08','2011-11-02')

;with mycte as
(
select CustomerID, dt,col,row_number() Over(partition by CustomerID Order by dt) rn
,row_number() Over(partition by CustomerID Order by dt DESC) rn2  from test

cross apply (
values( CustomerEffDt,'CustomerEffDt'), (CustomerTrmDt,'CustomerTrmDt')
, (CustNationalBgnDt,'CustNationalBgnDt'), (CustNationalEndDt,'CustNationalEndDt'))
d(dt,col)
)

Select
Case when m1.rn=1 Then  '1/1/1900'
When m2.col='CustNationalEndDt' Then  dateadd(day,1,m2.dt)
Else m2.dt  end as StartDate,
Case
when m1.rn2=1 Then m1.dt
When m1.col='CustNationalEndDt' Then m1.dt
as EndDate

From mycte m1 LEFT Join mycte   m2 on m1.CustomerID=m2.CustomerID AND m1.rn=m2.rn+1
Order by m1.dt

drop table test```

Monday, March 17, 2014 6:29 PM
• my table has 100 million rows

if i do cross apply

will it work?

i am working on SCD concept here :(

the final output for a single row should be something liek this

CustomerID NationalIDAsWs CustomerEffDt   CustomerTrmDt  OpCo CustKey
1                  null                     1900-01-01           2010-11-07        5    52454
2                  FCMD                  2010-11-08           2011-11-02       5     52454
3                  nulll                     2011-11-03           2013-08-04       5    52454
4                  null                      2013-08-05           9999-12-31      5     52454

it has to be intellingent to calculate the being date, end date etc :(

Thanks

• Edited by Monday, March 17, 2014 6:48 PM
Monday, March 17, 2014 6:46 PM
• any body pls help :(

Thanks

Monday, March 17, 2014 7:25 PM
• so what will be initial value for record in the source?

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

Monday, March 17, 2014 7:32 PM
• CustomerID NationalID CustomerEffDt   CustomerTrmDt CustNationalBgnDt CustNationalEndDt OpCo CustKey
1                  FCMD         2013-08-05        9999-12-31       2010-11-08            2011-11-02            5        52454

is the initial value for example

Thanks

Monday, March 17, 2014 7:53 PM
• what are the possibilities and options i have to implement this?

Thanks

Monday, March 17, 2014 8:44 PM
• 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```

Tuesday, March 18, 2014 11:04 AM
• CustomerID NationalID CustomerEffDt   CustomerTrmDt CustNationalBgnDt CustNationalEndDt OpCo CustKey
1                  FCMD         2013-08-05        9999-12-31       2010-11-08            2011-11-02            5        52454

is the initial value for example

Thanks

Hmm..so do you mean you want to take all date ranges from various fields in row and split them up into multiple rows?

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

Tuesday, March 18, 2014 11:07 AM
• You are right

that is the reason i am really confused :(

Thanks

Tuesday, March 18, 2014 2:36 PM
• Here's still the same basic query from my earlier post, modified I believe to match your question, and also modified somewhat to match the column names you use.  Keep in mind only the final statement is the path you might choose to take, the first 2 statements are just recreating an environment.

```SETUP_EXAMPLE_TABLE:
Declare @Example table (ID int, CustomerEffDt date, CustomerTrmDt date, CustNationalBgnDt date, CustNationalEndDt Date, otherstuff varchar(99))

Create_example_data:
insert @Example Select 1, getdate(), getdate() + 9999, getdate()+365, getdate()+ 720, 'first'
UNION Select 2, getdate(), '9999-12-31', getdate() + 30, getdate() + 60, 'second'
UNION Select 3, '2013-08-05', '9999-12-31', '2010-11-08', '2011-11-02', 'first example by OP'

QUERY_INCLUDING_SMALL_NUMBERS_TABLE_VIA_CROSS_APPLY:
Select *
, case When Num = 1  Then '1/1/1900'
when NUM = 2 then CustNationalBgnDt
when NUM = 3 then DateAdd(day, 1, CustNationalEndDt )
when NUM = 4 then CustomerEffDt End  as startdate
, case When NUM = 1 then DateAdd(day, -1, CustNationalBgnDt)
when NUM = 2 then CustNationalEndDt
when NUM = 3 then Dateadd(Day, -1, CustomerEffDt)
when NUM = 4 then CustomerTrmDt 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```

Regarding your cross apply question earlier: cross apply loosely behaves about the same way as an Inner Join, so other than (in this case) intentionally leading to 4 times as many rows being returned, there isn't really any particular performance impact.

• P.S. Just in case two replies very similar to this one end up showing up, I could have sworn I posted this follow up reply yesterday too, but it never showed up.

Wednesday, March 19, 2014 12:52 PM
• John,

Can you run my query against your sample data and compare with your result? The result for ID =2 in your result does not seem right.

My question to the OP, why don't you try in your case for the solutions other people proposed?

You should provide your table DDL along with sample data that can represent your issues in your question.

We don't have the same environment to test the script and we need feedback to improve. Thanks.

Wednesday, March 19, 2014 2:27 PM
• Our columns were created in a different order in the temp table, that's the difference you're seeing.  I think both match up fine.  Also, my first two examples, 1 and 2, were from before I was even trying to recreate the OPs formulas, so they were random dates, including my apparent inability to multiple 365 by 2 correctly... ;-)
Wednesday, March 19, 2014 2:38 PM
• ```Declare @Example table  (CustomerID int,   CustomerEffDt date,  CustomerTrmDt date, CustNationalBgnDt date, CustNationalEndDt date, otherstuff varchar(99))

insert @Example Select 1, '3/19/2014', '9999-12-31', '3/19/2015',  '2/19/2016', 'first'
UNION Select 2,  '3/19/2014', '9999-12-31',  '4/19/2014',  '5/28/2014', 'second'
UNION Select 3, '2013-08-05', '9999-12-31', '2010-11-08', '2011-11-02', 'first example by OP'

Select CustomerID,Num
, case When Num = 1  Then '1/1/1900'
when NUM = 2 then CustNationalBgnDt
when NUM = 3 then DateAdd(day, 1, CustNationalEndDt )
when NUM = 4 then CustomerEffDt End  as startdate
, case When NUM = 1 then DateAdd(day, -1, CustNationalBgnDt)
when NUM = 2 then CustNationalEndDt
when NUM = 3 then Dateadd(Day, -1, CustomerEffDt)
when NUM = 4 then CustomerTrmDt End as EndDate
From @EXAMPLE
Cross Apply (Select 1 as Num UNION Select 2 UNION Select 3 UNION Select 4) as Numbers
order by CustomerID, Num

1	1	1900-01-01	2015-03-18
1	2	2015-03-19	2016-02-19
1	3	2016-02-20	2014-03-18
1	4	2014-03-19	9999-12-31
2	1	1900-01-01	2014-04-18
2	2	2014-04-19	2014-05-28
2	3	2014-05-29	2014-03-18 ---***
2	4	2014-03-19	9999-12-31 --****
3	1	1900-01-01	2010-11-07
3	2	2010-11-08	2011-11-02
3	3	2011-11-03	2013-08-04
3	4	2013-08-05	9999-12-31

*/

/*
CustomerID	StartDate	EndDate
1	1900-01-01	2014-03-18
1	2014-03-19	2015-03-18
1	2015-03-19	2016-02-19
1	2016-02-20	9999-12-31
2	1900-01-01	2014-03-18
2	2014-03-19	2014-04-18
2	2014-04-19	2014-05-28
2	2014-05-29	9999-12-31
3	1900-01-01	2010-11-07
3	2010-11-08	2011-11-02
3	2011-11-03	2013-08-04
3	2013-08-05	9999-12-31
*/

;with mycte as
(
select CustomerID, dt,col,row_number() Over(partition by CustomerID Order by dt) rn
,row_number() Over(partition by CustomerID Order by dt DESC) rn2  from @Example

cross apply (
values( CustomerEffDt,'CustomerEffDt'), (CustomerTrmDt,'CustomerTrmDt')
, (CustNationalBgnDt,'CustNationalBgnDt'), (CustNationalEndDt,'CustNationalEndDt'))
d(dt,col)
)

Select  m1.CustomerID,
Case when m1.rn=1 Then  '1/1/1900'
When m2.col='CustNationalEndDt' Then  dateadd(day,1,m2.dt)
Else m2.dt  end as StartDate,
Case
when m1.rn2=1 Then m1.dt
When m1.col='CustNationalEndDt' Then m1.dt
as EndDate

From mycte m1 LEFT Join mycte   m2 on m1.CustomerID=m2.CustomerID AND m1.rn=m2.rn+1
Order by m1.CustomerID, m1.dt

```

• Proposed as answer by Wednesday, March 26, 2014 1:17 AM
• Marked as answer by Wednesday, March 26, 2014 7:17 PM
Wednesday, March 19, 2014 2:53 PM
• Hmm... I'm not sure, I think I'm right, but not sure.  Hopefully if the OP returns, they can clarify.  My original intent (post 1) was to suggest a way to do it, but leave the final twerking of the formula up to them, but since they seemed not to grasp either your solution or mine, I took a second stab at filling in their formula.  (And, I also disclaimered up my answer, I did say, "I believe..."   :-)

My understanding of it was as follows:

• Row 1 = 1900-01-01 through the day before the customer begin date
• Row 2 = Actual customer begin date through actual customer end date
• Row 3 = Day after actual end date through day before termination date
• Row 4 = customer effective date through customer termination date

I don't think either of us mentioned "the midnight concept" to the OP either.  If further downstream, a customer buys something on the actual final day of their End date, let's say at 10:00 a.m. on November 2, 2011, the OPs code might fail, since they might have coded it to ask, "is November 2, 2011 at 10 a.m. less than November 2, 2011" (which would treat itself as November 2 at time 00:00), and their logic might fail them there.  Baby steps though, they first need to get their table loaded right.

EDIT: Also contributing to the confusion.. the example from the OP (on the surface) is confusing because there are two, but the second one also has some inconsistencies:  Either the effective date is outside the national begin and end dates, or if you flip it the other way, the national begin date is before the effective date.  Either way, I'm (we're?) confused!
• Edited by Wednesday, March 19, 2014 3:54 PM Added final EDIT paragraph
Wednesday, March 19, 2014 3:41 PM
• John,

Since you are working on this. I share what I came up with. But I cannot answer the question from OP for whether this solution will work for millions rows.(or whether it works or not). We may get more information from OP if he is still interested in this question. Thanks.

Wednesday, March 19, 2014 3:52 PM
• As noted above, be mindful of the super dangerous datetime midnight bug:

http://www.sqlusa.com/bestpractices2008/between-dates/

The combination of the >= and < (1 day passed end date) comparison operators is the best way to program a datetime range predicate.

Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

Wednesday, March 19, 2014 3:56 PM
• Maybe we scared him off!   The first day, he was bumping his post every 10 to 30  minutes... Now he's not coming back at all.   If only he could fine a nice happy medium!  :-D
Wednesday, March 19, 2014 4:04 PM