DATE Comparison in HAVING Clause

Question

DATE Comparison in HAVING Clause

honey47

SSC-Addicted

Points: 459
More actions
December 15, 2010 at 1:55 am

#228488

I am getting incorrect results while trying to get the all dates less than the current date
The query is
SELECT Convert(varchar(12),(MAX(VisitOn) + CASE WHEN (PrimeStatus = 'PRIME' OR PrimeStatus = 'T-PRIME') THEN 300/24 ELSE 350/'8' END ),106) from tblSites A
LEFT JOIN tblActivityMaster B on A.SiteID=B.SiteId
LEFT JOIN tblActivityParts C on B.activityRefNo=C.ActivityRefNo
LEFT JOIN tblGensetActivity D on B.ActivityRefNo=D.ActivityRefNo
LEFT JOIN tblStoreInventory E on C.StoreCode=E.ItemCode
WHERE 1=1AND Center in ('KARACHI') and activityType='PM' and GensetName is NOt Null
GROUP BY a.SiteId, GensetName,primeStatus
HAVING (Convert(varchar(12),(MAX(VisitOn) + CASE WHEN (PrimeStatus = 'PRIME' OR PrimeStatus = 'T-PRIME') THEN 300/24 ELSE 350/'8' END ),106)) <= '15 Dec 2010'
The result should be the dates less than 15-dec-2010
but i am getting 20-jan-2011 as well
i guess there is some thing wring in HAVING CLAUSE

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post table definitions, sample data and expected results as per http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass