Good Morning,
CREATE TABLE Customers (
CID int NOT NULL PRIMARY KEY,
GroupName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CustomerStatus SMALLINT, ---1 Means Active, 0 Inactive
CustomerJoinDate Date
)
CREATE TABLE Customers_Orders (
CID int NOT NULL PRIMARY KEY,
Day_Date Date,
ORDER_INDICATOR SMALLINT, --- 1 means made order 0 means no order
Account_CashDeposit SMALLINT, --- 1 means yes 0 means no
OrderCost int ,
Qty int
)
---Customers
INSERT INTO Customers Values(100, 'ABC123', 'John',30,1,'01/16/2022')
INSERT INTO Customers Values(101, 'ABC123', 'King',40,1,'01/19/2022')
INSERT INTO Customers Values(102, 'XYZ456', 'SARA',55,1,'02/21/2022')
INSERT INTO Customers Values(104, 'XYZ123', 'Jimi',45,0,'03/26/2022')
--- Orders Data
INSERT INTO Customers_Orders Values(100,'04/19/2022',1,1,125,30)
INSERT INTO Customers_Orders Values(100,'05/21/2022',1,0,125,30)
INSERT INTO Customers_Orders Values(100,'06/25/2022',0,1,0,0)
INSERT INTO Customers_Orders Values(100,'07/11/2022',1,0,125,30)
INSERT INTO Customers_Orders Values(100,'08/17/2022',1,0,125,30)
INSERT INTO Customers_Orders Values(102,'05/19/2022',1,1,50,30)
INSERT INTO Customers_Orders Values(102,'05/21/2022',1,0,15,30)
INSERT INTO Customers_Orders Values(102,'05/25/2022',1,1,30,20)
INSERT INTO Customers_Orders Values(102,'08/11/2022',1,0,50,30)
INSERT INTO Customers_Orders Values(102,'08/17/2022',1,0,50,30)
INSERT INTO Customers_Orders Values(101,'03/17/2022',1,1,50,30)
Expected outcome Month-YYYY, Historically Order placed counts, Never Place Order counts, Orders Placed Counts
example customer# 100 joined Jan 16th first order made April 19,
so for Jan counts, this customer falls "Never Placed Order",
feb also "Never Placed Order",
march also "Never Placed Order",
but for the april counts, this account falls as "orders placed"
for May this account falls as "orders placed"
for jun ORDER_INDICATOR= 0 it means this account falls as part of "historically order placed" counts.
then july it falls as "orders placed"
Then Aug it falls as "orders placed"
sep it is historically order placed`.
this helps
expected outcome
Month NeverOrdered, HistoricallyOrdered, Orders PLaced during month
Jan-2022 4,0,0
Feb-2022 4,0,0
Mar-2022 3,0,1
APR-2022 2,1,1
May-2022 1,1,2
Jun-2022 1,3,0
Jul-2022 1,3,0
Aug-2022 1,1,2
Sep-2022 1,3,0
explanation on output.
Jan 2022, all 4 accounts dont made any orders placed also no historical as we start analysis from Jan 1st onwards orders placed.
Feb all 4 falls never placed order`
March 101 placed order (month(day_date) and ORDER_INDICATOR-1) so 3 falls never placed order, 1 fall order placed duirng month.
April, 2 never placed, 101 is already made order in March but not in april so historically placed, cust#100 placed order in april
May -2022 never placed only 1 (cust#104), historically order placed only 1 (cust#101),order placed 2 during month 100 & 102 (based on date, ORDER_INDICATOR=1)
Jun-2022 no current month order any, there is one entry for cust#no 100 but there is ORDER_INDICATOR=0 so no need to consider.
Please help with it, let me know if it need more info to explain.
Thank you in advance
Asiti