Complex Date Computations - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Complex Date Computations

Author

Topic

ucal
Yak Posting Veteran

72 Posts

Posted - 2008-03-06 : 11:17:01

I have a table the looks like this :

ID Var1 Date1
-- --- -------
1 10 18-Feb-02
1 11 23-Dec-02
2 30 27-Nov-02
3 50 09-Oct-02
4 60 25-Mar-02
4 63 10-May-02
5 52 30-May-02
6 69 07-Feb-02
6 74 26-Feb-02
6 80 23-Sep-02
6 90 09-Oct-02
7 123 01-Feb-02
7 129 10-Dec-02
8 56 12-Jan-02
9 10 04-Feb-02
9 15 18-Feb-02
9 19 30-Sep-02
9 20 12-Oct-02
10 21 03-Oct-02
10 23 08-Oct-02
10 24 30-Nov-02

I want to create a table like this :

IF the date (Date1) diffrence between any two dates for the same ID is less than 30 Days CNT1 = 1, ELSE CNT1 =0
IF the date (Date1) diffrence between any two dates for the same ID is less than 60 Days CNT2 = 1, ELSE CNT2 =0
IF the date (Date1) diffrence between any two dates for the same ID is less than 90 Days CNT3 = 1, ELSE CNT1 =0

Note : CNT1,CNT2 and CNT3 for ID =2,3,5 and 8 should be 0 because there are no multiple recors for the same ID

ID CNT1 CNT2 CNT3
-- ---- ---- ----
1
2
3
4
5
6
7
8
9
10

Any help will be welcomed.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-06 : 11:30:15

You need to join the table to itself on the ID column, allowing the date column to "cross join" so that all dates for each ID are compared. From that, we can use DateDiff to get the # of days between the two dates, and use ABS() to ensure we take only the absolute value:

So, we start with this:

select t1.ID, t1.Date1, t2.date1, abs(datediff(day, t1.date1, t2.date1)) as DaysDiff
from yourtable t1
inner join yourtable t2 on t1.id = t2.id and t1.date1 <> t2.Date1

Now, using that as our base, we can select FROM the above SQL and use 3 CASE expressions to determine which of the 3 conditions are true:

select id,
max(case when DaysDiff <=30 then 1 else 0 end) as CNT1,
max(case when DaysDiff <=60 then 1 else 0 end) as CNT2,
max(case when DaysDiff <=90 then 1 else 0 end) as CNT3
from
( THE ABOVE SQL HERE) x
group by id

Keep in mind that as specified, your requirements to break the results out into 3 columns don't seem to make a lot of sense: if CNT3 is 1, then CNT1 and CNT2 will always be 1, and if CNT2 is 1, then CNT1 is always 1.

- Jeff
http://weblogs.sqlteam.com/JeffS

ucal
Yak Posting Veteran

72 Posts

Posted - 2008-03-06 : 12:29:43

Thanks Jeff,
When I want to:

set CNT1 = 1 for a unique ID anytime the date difference between any pair of dates
within an ID is less than 90 Days .

CNT2 = 1 for a unique ID anytime the date difference between any pair of dates within an ID is less than 60 Days.

CNT3 = 1 for a unique ID anytime the date difference between any pair of dates within an ID is less than 30 Days

ucal
Yak Posting Veteran

72 Posts

Posted - 2008-03-06 : 14:15:34

Any ideas on how to skin this cat ?????

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-06 : 14:36:20

uh ... what's wrong with what I just showed you?

- Jeff
http://weblogs.sqlteam.com/JeffS

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-06 : 14:45:56

quote:
Originally posted by jsmith8858

uh ... what's wrong with what I just showed you?

- Jeff
http://weblogs.sqlteam.com/JeffS

"I hate when my code does what I tell it to, and not what I want it to."

Would be more prudent to do >30 >60 >90...at least that way, multiple "true" won't occur.

Poor planning on your part does not constitute an emergency on my part.

ucal
Yak Posting Veteran

72 Posts

Posted - 2008-03-06 : 15:04:45

Thanks Jeff,
I have removed the tree that is preventing me from see the forest !!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources