I am doing a query on finding out when a service is suspended.
I have a created date. I have a suspended date. So far so good. However some servies are suspended multiple times. Currently I just need to know the amount of days between the created date and the first suspended date. So I
thought it might be easier to create a date difference column to get over this. However when I ran the data I got information like this. So how can I be certain to pick up the first row only of tpk1? Any help would be great on this.
Don't have your complete schema details, therefore guessing structure. So, try something like below query:
SELECT ServiceID, Min(SuspendedDate) FirstSuspendedDateTime
GROUP BY ServiceID
SELECT S.ServiceID, S.ServiceName, S.CreatedDate, DateDiff(Day, S.CreatedDate, CS.FirstSuspendedDateTime) AS NoOfDaysServiceSuspended
FROM Services AS S
INNER JOIN CTE_Services AS CS
ON S.ServiceID = CS.ServiceID