I am having SQL table where the records of the employee on daily basis are stored/saved I would like to get that result in tabular format as shown in attached screenshots i am using SQL 2012
ALM TIME TAGNAME STATUS NORMSTS UNIT TEMP OPERNAME
2019-07-07 10:10:00 A LO C 18.5 JOHN
2019-07-07 10:11:00 B LO C 14.2 WILLIAM
2019-07-07 10:14:00 C LO C 14.36 WILLIAM
2019-07-07 10:15:00 B OK C 12.01 KATE
2019-07-07 10:17:00 A OK F 17 JOHN
2019-07-07 10:18:00 A Y C 17.26 JOHN
2019-07-07 10:19:00 D LO PA 11.2 KATE
2019-07-07 10:21:00 C OK C 22.21 WILLIAM
2019-07-07 10:24:00 E LO C 27.5 JOHN
2019-07-07 10:25:00 E OK C 28.1 KATE
2019-07-07 10:27:00 D OK PA 15.24 JOHN
2019-07-07 10:29:00 B Y C 15.36 WILLIAM
2019-07-07 10:30:00 D Y F 17.25 JOHN
ALM IN ALM OUT TAG UNIT TEMP OPERNAME ALM ACK TIME
2019-07-07 10:10:00 2019-07-07 10:17:00 A C 18.5 JOHN 2019-07-07 10:18:00
2019-07-07 10:11:00 2019-07-07 10:15:00 B C 14.2 WILLIAM 2019-07-07 10:29:00
2019-07-07 10:14:00 2019-07-07 10:21:00 C C 14.36 WILLIAM -----
2019-07-07 10:19:00 2019-07-07 10:27:00 D C 15.54 KATE 2019-07-07 10:30:00
What I have tried:
<pre>
select ALM_DESCR,
max(case when [ALM_ALMSTATUS] = 'lo' then [ALM_NATIVETIMELAST] end) as intime,
max(case when [ALM_ALMSTATUS] = 'ok' then [ALM_NATIVETIMELAST] end) as outtime
from fixalarms WHERE ALM_NATIVETIMELAST BETWEEN '2019-05-20 06:00:00' AND '2019-05-20 21:00:00'
group by ALM_DESCR;