Query Help to Split a table row based on date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query Help to Split a table row based on date

Author

Topic

shankar6666
Starting Member

8 Posts

Posted - 2006-09-11 : 03:26:39

I am selecting a row from a table as shown below. I want to split the row into multiple based on eligibility date range values.

cURRENT ROW
-----------

emp_Id StartDate EndDate Elig_Begin Elig_End
-----------------------------------------------------
1 01/01/2006 12/31/2006 06/01/2006 11/30/2006
-----------------------------------------------------

The above row should be split and inserted to a new table as shown below based on Elig_Begin and Elig_End Date. The employee has eligibility only for the period of 5 months(06/01/2006 to 11/30/2006).

NEW TABLE
-----------
emp_Id StartDate EndDate Elig_Ind
-----------------------------------------
1 01/01/2006 05/31/2006 N
1 06/01/2006 11/30/2006 Y
1 12/01/2006 12/31/2006 N

Can somebody help me to frame a query for this?
Any comments will be greatly appreciated

Thanks in Advance
Ravi

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-11 : 03:33:06

[code]
Insert NewTable
Select EmpID,StartDate,EndDate,
Case When DateDiff(mm,Elig_Begin,Elig_End) <5 Then 'N' Else 'Y' End As Elig_Ind
From <YourTable>
[/code]

Chirag

shankar6666
Starting Member

8 Posts

Posted - 2006-09-11 : 08:32:51

That is not a right answer. I think you didn't get the logic of splitting. Also, we can't check for any hard-coded values because the values will change each time.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 08:35:19

Is this homework?

Peter Larsson
Helsingborg, Sweden

shankar6666
Starting Member

8 Posts

Posted - 2006-09-11 : 08:39:04

nope. Just one logic i need to implement in my work.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 08:55:12

The solution provided by Chriag works very well. What is the "hardcoded" value you talk about? The 5 months?
How do you else make the split?

If you want us to help you, you must provide sample data, table layout and your expected output based on the provided sample data.

Peter Larsson
Helsingborg, Sweden

shankar6666
Starting Member

8 Posts

Posted - 2006-09-11 : 09:06:48

Peter,

I want to insert 3 rows into the new table from the original single row as shown in the ex. I've told 5 months just as an example. it depends on the elig_begin and elig_end dates which varies for employees, which determines how many months the employee has coverage. We need to insert 'Y' for months with coverage and 'N' for no coverage period. So, in this situation, we need to add 3 rows as described in the original example.

What the GOAL is "to split eligible and non-eligible period".
CURRENT ROW
-----------

emp_Id StartDate EndDate Elig_Begin Elig_End
-----------------------------------------------------
1 01/01/2006 12/31/2006 06/01/2006 11/30/2006 /* original row */
-----------------------------------------------------

NEW TABLE
-----------
emp_Id StartDate EndDate Elig_Ind
-----------------------------------------
1 01/01/2006 05/31/2006 N /* non-eligible period */
1 06/01/2006 11/30/2006 Y /* eligible period */
1 12/01/2006 12/31/2006 N /* non-eligible period */

Now, i think the situation got a bit clearer to you.

Thanks in advance.
RaviS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-11 : 09:11:12

quote:
Originally posted by shankar6666

That is not a right answer. I think you didn't get the logic of splitting. Also, we can't check for any hard-coded values because the values will change each time.

I don't think you are explaining the logic for splitting, nor did you provide enough sample data.

You *might* want something like this:

select emp_id, StartDate, Elig_Begin, 'N' as Elig_Ind
from YourTable
union all
select emp_id, Elig_Begin, Elig_End, 'Y' as Elig_Ind
from YourTable
union all
select empID, Elig_End, EndDate, 'N' as Elig_Ind
from YourTable

.. but it is hard to know for sure. Also, do all employees have eligiblity? If not, what is in those dates? Will Elig_End and Elig_Begin always fall within StartDate and EndDate? Will Elig_Begin ever be equal to StartDate, and/or Elig_End ever be equal to EndDate? Do all employees have an EndDate? (i.e., if they are still active or the EndDate is not known, what does EndDate equal? Null? How is that handled?)

