shagil.a.gopinath (5/31/2016)
Hi JohnStill its not meet my requirement, i need only the total hours as result. In the below query i get the total hours but i am not able to filter only the status = "I"
with cteMax as
(
SELECT TOP 1 StatusTime, SiteCode
FROM [Temp2]
WHERE [CreateDate] > DateAdd(DAY, -1, GETDATE()) and [CreateDate]<=GETDATE() and SiteCode = '11119'
and Status = 'I'
group by Status, StatusTime, siteCode
order by StatusTime desc
), cteMin as
(
SELECT TOP 1 StatusTime, SiteCode
FROM [Temp2]
WHERE [CreateDate] > DateAdd(DAY, -1, GETDATE()) and [CreateDate]<=GETDATE() and SiteCode = '11119'
and Status = 'I'
group by Status, StatusTime, siteCode
order by StatusTime
)
select DATEDIFF(HOUR,MIN(cteMin.StatusTime), MAX(cteMax.StatusTime)), MIN(cteMin.StatusTime), MAX(cteMax.StatusTime) from cteMin
inner join cteMax on cteMin.SiteCode = cteMax.SiteCode
assuming you are replying to me (my name is not John by the way)
the code I gave you gives the results you asked for based on your sample data (Result expected for the code 111 is 1 Hour and for the code 112 its 2 hours,)
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day