Calculate Sales with different start Dates

Question

Calculate Sales with different start Dates

jcobb 20350

Default port

Points: 1438
More actions
February 16, 2024 at 4:54 pm

#4379818
We have a rebate program where I need to calculate sales based on the date the customer joined the program. Ex: Customer A joins the program on October 5th, 2023 and Customer B joins on December 8th, 2023. I'd like results to look at the start date and show their sales by month for a twelve month range. I'd be using "Month 1" as the month they started in the program, so even though the header says Month 1, the user knows it does not pertain to a specific month, only the first month of that customer/date combo.
Using this data then querying the table, I get results by month. Image just below code.
```
CREATE TABLE cust_sales_temp (customer_id varchar(10), program_date date, oct_sales decimal(10,2), nov_sales decimal(10,2), dec_sales decimal(10,2), jan_sales decimal(10,2), feb_sales decimal(10,2))

INSERT INTO cust_sales_temp (customer_id, program_date, oct_sales, nov_sales, dec_sales, jan_sales, feb_sales)

VALUES ('A', '10-5-23', 400, 300, 300, 800, 600), 
('B', '12-8-23', 0, 0, 1000, 1200, 900)

SELECT *
FROM cust_sales_temp
```
What I want to see is this where the Month 1 is the first month the customer joined the program.
I was think a LOOP but I'm not sure how to implement or if that's even the correct approach.

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Your temp table struct is unpleasantly denormalised – perhaps by design, but I suggest you have a rethink about it, because this is very easily done with normalised data, as shown below,

DROP TABLE IF EXISTS #Customer;

CREATE TABLE #Customer
(
    CustomerId VARCHAR(10) PRIMARY KEY CLUSTERED
   ,ProgramDate DATE NOT NULL
);

DROP TABLE IF EXISTS #CustSales;

CREATE TABLE #CustSales
(
    CustomerId VARCHAR(10) NOT NULL
   ,SalesDate DATE NOT NULL
   ,SalesAmt DECIMAL(10, 2) NOT NULL
   ,
   PRIMARY KEY CLUSTERED(
                            CustomerId
                           ,SalesDate
                        )
);

INSERT #Customer
(
    CustomerId
   ,ProgramDate
)
VALUES
('A', '20231005')
,('B', '20231208');

INSERT #CustSales
(
    CustomerId
   ,SalesDate
   ,SalesAmt
)
VALUES
('A', '20231001', 400)
,('A', '20231101', 300)
,('A', '20231201', 300)
,('A', '20240101', 800)
,('A', '20240201', 600)
,('B', '20231201', 1000)
,('B', '20240101', 1200)
,('B', '20240201', 900);

WITH ordered
AS (SELECT cs.CustomerId
          ,cs.SalesAmt
          ,c.ProgramDate
          ,rn = ROW_NUMBER () OVER (PARTITION BY cs.CustomerId ORDER BY cs.SalesDate)
    FROM #CustSales cs
        JOIN #Customer c
            ON c.CustomerId = cs.CustomerId)
SELECT ordered.CustomerId
      ,ordered.ProgramDate
      ,month1 = MAX (IIF(ordered.rn = 1, ordered.SalesAmt, NULL))
      ,month2 = MAX (IIF(ordered.rn = 2, ordered.SalesAmt, NULL))
      ,month3 = MAX (IIF(ordered.rn = 3, ordered.SalesAmt, NULL))
      ,month4 = MAX (IIF(ordered.rn = 4, ordered.SalesAmt, NULL))
      ,month5 = MAX (IIF(ordered.rn = 5, ordered.SalesAmt, NULL))
FROM ordered
GROUP BY ordered.CustomerId
        ,ordered.ProgramDate
ORDER BY ordered.CustomerId;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 2

A quick question, is this the format of the data you must work with or can you correct the data structure? Adding columns for each month is not a sustainable data structure!

😎

Constructing a query that implies an attribute change given the ordinal position of each column is an anti-pattern and does not conform to good practices.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 3

SELECT ca1.*
FROM dbo.cust_sales_temp cst
CROSS APPLY (
    SELECT cst.customer_id, cst.program_date, 
        MAX(CASE WHEN row_num = 1 THEN sales END) AS month1,
        MAX(CASE WHEN row_num = 2 THEN sales END) AS month2,
        MAX(CASE WHEN row_num = 3 THEN sales END) AS month3,
        MAX(CASE WHEN row_num = 4 THEN sales END) AS month4,
        MAX(CASE WHEN row_num = 5 THEN sales END) AS month5,
        MAX(CASE WHEN row_num = 6 THEN sales END) AS month6
    FROM (
        SELECT month#, sales, ROW_NUMBER() OVER(ORDER BY month#) AS row_num
        FROM ( VALUES(1, cst.oct_sales), (2, cst.nov_sales), (3, cst.dec_sales), 
               (4, cst.jan_sales), (5, cst.feb_sales) ) AS sales(month#, sales)
        WHERE sales <> 0
    ) AS sales
) AS ca1

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

jcobb 20350 Default port Points: 1438 More actions · Answer 4

The monthly data would be limited to the first six months of sales based on the date they joined the program. After six months, I no longer need to report for that customer. Using the possible solutions from Phil and Scott, I am going to build out and see where this takes me. It's possible for the customer to have sales prior to joining the rebate program so I need to limit my INSERT to sales after the date they joined the program.

Thank you all for the guidance.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 5

I think my code would adjust as follows to check program_date for inclusion:

SELECT ca1.*
FROM dbo.cust_sales_temp cst
CROSS APPLY ...
FROM (
    SELECT month#, sales, ROW_NUMBER() OVER(ORDER BY month#) AS row_num
    FROM ( VALUES(1, CAST('20231001' AS date), cst.oct_sales), (2, '20231101', cst.nov_sales), (3, '20231201', cst.dec_sales),
(4, '20240101', cst.jan_sales), (5, '20240201', cst.feb_sales) ) AS sales(month#, date, sales)
    WHERE sales <> 0 AND cst.program_date < DATEADD(MONTH, 1, date)
) AS sales
) AS ca1

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 6

As an aside, MySQL had a proposal for extended ISO 8601 date formats. The idea was that the yyyy-mm-dd format which is the only one allowed in ANSI/ISO standard SQL could have '00' for the day or month fields in a DATE. The zeros would serve as shorthand for "01 to {28,29,30,31}" when it appears in a day field and "01 to {01,02, ..12} when it appears in a month field. A whole year would be represented by 'yyyy-00-00' if you need it. The advantages of this proposal are that 1) it is language-independent, 2) it sorts dates correctly into a temporal hierarchy, 3) It looks to be fairly simple to implement an existing temporal model 4) you can write a simple look-up table to implement now.

Please post DDL and follow ANSI/ISO standards when asking for help.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

jcobb 20350 wrote:

We have a rebate program where I need to calculate sales based on the date the customer joined the program. Ex: Customer A joins the program on October 5th, 2023 and Customer B joins on December 8th, 2023. I'd like results to look at the start date and show their sales by month for a twelve month range. I'd be using "Month 1" as the month they started in the program, so even though the header says Month 1, the user knows it does not pertain to a specific month, only the first month of that customer/date combo.

As a bit of a sidebar, if we have Customer "C" that joins on the 31st of January, does that mean that "Month 1" will only be 1 day in duration? If not, please explain what you expect to happen with that customer for the ranges of dates to be included for each numbered month.

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