DATEDIFF Help - 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

General SQL Server Forums

New to SQL Server Programming

DATEDIFF Help

Author

Topic

emurphy4
Starting Member

17 Posts

Posted - 2010-02-19 : 13:56:38

I have a database with records and the From/To dates in different columns. I need to find records where the To date is within 7 days of the MIN To date and the ID10 is equal. I can't get it to pull the correct records.

Here's one query that I tried.

SELECT ID10 ,ID_2 ,ID_3, DTE_FIRST_SVC, DTE_FIRST_SVC
FROM ##Extract4
GROUP BY ##Extract4.ID10 ,##Extract4.ID_2 ,##Extract4.ID_3 ,##Extract4.DTE_FIRST_SVC ,##Extract4.DTE_LAST_SVC
HAVING DATEDIFF(day,MIN(##Extract4.DTE_FIRST_SVC), MAX(##Extract4.DTE_FIRST_SVC)) < 7
ORDER BY ID10

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 14:00:39

Could you please post sample data and explain what you want?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

emurphy4
Starting Member

17 Posts

Posted - 2010-02-19 : 14:13:18

Here is the sample of data that I'm starting with.

ID10 ID_2 ID_3 DTE_FIRST_SVC DTE_LAST_SVC
3286414 749 979465 10/31/2008 11/14/2008
3286414 749 979465 1/5/2009 1/31/2009
3294312 749 979465 12/24/2008 12/28/2008
3294312 749 979465 12/19/2008 12/23/2008
3312511 749 32424 1/26/2009 1/27/2009
3312511 749 32424 12/9/2008 12/12/2008
3389402 749 979465 11/22/2008 11/25/2008
3389402 749 979465 7/7/2008 7/10/2008
3389402 749 979465 10/19/2008 10/22/2008
3389402 749 979465 10/6/2008 10/8/2008
3389402 749 979465 7/12/2008 7/27/2008

Here are the records that should be pulled, because that have the same ID10 and the DTE_FIRST_SVC is within 7 days of the MIN(DTE_LAST_SVC).

ID10 ID_2 ID_3 DTE_FIRST_SVC DTE_LAST_SVC
3294312 749 979465 12/24/2008 12/28/2008
3294312 749 979465 12/19/2008 12/23/2008
3389402 749 979465 7/12/2008 7/27/2008
3389402 749 979465 7/7/2008 7/10/2008

Let me know if this makes sense.
Thanks.
EMM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 14:21:42

why didnt below got included in output?

3312511 749 32424 12/9/2008 12/12/2008

min date for group is 12/12/2008 and first date is only 3 days behind??

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-19 : 14:25:35

This?
EDIT : Removed the code...was only creating more confusion.

Ohh..yeah..and Visakh's question too...

emurphy4
Starting Member

17 Posts

Posted - 2010-02-19 : 14:35:01

The ID10 3312511 only has one other record and the date is outside 7 days.
Sorry, I don't think I was clear on my issue. It's the first date of a record being within 7 days of the LAST_Date of the MIN9LAST_Date.

In the extracted date here the ID10 was equal and 12/24/2008 was within 7 days of 12/23/2008.

3294312 749 979465 12/24/2008 12/28/2008
3294312 749 979465 12/19/2008 12/23/2008

quote:
Originally posted by visakh16

why didnt below got included in output?

3312511 749 32424 12/9/2008 12/12/2008

min date for group is 12/12/2008 and first date is only 3 days behind??

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-19 : 14:38:00

How do you decide the 'first' record? Is it ordered by DTE_FIRST_SVC descending?

emurphy4
Starting Member

17 Posts

Posted - 2010-02-19 : 14:40:43

I guess that's part of the probably is determining the MIN(date) where ID10 = ID10?

quote:
Originally posted by vijayisonly

How do you decide the 'first' record? Is it ordered by DTE_FIRST_SVC descending?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-19 : 14:48:10

No I dont think you understood my question...per your earlier statement..

quote:
It's the first date of a record being within 7 days of the LAST_Date of the MIN9LAST_Date.

How do you decide first record? If its based on record having latest DTE_FIRST_SVC ..then still your expected output doesn't match...

Because the first record of 3389402 ...does not have the date within 7 days of MIN(DTE_LAST_SVC)

emurphy4
Starting Member

17 Posts

Posted - 2010-02-19 : 14:54:07

For that example, 07/12/2008 is within 7 days of 07/10/2008.

3389402 749 979465 7/12/2008 7/27/2008
3389402 749 979465 7/7/2008 7/10/2008

quote:
Originally posted by vijayisonly

No I dont think you understood my question...per your earlier statement..

quote:
It's the first date of a record being within 7 days of the LAST_Date of the MIN9LAST_Date.
How do you decide first record? If its based on record having latest DTE_FIRST_SVC ..then still your expected output doesn't match...

Because the first record of 3389402 ...does not have the date within 7 days of MIN(DTE_LAST_SVC)

emurphy4
Starting Member

17 Posts

Posted - 2010-02-19 : 14:55:50

Basically, I need to find where an ID checked out(DTE_LAST_SVC) and then checked back in (DTE_FIRST_SVC) within 7 days.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-19 : 15:03:18

OK..I seem to get it...but why both the rows...isn't it fine if output is like this?

3294312 749 979465 12/24/2008 12/23/2008
3389402 749 979465 7/12/2008 7/10/2008

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-19 : 15:04:11

If thats ok...try this

select a.ID10 ,a.ID_2 ,a.ID_3, a.DTE_FIRST_SVC,b.DTE_LAST_SVC
from
##Extract4 a
inner join 
(
SELECT ID10,MIN(DTE_LAST_SVC) AS DTE_LAST_SVC 
FROM ##Extract4
GROUP BY ID10
) b
ON a.ID10 = b.ID10 
where dateadd(dd,7,b.DTE_LAST_SVC) > a.DTE_FIRST_SVC AND b.DTE_LAST_SVC < a.DTE_FIRST_SVC

emurphy4
Starting Member

17 Posts

Posted - 2010-02-19 : 15:12:08

Very close, thanks. I just need to get the first record with it, but this helps a lot.
thanks, again.

quote:

Originally posted by vijayisonly

If thats ok...try this

select a.ID10 ,a.ID_2 ,a.ID_3, a.DTE_FIRST_SVC,b.DTE_LAST_SVC
from
##Extract4 a
inner join 
(
SELECT ID10,MIN(DTE_LAST_SVC) AS DTE_LAST_SVC 
FROM ##Extract4
GROUP BY ID10
) b
ON a.ID10 = b.ID10 
where dateadd(dd,7,b.DTE_LAST_SVC) > a.DTE_FIRST_SVC AND b.DTE_LAST_SVC < a.DTE_FIRST_SVC

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-19 : 15:29:29

Ok. Great. Good luck

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources