FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

-- INIT database
CREATE TABLE Customer ( 
  
 ProjectID INT
,ActivityCode VARCHAR(10)
,ActivityDescription VARCHAR(25)
,ActivityResults VARCHAR (25)
,DTTM DATETIME
  
);

INSERT INTO Customer(ProjectID, ActivityCode ,ActivityDescription ,ActivityResults ,DTTM) VALUES ('123456', '15', 'CustomerID', '999', '4/24/24 18:01');
INSERT INTO Customer(ProjectID, ActivityCode ,ActivityDescription ,ActivityResults ,DTTM) VALUES ('123456', '16', 'InvoiceNo', '11232', '4/24/24 18:01');
INSERT INTO Customer(ProjectID, ActivityCode ,ActivityDescription ,ActivityResults ,DTTM) VALUES ('123456', '1745', 'CategoryColor', 'Green', '4/25/2024 09:01');
INSERT INTO Customer(ProjectID, ActivityCode ,ActivityDescription ,ActivityResults ,DTTM) VALUES ('123456', '2256', 'Size', 'Large', '4/25/2024 09:21');
INSERT INTO Customer(ProjectID, ActivityCode ,ActivityDescription ,ActivityResults ,DTTM) VALUES ('123456', '4748', 'Quantity', '7', '4/25/2024 10:05');
INSERT INTO Customer(ProjectID, ActivityCode ,ActivityDescription ,ActivityResults ,DTTM) VALUES ('654321', '15', 'CustomerID', '1745', '4/26/2024 11:15');
INSERT INTO Customer(ProjectID, ActivityCode ,ActivityDescription ,ActivityResults ,DTTM) VALUES ('654321', '16', 'InvoiceNo', '19121', '4/26/2024 11:15');
INSERT INTO Customer(ProjectID, ActivityCode ,ActivityDescription ,ActivityResults ,DTTM) VALUES ('654321', '1745', 'CategoryColor', 'Blue', '4/26/2024 11:15');
INSERT INTO Customer(ProjectID, ActivityCode ,ActivityDescription ,ActivityResults ,DTTM) VALUES ('654321', '2256', 'Size', 'Medium', '4/26/2024 11:15');
INSERT INTO Customer(ProjectID, ActivityCode ,ActivityDescription ,ActivityResults ,DTTM) VALUES ('654321', '4748', 'Quantity', '10', '4/26/2024 11:15');
INSERT INTO Customer(ProjectID, ActivityCode ,ActivityDescription ,ActivityResults ,DTTM) VALUES ('171718', '15', 'CustomerID', '149', '4/20/2024 13:25');
INSERT INTO Customer(ProjectID, ActivityCode ,ActivityDescription ,ActivityResults ,DTTM) VALUES ('171718', '16', 'InvoiceNo', '12001', '4/20/2024 13:25');
INSERT INTO Customer(ProjectID, ActivityCode ,ActivityDescription ,ActivityResults ,DTTM) VALUES ('171718', '1745', 'CategoryColor', 'Yellow', '4/22/2024 12:05');
INSERT INTO Customer(ProjectID, ActivityCode ,ActivityDescription ,ActivityResults ,DTTM) VALUES ('171718', '2256', 'Size', 'Small', '4/22/2024 12:05');
INSERT INTO Customer(ProjectID, ActivityCode ,ActivityDescription ,ActivityResults ,DTTM) VALUES ('171718', '4748', 'Quantity', '3', '4/22/2024 12:05');

-- QUERY database
--SELECT * FROM Customer;
SELECT 
 main.ProjectID
,custid.ActivityResults CustomerID
,invno.ActivityResults InvoiceNo
,color.ActivityResults CategoryColor
,size.ActivityResults Size
,qty.ActivityResults Quantity
,DTTM

FROM (SELECT
 ProjectID
,ActivityCode
,ActivityDescription
,ActivityResults
,DTTM

From Customer
WHERE ActivityCode IN (15,16,1745,2256,4748)) main

LEFT JOIN (SELECT
 ProjectID
,ActivityCode
,ActivityDescription
,ActivityResults
FROM Customer
WHERE ActivityCode = 15) custid ON main.ProjectID = custid.ProjectID

LEFT JOIN (SELECT
 ProjectID
,ActivityCode
,ActivityDescription
,ActivityResults
FROM Customer
WHERE ActivityCode = 16) invno ON main.ProjectID = invno.ProjectID

LEFT JOIN (SELECT
 ProjectID
,ActivityCode
,ActivityDescription
,ActivityResults
FROM Customer
WHERE ActivityCode = 1745) color ON main.ProjectID = color.ProjectID

