October 1, 2022 at 4:52 pm
Good Morning There,
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
October 1, 2022 at 10:09 pm
No comment on the date calculations?
declare @start_yr int=2022;
with months_cte(mo_num) as (
select * from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) v(mo_num))
select m.*, v.calc_mo, cust.mo_count cust_mo_count, ord.mo_count ord_mo_count,
lag(cust.mo_count, 1, 0) over (order by m.mo_num) lag_cust_mo_count,
lag(ord.mo_count, 1, 0) over (order by m.mo_num) lag_ord_mo_count
from months_cte m
cross apply (values (dateadd(month, m.mo_num-1, datefromparts(@start_yr, 1, 1)))) v(calc_mo)
outer apply (select count(*)
from #Customers c
where c.CustomerJoinDate<=eomonth(v.calc_mo)) cust(mo_count)
outer apply (select count(distinct co.CID)
from #Customers_Orders co
where co.ORDER_INDICATOR=1
and co.Day_Date>=v.calc_mo
and co.Day_Date<dateadd(day, 1, eomonth(v.calc_mo))) ord(mo_count);
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
October 1, 2022 at 10:27 pm
It would be nice if the example data script actually worked. 😉 Here are the results then I try to load the data for the Customer_Orders.
Msg 2627, Level 14, State 1, Line 29
Violation of PRIMARY KEY constraint 'PK__Customer__C1F8DC59364ADE6E'. Cannot insert duplicate key in object 'dbo.Customers_Orders'. The duplicate key value is (100).
The statement has been terminated.
Msg 2627, Level 14, State 1, Line 30
Violation of PRIMARY KEY constraint 'PK__Customer__C1F8DC59364ADE6E'. Cannot insert duplicate key in object 'dbo.Customers_Orders'. The duplicate key value is (100).
The statement has been terminated.
Msg 2627, Level 14, State 1, Line 31
Violation of PRIMARY KEY constraint 'PK__Customer__C1F8DC59364ADE6E'. Cannot insert duplicate key in object 'dbo.Customers_Orders'. The duplicate key value is (100).
The statement has been terminated.
Msg 2627, Level 14, State 1, Line 32
Violation of PRIMARY KEY constraint 'PK__Customer__C1F8DC59364ADE6E'. Cannot insert duplicate key in object 'dbo.Customers_Orders'. The duplicate key value is (100).
The statement has been terminated.
Msg 2627, Level 14, State 1, Line 33
Violation of PRIMARY KEY constraint 'PK__Customer__C1F8DC59364ADE6E'. Cannot insert duplicate key in object 'dbo.Customers_Orders'. The duplicate key value is (100).
The statement has been terminated.
(1 row affected)
Msg 2627, Level 14, State 1, Line 36
Violation of PRIMARY KEY constraint 'PK__Customer__C1F8DC59364ADE6E'. Cannot insert duplicate key in object 'dbo.Customers_Orders'. The duplicate key value is (102).
The statement has been terminated.
Msg 2627, Level 14, State 1, Line 37
Violation of PRIMARY KEY constraint 'PK__Customer__C1F8DC59364ADE6E'. Cannot insert duplicate key in object 'dbo.Customers_Orders'. The duplicate key value is (102).
The statement has been terminated.
Msg 2627, Level 14, State 1, Line 38
Violation of PRIMARY KEY constraint 'PK__Customer__C1F8DC59364ADE6E'. Cannot insert duplicate key in object 'dbo.Customers_Orders'. The duplicate key value is (102).
The statement has been terminated.
Msg 2627, Level 14, State 1, Line 39
Violation of PRIMARY KEY constraint 'PK__Customer__C1F8DC59364ADE6E'. Cannot insert duplicate key in object 'dbo.Customers_Orders'. The duplicate key value is (102).
The statement has been terminated.
(1 row affected)
It's an easy fix but I'll let you do that. I'll try again when you post to say it's been fixed.
In the future, please try such scripts before you post them. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 1, 2022 at 11:51 pm
Hello Zeff Afternoon,
Apologize, here is the code i executed and tested.
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,
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);
October 2, 2022 at 12:00 am
Thank you Steve,
The date calculation is, only consider the customers after accountJoinDate Month (in Customers) table. for example a new customer #107 joined in May 2022, and had first order in August 2022 then they start count during and after May 2022.
for this customer #107 falls jan to may dont consider, May this account (107) falls under never placed, Jun never placed, july never placed, aug placed order, sep historically placed order and so on...
Please let me know if this explains or if you wish me to provide more info on it.
Thank you again
ASiit
October 2, 2022 at 1:00 pm
In Visual Studio if you hold the Alt key down while using the mouse to highlight text it rectangularly selects areas. Because the dates are horizontally aligned with just a few clicks they were standardized. Took like 10 seconds. Otherwise, if still saddled with SSMS (no rectangles, no dark mode) I wouldn't have bothered. VS can't trace dependencies tho which means SSMS is still always open on my desktop. Here's the whole script. What's being described might not be what's intended and vice versa 🙂 Maybe something like this
drop TABLE if exists #Customers;
go
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);
drop TABLE if exists #Customers_Orders;
go
CREATE TABLE #Customers_Orders (
--CID int --NOT NULL PRIMARY KEY,
CID int references #Customer(CID) NOT NULL,
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,'2022-01-16'),
(101, 'ABC123', 'King',40,1,'2022-01-19'),
(102, 'XYZ456', 'SARA',55,1,'2022-02-21'),
(104, 'XYZ123', 'Jimi',45,0,'2022-03-26'),
(107, 'XYZ123', 'Jimi',45,0,'2022-05-26');
--- Orders Data
INSERT INTO #Customers_Orders Values
(100,'2022-04-19',1,1,125,30),
(100,'2022-05-21',1,0,125,30),
(100,'2022-06-25',0,1,0,0),
(100,'2022-07-11',1,0,125,30),
(100,'2022-08-17',1,0,125,30),
(102,'2022-05-19',1,1,50,30),
(102,'2022-05-21',1,0,15,30),
(102,'2022-05-25',1,1,30,20),
(102,'2022-08-11',1,0,50,30),
(102,'2022-08-17',1,0,50,30),
(101,'2022-03-17',1,1,50,30),
(107,'2022-05-27',1,1,50,30),
(107,'2022-06-18',1,1,50,30);
declare @start_yr int=2022;
with months_cte(mo_num) as (
select * from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) v(mo_num))
select m.*, v.calc_mo, cust.mo_count cust_mo_count, ord.mo_count ord_mo_count,
lag(ord.mo_count, 1, 0) over (order by m.mo_num) lag_ord_mo_count
from months_cte m
cross apply (values (dateadd(month, m.mo_num-1, datefromparts(@start_yr, 1, 1)))) v(calc_mo)
cross join (select count(*)
from #Customers c) cust(mo_count)
outer apply (select count(distinct co.CID)
from #Customers c
join #Customers_Orders co on c.CID=co.CID
where datediff(month, c.CustomerJoinDate, co.Day_Date) >= 1
and co.ORDER_INDICATOR=1
and co.Day_Date>=v.calc_mo
and co.Day_Date<dateadd(day, 1, eomonth(v.calc_mo))) ord(mo_count);
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
October 2, 2022 at 8:23 pm
Hello Zeff Afternoon,
BWAAA-HAAA-HAAA!!! Four letters in my name and you won't even take the time to get that right. 🙁 Sorry... moving on.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply