Click here to Skip to main content
15,850,299 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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 22:37pm
Comments
jsc42 28-Oct-19 7:35am    
I've learnt something new today, having looked at your SQL. I did not know that the ELSE clause in a CASE was optional. I've looked it up and the docs say it defaults to ELSE NULL. I think that I'll still always put an explicit ELSE in my code just to confirm that I've at least considered the possibility; but that is the exciting thing about reading other people's code - there is always new things to discover or new ways of doing old things.

1 solution

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[^]
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900