Provide a complete scenerio will sample data and expected results for all possibilities and we can help you further. CREATE TABLE and INSERT statements help us help you even further since that will let us test and refine our solutions for you without having to manually create and type in your sample data, or guess about your actual table structure.

- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 09:11:50

[code]INSERT NewTable
(
Emp_ID,
StartDate,
EndDate,
Elig_Ind
)
SELECT Emp_ID,
StartDate,
DATEADD(day, -1, Elig_Begin),
'N'
FROM OldTable
WHERE StartDate < Elig_Begin
UNION ALL
SELECT Emp_ID,
Elig_Begin,
Elig_End,
'Y'
FROM OldTable
UNION ALL
SELECT Emp_ID,
DATEADD(day, 1, Elig_End),
EndDate,
'N'
FROM OldTable
WHERE Elig_End < EndDate[/code]
Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-11 : 09:25:18

Ah yes, forgot to add/subtract a day from the end ranges ...

- Jeff

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-11 : 09:25:49

My Efforts..


Declare @var Table
(
  Emp_Id int, 
  StartDate smalldatetime, 
  EndDate smalldatetime, 
  Elig_Begin smalldatetime, 
  Elig_End smalldatetime
)

Declare @NewTable TAble 
(
 Emp_ID int, 
 StartDate SmallDAteTime,
 EndDAte SmallDateTime, 
 Elig_Ind Char(1)
)
Insert @var 
Select 1,'01/01/2006','12/31/2006','06/01/2006','11/30/2006'

Insert @NewTable 
Select  Emp_ID, STartDAte,EndDate,
Case When 
	Datediff(mm,startDate,EndDate) < value Then 'N' 
     Else 'Y' 
     End  As Elig_Ind
From 
(
	Select Emp_Id, StartDate,DateAdd(dd,-1,Elig_Begin) as EndDate From @var
	Union All 
	Select Emp_Id,Elig_Begin,Elig_End From @var 
	Union All
	Select Emp_Id, dateadd(dd,1,Elig_end), EndDate From @var
) as f 
Inner Join 
(Select Emp_ID as EmpID,DateDiff(mm,Elig_Begin,Elig_End) As Value From @var) as d 
on d.EmpID = f.Emp_Id 


Select * From @NewTable

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 09:36:09

There are so many other considerations to attend to.
What if, for example, EndDate < Elig_Begin? Or EndDate > Elig_Begin an EndDate < Elig_End?

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-11 : 09:41:17

but the orginal poster, mentioned that it only depends on Elig_Begin and Elig_End column... for stating 'Y' or 'N'..

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 09:45:31

Yes. What if EndDate is 20060911 and Elig_Begin is 20060901 and Elig_End is 20060930?
Should the 20060911 or 20060930 date be set as end date for elig period?

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-11 : 09:49:49

I dont know what he wants but what i assumed is EndDate > Elig_End ..

Chirag

shankar6666
Starting Member

8 Posts

Posted - 2006-09-11 : 10:06:53

First of all, thanks for your response. I'll explain you the scenario exactly. I think i've messed it up.

THE BASIC CONDITION IS THAT "I MAY WANT TO INSERT MORE THAN ONE ROW INTO THE NEW TABLE DEPENDING UPON THE ELIGIBILITY OF THe EMPLOYEES"

Please have a look into Effective dates and Eligibility dates in the example before proceeding.

