Merging two tables based on date field

Question

Post reply

Merging two tables based on date field

Karthiart

Ten Centuries

Points: 1070
More actions
September 8, 2015 at 6:02 am

#320290

Hi I have two tables like,
create table dbo.#Status(
ID varchar(50),
Status varchar(50),
EffectiveStartDate datetime,
EffectiveEndDate datetime,
Is_Current bit
)
INSERT INTO #Status VALUES ('1','Same','2009-07-01','2009-09-30',0)
INSERT INTO #Status VALUES ('1','Lost','2009-10-01','2013-12-31',0)
INSERT INTO #Status VALUES ('1','Inter','2014-01-01',NULL,1)
CREATE TABLE dbo.#FourHistory(
ID varchar(50),
Fee varchar(100),
Bill varchar(50),
A_Date date,
B_Date date,
EffectiveStartDate datetime,
EffectiveEndDate datetime
)
INSERT INTO #FourHistory VALUES ('1','Variable','Not Applicable','2006-05-08','2006-05-19','2009-07-17 23:22:07.000','2009-07-18 01:11:26.000')
INSERT INTO #FourHistory VALUES ('1','Non Activity','Not Applicable','2001-01-01','2001-01-01','2009-07-18 01:11:27.000','2010-02-11 12:48:51.000')
INSERT INTO #FourHistory VALUES ('1','Variable','Not Applicable','2009-09-18','2009-10-22','2010-02-11 12:48:52.000','2014-01-17 00:05:29.000')
INSERT INTO #FourHistory VALUES ('1','Daily','Whole','2014-01-15','2014-01-20','2014-01-17 00:05:30.000',NULL)
I want result as the attached image.
Create table query for result is: CREATE TABLE dbo.#Result(
ID varchar(50),
Fee varchar(100),
Bill varchar(50),
A_Date date,
B_Date date,
Status VARCHAR(50),
EffectiveStartDate datetime,
EffectiveEndDate datetime
)
Kindly help, how to achieve this in sql server 2012.
Regards,
Karthik.
Regards,
Karthik.
SQL Developer.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

kdejan87 Right there with Babe Points: 737 More actions · Answer 1

You need to do something like this

Select a,b,c from x

union all -- ALL doesn't filter dups and is faster

Select d, e, 'c' as f from y

Karthiart Ten Centuries Points: 1070 More actions · Answer 2

Hi,

Unionall will append the data. But i need to merge the status column into second table based on the effective start and effective end date.

Regards,
Karthik.
SQL Developer.

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 3

Try this

select

f.ID ,

f.Fee ,

f.Bill ,

f.A_Date ,

f.B_Date ,

EffectiveStartDate = case when f1.EffectiveStartDate < s.EffectiveStartDate

then cast(s.EffectiveStartDate as datetime)

else f.EffectiveStartDate end,

EffectiveEndDate = case when f1.EffectiveEndDate >= s.EffectiveEndDate

then dateadd(second, -1, dateadd(day, 1, cast(s.EffectiveEndDate as datetime)))

else f.EffectiveEndDate end,

s.Status

from dbo.#FourHistory f

cross apply (select

EffectiveStartDate = cast (f.EffectiveStartDate as date),

EffectiveEndDate = cast (f.EffectiveEndDate as date)) f1

join dbo.#Status s

on s.EffectiveStartDate <= isnull(f1.EffectiveEndDate,s.EffectiveStartDate)

and f1.EffectiveStartDate <= isnull(s.EffectiveEndDate,f1.EffectiveStartDate)

Karthiart Ten Centuries Points: 1070 More actions · Answer 4

Hi Tons of thanks first.

I tried for the ID value 1. Its working perfectly. But when i try insert other id into those 2 temp tables. Am not getting as expected. I am also try tweaking your query. Can you please try and help me here?

INSERT INTO #Status VALUES ('2','Same','2009-07-01 00:00:00.000','2013-07-31 23:59:59.000',0)

INSERT INTO #Status VALUES ('2','Lost 13','2013-08-01 00:00:00.000',NULL,1)

INSERT INTO #FourHistory VALUES ('2','Variable','Not Applicable','2010-09-02','2010-09-01','2009-07-18 00:34:15.000',NULL)

Try insert the above records into those temp tables. I am expecting result for ID: 2 as attached.

Regards,
Karthik.
SQL Developer.

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 5

Based on initial sample data and results i assumed that only date part of #Status.EffectiveEndDate is meanigful. That is any of

#Status VALUES ('1','Same','2009-07-01','2009-09-30',0)

('1','Same','2009-07-01','2009-09-30 10:03:17',0) etc

must be treated as EffectiveEndDate = 2009-09-30 23:59:59, which was requiered as a result.

If seems like it's not the case when i look at the second sample. Please clarify the requierments.

Karthiart Ten Centuries Points: 1070 More actions · Answer 6

Hi, Requirement is, i want to merge the status column in second table. For that, i need to consider both tables effective start and end dates.

So, #Fourhistory tables effective start and end will drive the merging concept.

For ex: For ID: 2 in #Fourhistory table, we have effective start as 2009-07-18 and end date as NULL.

Since we have 2 status in first temp table, we need to maintain status "same"'s start and end date as well as for lost13.

Please let me know if you need input.

Regards,
Karthik.
SQL Developer.