July 26, 2022 at 6:17 pm
Good Morning
Have two tables Customers (Customer_ID
CREATE TABLE UHC_customers
( customer_id int NOT null, --Primary key unique values
AgeFallGroup varchar(6),
InsuranceStartDate Date,
Region varchar(20),
CustStatus Varchar(10)
);
INSERT INTO UHC_customers VALUES (1001, '20-29', '03/25/2022','North America','Active')
INSERT INTO UHC_customers VALUES (1002, '40-49', '02/18/2022','Asia','Active');
INSERT INTO UHC_customers VALUES (1003, '20-29', '01/01/2022','Asia','Active');
INSERT INTO UHC_customers VALUES (1004, '50-59', '05/21/2022','North America','Active');
INSERT INTO UHC_customers VALUES (1005, '70-79', '04/27/2022','North America','Active');
INSERT INTO UHC_customers VALUES (1006, '60-69', '05/15/2022','Europe','InActive');
INSERT INTO UHC_customers VALUES (1007, '20-29', '06/13/2022','North America','Active');
INSERT INTO UHC_customers VALUES (1008, '20-29', '07/19/2022','Australia','Active');
INSERT INTO UHC_customers VALUES (1009, '30-39', '11/14/2021','Australia','Active');
INSERT INTO UHC_customers VALUES (1010, '40-49', '12/26/2021','Australia','Active');
CREATE TABLE OrderTrans
( customer_id int ,
OrderDate Date,
OrderIndicator varchar(1), -- Y or N
DepositIndicator varchar(1), -- Y or N
CheckinIndicator varchar(1) -- Y or N
TitalOrders Int
OrderQty Decimal(20,8)
);
INSERT INTO OrderTrans VALUES (1001, '03/27/2022', 'N', 'N', 'Y',NULL,NULL);
INSERT INTO OrderTrans VALUES (1001, '04/10/2022', 'N', 'Y', 'N',NULL,NULL);
INSERT INTO OrderTrans VALUES (1001, '04/12/2022', 'N', 'N', 'N',NULL,NULL);
INSERT INTO OrderTrans VALUES (1001, '04/14/2022', 'Y', 'N', 'N',7,100.25);
INSERT INTO OrderTrans VALUES (1001, '04/18/2022', 'Y', 'Y', 'N',5,27.25);
INSERT INTO OrderTrans VALUES (1001, '05/02/2022', 'Y', 'N', 'Y',100,10);
INSERT INTO OrderTrans VALUES (1001, '06/02/2022', 'Y', 'N', 'N',2,38);
INSERT INTO OrderTrans VALUES (1001, '06/03/2022', 'Y', 'Y', 'N',1,148);
INSERT INTO OrderTrans VALUES (1001, '06/03/2022', 'N', 'N', 'Y',NULL,NULL);
INSERT INTO OrderTrans VALUES (1001, '06/03/2022', 'Y', 'Y', 'N',117,4000);
INSERT INTO OrderTrans VALUES (1002, '03/10/2022', 'N', 'N', 'N',NULL,NULL);
INSERT INTO OrderTrans VALUES (1002, '04/11/2022', 'N', 'Y', 'Y',NULL,NULL);
INSERT INTO OrderTrans VALUES (1002, '04/12/2022', 'Y', 'N', 'N',1,1558);
INSERT INTO OrderTrans VALUES (1002, '04/13/2022', 'Y', 'N', 'N',5,134);
INSERT INTO OrderTrans VALUES (1002, '04/14/2022', 'Y', 'Y', 'N',77,18.854);
INSERT INTO OrderTrans VALUES (1002, '04/15/2022', 'Y', 'N', 'N',10,18);
INSERT INTO OrderTrans VALUES (1002, '06/03/2022', 'Y', 'N', 'Y',1,14);
INSERT INTO OrderTrans VALUES (1003, '01/01/2022', 'Y', 'N', 'Y',1,148);
INSERT INTO OrderTrans VALUES (1003, '01/02/2022', 'Y', 'Y', 'N',5,28);
INSERT INTO OrderTrans VALUES (1003, '01/02/2022', 'Y', 'Y', 'N',10,33);
INSERT INTO OrderTrans VALUES (1003, '01/02/2022', 'Y', 'N', 'N',130,55);
INSERT INTO OrderTrans VALUES (1003, '01/03/2022', 'N', 'Y', 'N',NULL,NULL);
INSERT INTO OrderTrans VALUES (1003, '01/06/2022', 'N', 'Y', 'N',NULL,NULL);
INSERT INTO OrderTrans VALUES (1003, '01/07/2022', 'Y', 'N', 'Y',2,200);
INSERT INTO OrderTrans VALUES (1005, '04/29/2022', 'Y', 'N', 'N',10,550.34);
INSERT INTO OrderTrans VALUES (1005, '05/10/2022', 'Y', 'Y', 'N',30,355);
INSERT INTO OrderTrans VALUES (1005, '05/12/2022', 'Y', 'N', 'N',20,755);
INSERT INTO OrderTrans VALUES (1005, '05/14/2022', 'Y', 'N', 'N',54,505);
INSERT INTO OrderTrans VALUES (1005, '05/18/2022', 'Y', 'Y', 'N',5,1055);
INSERT INTO OrderTrans VALUES (1005, '06/02/2022', 'Y', 'N', 'Y',7,1255);
INSERT INTO OrderTrans VALUES (1005, '07/02/2022', 'Y', 'N', 'N',80,3455.54);
INSERT INTO OrderTrans VALUES (1005, '07/03/2022', 'Y', 'Y', 'N',1,155);
INSERT INTO OrderTrans VALUES (1005, '07/03/2022', 'Y', 'N', 'N',3,2355);
INSERT INTO OrderTrans VALUES (1005, '07/03/2022', 'Y', 'Y', 'N',100,3355.45);
I am trying to get the data as
CustomerID, InsuranceStartDate, Region, AgeFallGroup, DaysfromAccountOpen_AND_OrderDate, TotalOrders_Count, OrderQty_Sum
1001,03/25/2022,'North America','20-29', 2 ,
SELECT pk.customer_id , fk.AgeFallGroup , fk.InsuranceStartDate, Datediff(day,InsuranceStartDate,OrderDate) as DaysToplaceOrder, SUM(OrderQty)
from UHC_customers PK
LEFT JOIN OrderTrans FK on PK.customer_id = FK.customer_id
GROUP BY 1,2,3,4
SELECT pk.customer_id , fk.AgeFallGroup , fk.InsuranceStartDate, Datediff(day,InsuranceStartDate,OrderDate) as DaysToplaceOrder, SUM(OrderQty)
from UHC_customers PK
LEFT JOIN OrderTrans FK on PK.customer_id = FK.customer_id
GROUP BY 1,2,3,4
but here I am trying to see Datediff(day,InsuranceStartDate,OrderDate) as column names and column values are sum(orderqty) for that diff date
please see attached output expected.
Thank you
Asita
July 26, 2022 at 7:58 pm
I think it using Pivot, with dynamic. Thanks
July 29, 2022 at 3:58 pm
I do this kind of thing all the time using a crosstab. There is a range of date differences so I put it into buckets. This is merely a jumping off point for you to point you in the right direction. Tailor to your needs:
SELECT c.customer_id, c.AgeFallGroup, c.InsuranceStartDate,
[0-5] = SUM(CASE WHEN DATEDIFF(D, c.InsuranceStartDate, o.OrderDate) BETWEEN 0 AND 5 THEN 1 ELSE 0 END),
[6-10] = SUM(CASE WHEN DATEDIFF(D, c.InsuranceStartDate, o.OrderDate) BETWEEN 6 AND 10 THEN 1 ELSE 0 END),
[11-15] = SUM(CASE WHEN DATEDIFF(D, c.InsuranceStartDate, o.OrderDate) BETWEEN 11 AND 15 THEN 1 ELSE 0 END),
[16-20] = SUM(CASE WHEN DATEDIFF(D, c.InsuranceStartDate, o.OrderDate) BETWEEN 16 AND 20 THEN 1 ELSE 0 END),
[21-50] = SUM(CASE WHEN DATEDIFF(D, c.InsuranceStartDate, o.OrderDate) BETWEEN 21 AND 50 THEN 1 ELSE 0 END),
[>50] = SUM(CASE WHEN DATEDIFF(D, c.InsuranceStartDate, o.OrderDate) > 50 THEN 1 ELSE 0 END)
FROM UHC_customers c
INNER JOIN dbo.OrderTrans o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.AgeFallGroup, c.InsuranceStartDate
ORDER BY c.customer_id
Output attached.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply