SQL Query for display single records from two records

Question

my table consists data after research my query looks like this Select * From TableName join ( select event, Max (Date_Time) as maxDate FROM TableName group by Event) as t2 on TableName.Event = t2.event and TableName.Date_Time = t2.maxDate UNION Select * From TableName join ( select event, Min (Date_Time) as minDate FROM TableName group by Event) as t2 on TableName.Event = t2.event and TableName.Date_Time = t2.minDate Order By TablName.Event through above Query finally got this result.. in this i need single record for event name burry. i have draw table in below i need like that with that two reocrd

Rijwan Ansari · Answer

You can use datediff to get duration According to your query: Select TableName.StartDate, TableName.StartDate, TableName.Event, DATEDIFF( day , TableName.StartDate, TableName.StartDate) as Duration From TableName join ( select event, Max (Date_Time) as maxDate FROM TableName group by Event) as t2 on TableName.Event = t2.event and TableName.Date_Time = t2.maxDate UNION Select TableName.StartDate, TableName.StartDate, TableName.Event, DATEDIFF( day , TableName.StartDate, TableName.StartDate) as Duration From TableName join ( select event, Min (Date_Time) as minDate FROM TableName group by Event) as t2 on TableName.Event = t2.event and TableName.Date_Time = t2.minDate Order By TablName.Event DATEDIFF (interval, date1, date2) Example: SELECT DATEDIFF( day , StartDate, EndDate') AS Duration from TableName.. You can replace day with other interval based on your requirement. year, yyyy, yy = Year quarter, qq, q = Quarter month, mm, m = month dayofyear = Day of the year day, dy, y = Day week, ww, wk = Week weekday, dw, w = Weekday hour, hh = hour minute, mi, n = Minute second, ss, s = Second millisecond, ms = Millisecond You can try let me know there is any issue. Regards Rijwan

Jhon ABhu · Answer

@Rijwan almost correct but i need End date through your query just got same start date

Malvik Bhavsar · Answer

Dear Jhon Abhu, Please check the following example for more clarification. DECLARE @Bill TABLE ( [Date Time] datetime , TICR04 INT, PICR02 INT, EVENT VARCHAR(20) ) INSERT INTO @Bill ([Date Time],TICR04,PICR02,EVENT) VALUES ('2020-01-22 00:00:00.000',15,1,'Bury'), ('2020-01-23 00:00:00.000',15,1,'Bury'), ('2020-01-24 00:00:00.000',15,1,'Cooling'), ('2020-01-25 15:00:00.000',15,1,'Cooling') --- select * from @Bill --- SELECT min([Date Time]) as Startdate, EVENT,max([Date Time]) as Enddate, convert(varchar(50), min([Date Time]))+ ' - ' + CONVERT(varchar(50), max([Date Time])) as Duration FROM @Bill group by EVENT

Kiran Mohanty · Answer

You need to change the query. Before that can you please post your table data just by running below query? Select * From TableName