karen.blake (7/7/2015)
Hi John,Thanks for your reply, you might have lost me on that one could you explain that further?
Chris the datatype is datetime for that field if that helps.
Thanks. Couple of simplifications here:
select
c.APPLICANT_ID as [Applicant ID],
aetc.EVENT_TYPE as [Event Type],
cast(aetr.CREATE_DATE as date) as [Registration Date],
cast(aetc.CREATE_DATE as date) as [C Creation Date],
datediff(mi, aetr.CREATE_DATE, aetc.CREATE_DATE) as [time diff],
cast(c.CREATE_DATE as date) as [c create date],
app.APPLICATION_ID as [Application ID]
from c C
join AET_REGISTRATION AETR
on c.APPLICANT_ID = AETR.APPLICANT_ID
join AET_C AETC
on c.APPLICANT_ID = aetc.APPLICANT_ID
left join [application] app
on c.APPLICANT_ID = app.APPLICANT_ID
where C.CREATE_DATE >= DATEADD(month,-12,CAST(GETDATE() AS DATE)) -- simpler than original
and aetc.EVENT_TYPE in ('new_cv_dist', 'new_cv_hide')
and c.CREATE_DATE < aetc.CREATE_DATE
and app.application_id is not null
order by c.CREATE_DATE asc;
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden