Optimize this query? - 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)

Optimize this query?

Author

Topic

dlorenc
Posting Yak Master

172 Posts

Posted - 2008-10-23 : 11:23:43

this query works, but I'm thinking there maybe a way to make it more readable? The question is "given a status report weekly period of sunday thru saturday, what status reports are late?" So I find today, determine the date of last sunday (AS sunday), turn that into an integer (AS intsunday), and get the status report date and turn that into an integer (AS intstatudate)... subtract (as Diff) and use that in the where clause >8 to see if the status report date is older than 8 days.

especially the repetitive date calculations in the select or where statements using the convert clause (see the underlined)??? I was wondering if once a column is defined using the 'as', if it can be referred to further down in the query like in the DIFF or in the Where???

SELECT TOP 100 PERCENT EPMPortal.Project.ManagementLead, EPMPortal.Project.PlannedStartDate, EPMPortal.Project.PlannedEndDate, EPMPortal.Project.ActualStartDate, EPMPortal.Project.ActualEndDate, EPMPortal.StatusReports.StatusDate,

CONVERT(smalldatetime,CONVERT(varchar(25), DATEADD(day, 1 - DATEPART(weekday, GETDATE()), GETDATE()), 107)) AS sunday,

CONVERT(int, CONVERT(smalldatetime,CONVERT(varchar(25), DATEADD(day, 1 - DATEPART(weekday, GETDATE()), GETDATE()), 107))) AS intsunday,

CONVERT(int,EPMPortal.StatusReports.StatusDate) AS intstatusdate,

CONVERT(int, CONVERT(smalldatetime, CONVERT(varchar(25), DATEADD(day,1 - DATEPART(weekday, GETDATE()), GETDATE()), 107))) - CONVERT(int, EPMPortal.StatusReports.StatusDate) AS Diff

FROM EPMPortal.StatusReports INNER JOIN EPMPortal.Project
ON EPMPortal.Project.Project_ID = EPMPortal.StatusReports.Project_ID

WHERE (EPMPortal.StatusReports.StatusDate =
(SELECT MAX(StatusDate)
FROM EPMPortal.StatusReports AS b
WHERE (statusreports.project_ID = Project_ID))) AND
(CONVERT(int, CONVERT(smalldatetime, CONVERT(varchar(25), DATEADD day,
1 - DATEPART(weekday, GETDATE()), GETDATE()), 107))) -
CONVERT(int, EPMPortal.StatusReports.StatusDate) >= 8)

Thanks for the help

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-23 : 11:35:28

datediff(dd,fromdate,todate)

dlorenc
Posting Yak Master

172 Posts

Posted - 2008-10-23 : 11:44:32

that works much easier than the raw calculation...thank you...

now, if I USE the datediff in the select and use the AS to define it..can I use it like a variable in the where clause at the bottom?...ie. where DateDiff >=8...?

DATEDIFF(dd, EPMPortal.StatusReports.StatusDate, CONVERT(smalldatetime, CONVERT(varchar(25),DATEADD(day, 1 - DATEPART(weekday, GETDATE()), GETDATE()), 107))) AS DateDiff

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 11:48:40

quote:
Originally posted by dlorenc

that works much easier than the raw calculation...thank you...

now, if I USE the datediff in the select and use the AS to define it..can I use it like a variable in the where clause at the bottom?...ie. where DateDiff >=8...?

DATEDIFF(dd, EPMPortal.StatusReports.StatusDate, CONVERT(smalldatetime, CONVERT(varchar(25),DATEADD(day, 1 - DATEPART(weekday, GETDATE()), GETDATE()), 107))) AS DateDiff

you can use it only if you form a derived table out of query

SELECT *
FROM
(
SELECT DATEDIFF(dd, EPMPortal.StatusReports.StatusDate, CONVERT(smalldatetime, CONVERT(varchar(25),DATEADD(day, 1 - DATEPART(weekday, GETDATE()), GETDATE()), 107))) AS [DateDiff],...

)t
WHERE [DateDiff]....

the part in blue is your current query

dlorenc
Posting Yak Master

172 Posts

Posted - 2008-10-23 : 11:56:40

ahh...I see...thank you...

Not knowing the performance impact of a derived table... it seems like just duplicating the query code is the better solution, no?

WHERE (statusreports.project_ID = Project_ID))) AND
DATEDIFF(dd, EPMPortal.StatusReports.StatusDate, CONVERT(smalldatetime, CONVERT(varchar(25),DATEADD(day, 1 - DATEPART(weekday, GETDATE()), GETDATE()), 107))) >= 8)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources