How to create a view that separate dates in two fields by their type in SQL

Im trying to use this table Alarms:

ID        CarID  Location           Date          Type 897847    7198      3       2019-11-26 11:54:54    20 897852    7198      3       2019-11-26 11:55:14    21 1001987   7198      3       2019-12-10 16:08:28    20 1001988   7198      3       2019-12-10 16:08:38    21 

To create a view that separates the Date by their type (20 or 21) and using timediff to calculate the difference between then and respecting same carID and Location, to get something like this:

ID        CarID  Location      InicialDate(20)        EndDate(21)          Total 897847    7198      3       2019-11-26 11:54:54   2019-11-26 11:55:14     00:00:20 1001987   7198      3       2019-12-10 16:08:28   2019-12-10 16:08:38     00:00:10 

There will be cases that will appear dates with Type 20 2,3 or 4 times before appear a date with Type 21, so i need to always get the first date Type 20 with first date type 21, second type 20 with second type 21 and so on always respecting same carID and Location, cannot be same carid and different location and vice versa.

Any ideas that can help me? Thank you

Asked on September 1, 2020 in Sql.
Add Comment
1 Answer(s)

For reference Check here

;with cte as (select dense_rank() over( order by CarID,Location,cast(Date as date)) dr, * from tbl),cte1 as( select dr,min(ID) ID,CarID,Location,type,min(date) min_date,max(date) max_date from cte group by dr,CarID,Location,type) select min(ID) ID,CarID,Location,min(min_date) [InicialDate(20)],max(max_date)[EndDate(21)], datediff(second,min(min_date),max(max_date)) total from cte1 group by CarID,Location,dr 
Answered on September 1, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.