getdate()-1 1

[Gatorajc]

I am posting this in 3 forums because I'm not sure who would best answer this so if you have read this before forgive me. Why does the first statement work but take forever and the second one come right up. the only difference is the -1 is there a way I can fix it. The query is called using an ASP page and the table is on an M$ SQL Server.

Select B.UnitName, count(*) as cnt, datediff(d,Create_date,getdate()-1) as DaysOld
From Roster A Left OUTER JOIN Units B ON (B.UnitID = A.UnitID)
Where A.DepartmentID = 120 and datediff(d,Create_date,getdate()-1) < 11 and QName <> 'QAReviewer'
Group by B.UnitName, Create_date
order by B.UnitName ASC


Select B.UnitName, count(*) as cnt, datediff(d,Create_date,getdate()) as DaysOld
From Roster A Left OUTER JOIN Units B ON (B.UnitID = A.UnitID)
Where A.DepartmentID = 120 and datediff(d,Create_date,getdate()) < 11 and QName <> 'QAReviewer'
Group by B.UnitName, datediff(d,Create_date,getdate()), A.DepartmentID
order by B.UnitName ASC AJ
I would lose my head if it wasn't attached.

 

[Tarwn]

Why not use (datediff(d,Create_date,getdate())-1) that way you only have an integer subtraction. the reason the first one (probably, just a guess) takes so long is because it has to resolve the variable type on the 1.

-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
For my next trick I will pull a hat out of a rabbit (if you think thats bad you should see how the pigeon feels...)

 

[Gatorajc]

Actually the query works fine both ways turned out to be something else. Long story with the way the dates and the data related. But I fixed it thanks though. AJ
I would lose my head if it wasn't attached.