Compare Dates

Question

Post reply

Compare Dates

ssc_san

SSCommitted

Points: 1573

June 26, 2019 at 3:47 pm

#3657782

Hello All,

create table #app1
(
custID INT,
Seqs INT,
eff_dt datetime,
term_dt datetime
)

insert into #app1
select 123, 100, '07/01/2017', '12/31/2017'
union all
select 123, 200, '01/01/2018', NULL
union all
select 456, 021, '08/01/2017', '11/30/2017'
union all
select 456, 021, '12/01/2018', '02/28/2019'
union all
select 456, 031, '03/01/2019', NULL
union all
select 789, 033, '01/01/2017', '12/31/2017'
union all
select 789, 040, '01/01/2018', '12/31/2018'
union all
select 789, 050, '01/01/2019', NULL

create table #app2
(
custID INT,
Seqs INT,
eff_dt datetime,
term_dt datetime
)

insert into #app2
select 123, 100, '07/01/2017', '12/31/2017'
union all
select 123, 200, '01/01/2018', '09/01/2018'
union all
select 123, 200, '10/01/2018', '12/31/2019'
union all
select 456, 021, '08/01/2017', '02/28/2019'
union all
select 456, 031, '03/01/2019', NULL
union all
select 789, 040, '01/01/2018', '05/31/2018'
union all
select 789, 040, '07/01/2018', '12/31/2018'
union all
select 789, 050, '01/01/2019', NULL



select * from #app1 a left join #app2 b
on a.custID = b.custID
and a.Seqs = b.Seqs
where a.custID = 789
and b.custID is null


drop table #app1;
drop table #app2;

I have difficulty figuring out date comparison between data from two applications (Code for test data above.)

I have #app1 table and #app2 table and I am trying to compare the effective and term dates, so there is a possibility of the dates to be split between the application, either on #app1 table or #app2 table, I have compare #app2 display the differences in #app2 table, so I have a left join and app2 table custID null. There is a possibility of the gaps between the dates and I have sample data for that scenario as well. How do I include all the possibilities in the query?

Thank you for your time! sincerely appreciate your help.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Thom A SSC Guru Points: 99133 More actions · Answer 1

Not sure I understand what you're after here. For what you've provided what is the result set you're after?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

ssc_san SSCommitted Points: 1573 More actions · Answer 2

Hi,

Thanks for the reply.

I am trying to compare the data from two tables and display any difference in coverage in #app2 table. There is gap in coverage for CustID 798 in #app2 table, so I want to display the details for the custID and Seq from #app1 to check the discrepancy later, and another scenario is of an extra record(789, 050, '01/01/2019', NULL) that I want to display in the result set.

In short, I am trying to compare any discrepancy in coverage as the dates are split between applications.

Thank you!

jonathan.crawford SSCertifiable Points: 6613 More actions · Answer 3

You can't compare anything to NULL, as NULL is undefined, do you need to convert those to current date/time or a future default date to do the comparison? Our system of record likes to use '12/31/2078' for example.

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

ssc_san SSCommitted Points: 1573 More actions · Answer 4

Yes, to some high date as '12/31/2099'

Thank you for your help!