LEFT JOIN (SELECT
 ProjectID
,ActivityCode
,ActivityDescription
,ActivityResults
FROM Customer
WHERE ActivityCode = 2256) size ON main.ProjectID = size.ProjectID

LEFT JOIN (SELECT
 ProjectID
,ActivityCode
,ActivityDescription
,ActivityResults
FROM Customer
WHERE ActivityCode = 4748) qty ON main.ProjectID = qty.ProjectID

LEFT JOIN (SELECT
 ProjectID
,ActivityCode
,ActivityDescription
,ActivityResults
,ROW_NUMBER() OVER (PARTITION BY ProjectID, CASE WHEN ActivityCode = 15 THEN 1 ELSE 0 END, CASE WHEN ActivityCode = 16 THEN 1 ELSE 0 END ORDER BY DTTM DESC) RN
FROM Customer
) MaxDTTM ON main.ProjectID = MaxDTTM.ProjectID

WHERE RN = 1

ProjectID	CustomerID	InvoiceNo	CategoryColor	Size	Quantity	DTTM
123456	999	11232	Green	Large	7	2024-04-24 18:01:00.000
123456	999	11232	Green	Large	7	2024-04-24 18:01:00.000
123456	999	11232	Green	Large	7	2024-04-25 09:01:00.000
123456	999	11232	Green	Large	7	2024-04-25 09:21:00.000
123456	999	11232	Green	Large	7	2024-04-25 10:05:00.000
123456	999	11232	Green	Large	7	2024-04-24 18:01:00.000
123456	999	11232	Green	Large	7	2024-04-24 18:01:00.000
123456	999	11232	Green	Large	7	2024-04-25 09:01:00.000
123456	999	11232	Green	Large	7	2024-04-25 09:21:00.000
123456	999	11232	Green	Large	7	2024-04-25 10:05:00.000
123456	999	11232	Green	Large	7	2024-04-24 18:01:00.000
123456	999	11232	Green	Large	7	2024-04-24 18:01:00.000
123456	999	11232	Green	Large	7	2024-04-25 09:01:00.000
123456	999	11232	Green	Large	7	2024-04-25 09:21:00.000
123456	999	11232	Green	Large	7	2024-04-25 10:05:00.000
171718	149	12001	Yellow	Small	3	2024-04-20 13:25:00.000
171718	149	12001	Yellow	Small	3	2024-04-20 13:25:00.000
171718	149	12001	Yellow	Small	3	2024-04-22 12:05:00.000
171718	149	12001	Yellow	Small	3	2024-04-22 12:05:00.000
171718	149	12001	Yellow	Small	3	2024-04-22 12:05:00.000
171718	149	12001	Yellow	Small	3	2024-04-20 13:25:00.000
171718	149	12001	Yellow	Small	3	2024-04-20 13:25:00.000
171718	149	12001	Yellow	Small	3	2024-04-22 12:05:00.000
171718	149	12001	Yellow	Small	3	2024-04-22 12:05:00.000
171718	149	12001	Yellow	Small	3	2024-04-22 12:05:00.000
171718	149	12001	Yellow	Small	3	2024-04-20 13:25:00.000
171718	149	12001	Yellow	Small	3	2024-04-20 13:25:00.000
171718	149	12001	Yellow	Small	3	2024-04-22 12:05:00.000
171718	149	12001	Yellow	Small	3	2024-04-22 12:05:00.000
171718	149	12001	Yellow	Small	3	2024-04-22 12:05:00.000
654321	1745	19121	Blue	Medium	10	2024-04-26 11:15:00.000
654321	1745	19121	Blue	Medium	10	2024-04-26 11:15:00.000
654321	1745	19121	Blue	Medium	10	2024-04-26 11:15:00.000
654321	1745	19121	Blue	Medium	10	2024-04-26 11:15:00.000
654321	1745	19121	Blue	Medium	10	2024-04-26 11:15:00.000
654321	1745	19121	Blue	Medium	10	2024-04-26 11:15:00.000
654321	1745	19121	Blue	Medium	10	2024-04-26 11:15:00.000
654321	1745	19121	Blue	Medium	10	2024-04-26 11:15:00.000
654321	1745	19121	Blue	Medium	10	2024-04-26 11:15:00.000
654321	1745	19121	Blue	Medium	10	2024-04-26 11:15:00.000
654321	1745	19121	Blue	Medium	10	2024-04-26 11:15:00.000
654321	1745	19121	Blue	Medium	10	2024-04-26 11:15:00.000
654321	1745	19121	Blue	Medium	10	2024-04-26 11:15:00.000
654321	1745	19121	Blue	Medium	10	2024-04-26 11:15:00.000
654321	1745	19121	Blue	Medium	10	2024-04-26 11:15:00.000

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(45 rows affected)