SQL Query for display single records from two records

Jhon ABhu

5y1k

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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rijwan Ansari5y ago
Accepted 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 ABhu5y ago
@Rijwan almost correct but i need End date through your query just got same start date
Malvik Bhavsar5y ago
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 Mohanty5y ago
You need to change the query. Before that can you please post your table data just by running below query?

Select * From TableName