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.

    Please help with it, let me know if it need more info to explain.

    Thank you in advance

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


    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.


    Helpful Links:
    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 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);
  • 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;
    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

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


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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply