Need help with DateDiff - Aging Report

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

Development Tools

Reporting Services Development

Need help with DateDiff - Aging Report

Author

Topic

proflola
Starting Member

2 Posts

Posted - 2007-10-29 : 13:48:19

Hi all,

My company is interested in learning how long it takes to respond to a claim. Ideally, they'd like to be able to put their own parameters to the length of the age, but for now it can be set.

Here's what I did in Access that looked like this:

SELECT Count(TLK_CurrRequest.CurrReqID) AS [Age 1-30]
FROM TLK_CurrRequest
WHERE ((([TLK_CurrRequest].[date_compl]) Is Null) AND ((Date()-[Date_Entered]) Between 1 And 30));

Now, I'm on a project where I'm learning to do new development in the SQL 2005 Reporting Services. A developer told me that DATEDIFF might be a better way to go. I thought he told me that the DATEDIFF statement would look like this, but I've got something wrong.

DateDiff("day", [Date_compl], days)/30

What am I doing wrong??? Thanks for any help you can give me

--Sandy

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-29 : 14:46:53

There is an average of 365.2425 days in a year...

E 12°55'05.25"
N 56°04'39.16"

Scott.Thornburg
Starting Member

10 Posts

Posted - 2007-11-05 : 21:07:31

Sandy,

If I understand correctly, you want to find rows with a [Data_Entered] that is between 1 and 30 days ago (e.g., not today).

To do this, you can indeed use DateDiff:

where Datediff(day, [Date_Entered], getdate() ) between 1 and 30.

However, if your table is large, you lose the benefit of any index on the column Date_Entered. I would STRONGLY suggest using an approach like this:

where Date_Entered between convert(int, getdate() - 30) and convert(int, getdate() - 1)

The convert(int, ) is to deal with whole days, rather than worrying about whether 2 pm today is a full day since 3 pm yesterday. I can explain more if you want. The main point is to not wrap your main column in a function, which prevents the index use.

I did a quick test of the two methods in a moderate-sized table -- about 1 million rows.

Query #1 (no index) 3790 ms; 67652 IOs
Query #2 (used index) 74 ms; 27 IOs

The second query was 50x faster and used 0.04% of the IO activity as the first.

Moral of the story: Think about your data access plans.

I know this is more than you originally asked for, but I hope it helps.

Scott Thornburg

proflola
Starting Member

2 Posts

Posted - 2007-11-06 : 07:32:00

Scott,

Thanks for the quick reply

. Your post was very informative and I'll try using the Convert. Your test results made me realize how important it is to do things "right".

Thanks, again,

Sandy

Scott.Thornburg
Starting Member

10 Posts

Posted - 2007-11-06 : 12:26:18

Sandy,

You are welcome. With SQL it's quite easy to come up with alternate methods that have identical results and can differ widely in the impact on applications and servers.

Always start with best practices.

Good luck,

Scott

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-06 : 14:00:38

quote:
Originally posted by Scott.Thornburg

Sandy,

You are welcome. With SQL it's quite easy to come up with alternate methods that have identical results and can differ widely in the impact on applications and servers.

Always start with best practices.

Good luck,

Scott

I wouldn't call you method of converting a datetime to a simple date by converting a date time value to an integer a "best practice". It produces a different date value, depending on the time portion of the date. If the time in 11:59:59.997 or later, the time is rounded up to the next day, and if it is before that, it is rounded down to the next day.

The method shown below using dateadd/datadiff rounds down to the start of the day correctly.


select
	[Date Int]		= convert(datetime,convert(int,DT)),
	[Date Dateadd/Datadiff]	= dateadd(dd,datediff(dd,0,DT),0),
	DT
from
	(
	-- Test Data
	select DT = getdate() union all
	select DT = '20071106 11:59:59.993' union all
	select DT = '20071106 11:59:59.997'
	)a

Results:

Date Int                 Date Dateadd/Datadiff    DT
-----------------------  -----------------------  ----------------------- 
2007-11-07 00:00:00.000  2007-11-06 00:00:00.000  2007-11-06 13:56:18.073
2007-11-06 00:00:00.000  2007-11-06 00:00:00.000  2007-11-06 11:59:59.993
2007-11-07 00:00:00.000  2007-11-06 00:00:00.000  2007-11-06 11:59:59.997

(3 row(s) affected)

CODO ERGO SUM

Scott.Thornburg
Starting Member

10 Posts

Posted - 2007-11-07 : 04:16:59

Good point - conversion to integer is not fully clean. I got sloppy on this one, as I was focusing on the use of the index rather than the conversion to the datetime.

Thanks for the correction,

Scott

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources