SQL change data format for min and max values
I have attendance data coming to me in this format:
EmployeeId | Date | Time |
--------------------------------------
1 | 01/01/2015 | 14:01:35 |
2 | 01/01/2015 | 13:00:00 |
1 | 01/01/2015 | 17:35:37 |
1 | 01/01/2015 | 18:01:35 |
2 | 01/01/2015 | 19:14:12 |
1 | 01/02/2015 | 14:21:42 |
2 | 01/02/2015 | 15:12:03 |
1 | 01/02/2015 | 15:42:20 |
I need this data in the following format:
EmployeeId | Date | FirstTime | LastTime |
--------------------------------------------------
1 | 01/01/2015 | 14:01:35 | 18:01:35 |
2 | 01/01/2015 | 13:00:00 | 19:14:12 |
1 | 01/02/2015 | 14:21:42 | 15:42:20 |
2 | 01/02/2015 | 15:12:03 | 15:12:03 |
FirstTime
- it is Min(Time)
for whom date/employee
and lasttime
have max(time)
for this date/employee
. I've removed Auto increment PK for simplicity.
I can't get my head around how to do this. I thought there Pivot
might be a way to go, but I couldn't use it for the effect I wanted. Can you help me how to do this?
source to share
You can use Pivot, here's an example:
CREATE TABLE Product(Cust VARCHAR(25), Product VARCHAR(20), QTY INT)
GO
-- Inserting Data into Table
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','VEG',2)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','SODA',6)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','MILK',1)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','BEER',12)
INSERT INTO Product(Cust, Product, QTY)
VALUES('FRED','MILK',3)
INSERT INTO Product(Cust, Product, QTY)
VALUES('FRED','BEER',24)
INSERT INTO Product(Cust, Product, QTY)
VALUES('KATE','VEG',3)
GO
-- Selecting and checking entires in table
SELECT *
FROM Product
GO
-- Pivot Table ordered by PRODUCT
SELECT PRODUCT, FRED, KATE
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT (SUM(QTY) FOR CUST IN (FRED, KATE)) AS pvt
ORDER BY PRODUCT
GO
-- Pivot Table ordered by CUST
SELECT CUST, VEG, SODA, MILK, BEER, CHIPS
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT (SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt
ORDER BY CUST
GO
Relative link: link
source to share