Date Comparison Problem

Question

Post reply

Date Comparison Problem

Jared

SSC Guru

Points: 61793
More actions
May 27, 2010 at 8:02 am

#223046

I am having a problem trying to take a date in one week and see if another date in that row in in the previous week.
Basically, we have contracts that have a SIGNING_DATE and an ENTRY_DATE. Contracts that are signed in one week must be entered in the next week. i.e. Signed between 5/9/10 and 5/15/10 must be entered into the database between 5/16/10 and 5/22/10. I need to find records that do not conform to this rule.
Any thoughts on this would be much appreciated.
Thanks,
Jared
Jared
CE - Microsoft

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

jared here's an example i think can show you the idea;

you want to use BETWEEN two dates, and use DATEADD to determin the range based on another date:

--results

SIGNING_DATEENTRY_DATE

2010-05-01 00:00:00.0002010-05-04 00:00:00.000

2010-05-01 00:00:00.0002010-05-07 00:00:00.000

create table example( SIGNING_DATE datetime, ENTRY_DATE datetime)

insert into example

SELECT '20100501 00:00:00.000','20100504 00:00:00.000' UNION ALL --within one week

SELECT '20100501 00:00:00.000','20100507 00:00:00.000' UNION ALL --barely within one week

SELECT '20100501 00:00:00.000','20100510 00:00:00.000' UNION ALL --outside of range

SELECT '20100501 00:00:00.000','20100515 00:00:00.000' --outside of range

select * from example

where SIGNING_DATE

--within 7 days before entry date and entry date itself

BETWEEN DATEADD(ww,-1,ENTRY_DATE) AND ENTRY_DATE

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jared SSC Guru Points: 61793 More actions · Answer 2

Thanks for the quick response, but I think the problem with this approach is that it is within 7 days of entry date. If the contract is signed on 20100405 and entered on 20100507, it is still abiding by the rule. It is not a matter of the amount of days between entry, but dependent on week.

Thanks,

Jared

Jared
CE - Microsoft

Lowell SSC Guru Points: 323518 More actions · Answer 3

still the same idea, i think; for example the week of today is 22:

select datepart(week,getdate())

so biz day wise, m-f of this week is 22, m-f of last week is 21;

so to compare weeks it's this code from my previous example:

select * from example

where datepart(week,SIGNING_DATE )

--within 7 days before entry date and entry date itself

BETWEEN datepart(week,ENTRY_DATE -7 ) AND datepart(week,ENTRY_DATE)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jared SSC Guru Points: 61793 More actions · Answer 4

Hmm... I tried this and it still does not work. I believe that this query is returning the rows that DO follow the rule. Not the ones that do not. Also, this does not account for the last and first weeks of a year. I am looking for a solution that can handle both. Thanks for your quick response again.

Thanks,

Jared

Jared
CE - Microsoft

Lowell SSC Guru Points: 323518 More actions · Answer 5

it's all good; i just moved the WEHRE statment into a CASE so i could print a status:

SIGNING_DATEENTRY_DATETimeStatus

2010-05-01 00:00:00.0002010-05-04 00:00:00.000On Time Signing

2010-05-01 00:00:00.0002010-05-07 00:00:00.000On Time Signing

2010-05-01 00:00:00.0002010-05-10 00:00:00.000Exception Not within Time range

2010-05-01 00:00:00.0002010-05-15 00:00:00.000Exception Not within Time range

select *,

CASE

WHEN datepart(week,SIGNING_DATE ) BETWEEN datepart(week,ENTRY_DATE -7 ) AND datepart(week,ENTRY_DATE)

THEN 'On Time Signing'

ELSE 'Exception Not within Time range'

END As TimeStatus

from example

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

AppSup_dba SSCrazy Points: 2215 More actions · Answer 6

My two cents:

select *,

CASE

WHEN DATEDIFF(WEEK,signing_date, entry_date)=1

THEN 'On Time Signing'

ELSE 'Exception Not within Time range'

END As TimeStatus

from example

result set:

2010-05-15 00:00:00.0002010-05-15 00:00:00.000Exception Not within Time range

2010-05-15 00:00:00.0002010-05-16 00:00:00.000On Time Signing

2010-05-15 00:00:00.0002010-05-22 00:00:00.000On Time Signing

2010-05-15 00:00:00.0002010-05-23 00:00:00.000Exception Not within Time range

2010-05-15 00:00:00.0002010-05-25 00:00:00.000Exception Not within Time range

2010-05-15 00:00:00.0002011-05-15 00:00:00.000Exception Not within Time range

2010-05-15 00:00:00.0002011-05-16 00:00:00.000Exception Not within Time range

2010-05-01 00:00:00.0002011-05-22 00:00:00.000Exception Not within Time range

2010-05-15 00:00:00.0002020-05-15 00:00:00.000Exception Not within Time range

Cheers

Jared SSC Guru Points: 61793 More actions · Answer 7

That seems to work perfectly for what I need.

Thanks,

Jared

Jared
CE - Microsoft