March 12, 2010 at 7:57 am
I am trying to get some records from the view PositionDescriptions_V'
with processcode not in ('v', 'u')
which have a matching record in PD_processhistory table
and actiondate more than 365 days from current date.
Here is my query:
SELECT BusinessAreaID,
PACS_ID,
ProcessName,
PreparationDate,
ValidationDate,
ClassifierSignDate,
SupervisorSignDate,
SubmissionDate,
BusinessAreaNM,
OrganizationCode
FROM dbo.PositionDescriptions_V A
WHERE ProcessCode NOT IN ('V', 'U')
AND EXISTS
(SELECT MIN(B.ActionDate)
FROM dbo.PD_ProcessHistory B
WHERE B.PACS_ID = A.PACS_ID
HAVING DATEDIFF(day, MIN(B.ActionDate), GETDATE()) > 365)
ORDER BY BusinessAreaNM, OrganizationCode,PACS_ID
My results included some records with actiondate in 02/2010, which is not correct.
Thanks,
Arun
March 12, 2010 at 10:25 am
I'm going to assume the column is a datetime and you're not taking into account the time aspect of the date math.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply