Click here to Skip to main content
14,267,437 members
Rate this:
Please Sign up or sign in to vote.
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;
Posted
Updated 7-Aug-19 21:37pm

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

You can use LAG and LEAD functions to fetch data from the neighboring rows. I've posted a small tip about this. Have a look at How to fetch data from the previous or next rows in the resultset[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100