I have a challenge to limit a data run on last 90 days, but take into account that there may be differing data entry dates for a grouped set of IDs. If I just run the query to limit data to last 90 days, it will not always pull in all the grouped data (of sets of 6). For the most part, the data entry will all be done on same day. However, there are times we don’t have the values, so data is entered next day or up to weeks later. I am trying to solve that if any of the 5 activities has an entry date within 90 days to grab the companion grouped data that may be older than 90 so it shows up in the report.
I have this raw table (note, I have to derive the columns from the activity codes).
| ProjectID | ActivityCode | ActivityDescription | ActivityResults | DTTM |
|---|---|---|---|---|
| 123456 | 15 | CustomerID | 999 | 4/24/24 18:01 |
| 123456 | 16 | InvoiceNo | 11232 | 4/24/24 18:01 |
| 123456 | 1745 | CategoryColor | Green | 4/25/24 9:01 |
| 123456 | 2256 | Size | Large | 4/25/24 9:21 |
| 123456 | 4748 | Quantity | 7 | 4/25/24 10:05 |
| 654321 | 15 | CustomerID | 1745 | 4/26/24 11:15 |
| 654321 | 16 | InvoiceNo | 19121 | 4/26/24 11:15 |
| 654321 | 1745 | CategoryColor | Blue | 4/26/24 11:15 |
| 654321 | 2256 | Size | Medium | 4/26/24 11:15 |
| 654321 | 4748 | Quantity | 10 | 4/26/24 11:15 |
| 171718 | 15 | CustomerID | 149 | 4/20/24 13:25 |
| 171718 | 16 | InvoiceNo | 12001 | 4/20/24 13:25 |
| 171718 | 1745 | CategoryColor | Yellow | 4/22/24 12:05 |
| 171718 | 2256 | Size | Small | 4/22/24 12:05 |
| 171718 | 4748 | Quantity | 3 | 4/22/24 12:05 |
The results I am trying to get is this:
| ProjectID | CustomerID | InvoiceNo | CategoryColor | Size | Quantity | DTTM |
|---|---|---|---|---|---|---|
| 123456 | 999 | 11232 | Green | Large | 7 | 4/25/24 10:05 |
| 654321 | 1745 | 19121 | Blue | Medium | 10 | 4/26/24 11:15 |
| 171718 | 149 | 12001 | Yellow | Small | 3 | 4/22/24 12:05 |
Here is the fiddle code: https://sqlfiddle.com/sql-server/online-compiler?id=7bcda154-e51a-4ad7-85ef-5f4149af8b73. Just in case the fiddle link doesn’t work, here is my code:
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
Thank you for any suggestions