Existing EMPLOYEE table (with 3 employee's details)
---------------
Thanks,
Ravi
ORIGINAL EFF DATES ELIGIBLITY DATES
emp_Id StartDate EndDate Elig_Begin Elig_End
-----------------------------------------------------
1 01/01/2006 12/31/2006 06/01/2006 11/30/2006
2 01/01/2005 12/31/2005 06/01/2004 05/31/2005
3 01/01/2003 12/31/2003 06/01/2002 05/31/2004
-----------------------------------------------------
In this table, there are 3 employees.

SCENARIO 1
------------
emp. 1 has elig. dates overlapping with original dates.Although he is enrolled from 01/2006 to 12/2006, he is eligible only from 06/2006 to 11/2006. from 01/2006 to 05/2006, he is not eligible.Also, for 12/2006 also, he is not eligible. So, his original Effective period need to have 3 partitions in the New table as follows.

NEW TABLE
----------
emp_Id StartDate EndDate Elig_Indicator
-----------------------------------------
1 01/01/2006 05/31/2006 N (elig. period start only on 06/2006)
1 06/01/2006 11/30/2006 Y (elig period)
1 12/01/2006 12/31/2006 N (elig period ended on 11/2006)

SCENARIO 2
------------
emp. 2 has elig_begin date overlapping within original dates. But elig_end is outside the original end date. He is eligible from 06/2004. But he is effective only from 01/2005 (Original Eff Date), we will consider only from then for his eligibility upto 05/2005 as specified. After that, he is ineligible for the rest of the eff period. So ,his effective period has got 2 partitions as shown.

NEW TABLE
----------
emp_Id StartDate EndDate Elig_Indicator
---------------------------------------------
2 01/01/2005 05/31/2005 Y (elig period )
2 06/01/2005 12/31/2005 N (elig ended on 5/2006)

SCENARIO 3
------------
for employee 3, the elig date starts before original eff dt, and ends only after original end date. So, in his case, he has eligibility for the whole effective period. So, only 1 row will be sufficient.

NEW TABLE
----------
emp_Id StartDate EndDate Elig_Indicator
---------------------------------------------
3 01/01/2003 12/31/2003 Y (eligible throughout)

I think from these 3 scenarios, you might have got some picture of the logic need to be implemented.
I want to split the employee Eff dt(Start dt and end dt) for each employees based on the eligiblity dates(Elig_Begin and Elig_End) with one flag indicating whether he is eligible for that period or not.(Y/N)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 10:14:56

In this simple scenario, I think the suggestion I posted earlier will suffice.

Peter Larsson
Helsingborg, Sweden

shankar6666
Starting Member

8 Posts

Posted - 2006-09-11 : 10:39:13

Peter,

Thanks for the query. it works fine for the first 2 scenarios. But, for the 3rd, It is not working correctly.

SCENARIO 3
-----------
ORIGINAL
---------

emp_Id StartDate EndDate Elig_Begin Elig_End
----------------------------------------------
3 01/01/2003 12/31/2003 06/01/2002 05/31/2004

INSERTED VALUE
--------------
emp_Id StartDate EndDate Elig_Ind
------------------------------------------------------
3 06/01/2002 05/31/2004 Y /* NOT CORRECT */
------------------------------------------------------

It should rather insert:
------------------------------------------------------
3 01/01/2003 12/31/2003 Y

shankar6666
Starting Member

8 Posts

Posted - 2006-09-11 : 11:22:52

Thanks a lot peter. I Made some changes to your query and got the intended result. The query is quoted below for your refernce. Once again THANKS A TON for your help.

INSERT NewTable
(
Emp_ID,
StartDate,
EndDate,
Elig_Ind
)
SELECT Emp_ID,
StartDate,
DATEADD(day, -1, Elig_Begin),
'N'
FROM OldTable
WHERE StartDate < Elig_Begin
UNION ALL
SELECT Emp_ID,

Case When Elig_Begin > StartDate Then Elig_Begin
Else StartDate
End,
case When Elig_End > EndDate Then EndDate
Else Elig_End
End,
'Y'
FROM OldTable

UNION ALL
SELECT Emp_ID,
DATEADD(day, 1, Elig_End),
EndDate,
'N'
FROM OldTable
WHERE Elig_End < EndDate

shankar6666
Starting Member

8 Posts

Posted - 2006-09-11 : 11:28:50

Friends,

THANKS A LOT FOR YOUR CONTRIBUTIONS which helped me formulating the Final Query. Special Thanks to Peter, Chirag and Smith. Hope you guys would help again in case of other difficult situations like this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources