# How to calculate orders historically, during month

• 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.

Asiti

• 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

• 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 29Violation 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 30Violation 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 31Violation 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 32Violation 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 33Violation 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 36Violation 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 37Violation 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 38Violation 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 39Violation 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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• 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 InactiveCustomerJoinDate  Date ) CREATE TABLE Customers_Orders (    CID int NOT NULL,Day_Date Date,ORDER_INDICATOR SMALLINT, --- 1 means made order 0 means no orderAccount_CashDeposit SMALLINT, --- 1 means yes 0 means no    OrderCost int,Qty int ) ---CustomersINSERT 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 DataINSERT 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);`
• 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

• 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;goCREATE TABLE #Customers (CID int NOT NULL PRIMARY KEY,GroupName varchar(255) NOT NULL,FirstName varchar(255),Age int,CustomerStatus SMALLINT, ---1 Means Active, 0 InactiveCustomerJoinDate Date);drop TABLE if exists #Customers_Orders;goCREATE 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 orderAccount_CashDeposit SMALLINT, --- 1 means yes 0 means noOrderCost int ,Qty int);---CustomersINSERT 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 DataINSERT 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

• Zond Sita wrote:

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.