Search code examples
sql-servert-sql

Generate row for each month year if product sold in that year

I have a question. Based on my dummy data I would like to generate a view which

  1. Shows all the customers even those who haven't sold anything, so this is just one row for example 'V.Z.W. Frderik' and the rest for 'Kevin N.V.'
  2. A combination of all the year/months and the products that were once sold in that particular year by that customer. As an example, product with ID '9000' has been sold in year/months '202303' and '202304' by 'Kevin N.V.' but I still want to see the combination for that product and all the year/months in that year for that customer. Product with ID '9001' has only been sold in the year/months '202403' and '202404' so I only want to see the combination of all year/months in that year where that product is sold by that customer. Product with ID '9002' has been sold in both year so there is a combination of year/months of both years. The sales should be 0 for those particular year/months such as '202301' = 0, '

DIM_CUSTOMER

DECLARE @DIM_CUSTOMERS TABLE([BusinessKey] INT,[Customer] NVARCHAR(255))
 
INSERT INTO @DIM_CUSTOMERS
VALUES 
(10000, 'Kevin N.V.'),
(10001, 'V.Z.W. Frederik'),
(10002, 'Klaas N.V.')
SELECT * FROM @DIM_CUSTOMERS

DIM_PRODUCT

DECLARE @DIM_PRODUCTS TABLE([BusinessKey] INT, [Product] NVARCHAR(255))
 
INSERT INTO @DIM_PRODUCTS
VALUES
(9000, 'PH114'),
(9001, 'PH272'),
(9002, 'PH878')
SELECT * FROM @DIM_PRODUCTS

DIM_DATES

DECLARE @DIM_DATES TABLE([BusinessKey] INT, [Year] INT, [Month] INT, [YearMonth] INT, [YearMonthText] NVARCHAR(20))

INSERT INTO @DIM_DATES
VALUES
(202301, 2023, 1, 202301, '2023.01'),
(202302, 2023, 2, 202302, '2023.02'),
(202303, 2023, 3, 202303, '2023.03'),
(202304, 2023, 4, 202304, '2023.04'),
(202305, 2023, 5, 202305, '2023.05'),
(202306, 2023, 6, 202306, '2023.06'),
(202307, 2023, 7, 202307, '2023.07'),
(202308, 2023, 8, 202308, '2023.08'),
(202309, 2023, 9, 202309, '2023.09'),
(202310, 2023, 10, 202310, '2023.10'),
(202311, 2023, 11, 202311, '2023.11'),
(202312, 2023, 12, 202312, '2023.12'),
(202401, 2024, 1, 202401, '2024.01'),
(202402, 2024, 2, 202402, '2024.02'),
(202403, 2024, 3, 202403, '2024.03'),
(202404, 2024, 4, 202404, '2024.04'),
(202405, 2024, 5, 202405, '2024.05'),
(202406, 2024, 6, 202406, '2024.06'),
(202407, 2024, 7, 202407, '2024.07'),
(202408, 2024, 8, 202408, '2024.08'),
(202409, 2024, 9, 202409, '2024.09'),
(202410, 2024, 10, 202410, '2024.10'),
(202411, 2024, 11, 202411, '2024.11'),
(202412, 2024, 12, 202412, '2024.12')
SELECT * FROM @DIM_DATES

FACT_SALES

DECLARE @FACT_SALES TABLE([ID] INT, [FK_Product] INT, [FK_Customer] INT, [FK_Date] INT, [Sales] FLOAT)

INSERT INTO @FACT_SALES
VALUES
(1, 9000, 10000, 202303, 90.48),
(2, 9000, 10000, 202304, 20.40),
(3, 9002, 10000, 202305, 250.85),
(4, 9002, 10000, 202303, 100.50),
(5, 9000, 10000, 202403, 38.40),
(6, 9000, 10000, 202406, 474.50),
(7, 9001, 10000, 202403, 128.60),
(8, 9001, 10000, 202404, 144.97),
(9, 9000, 10002, 202303, 199.60),
(10, 9001, 10002, 202302, 58.97),
(11, 9001, 10002, 202402, 40.88)
SELECT * FROM @FACT_SALES

This is an example of what I want in my view:

|   Customer    |   Product |   YearMonthText   |   Sales   |
|   -------------------------   |   ---------------------   |   ---------------------   |   ---------------------   |
|   Kevin N.V.  |   PH114   |   202301  |   0   |
|   Kevin N.V.  |   PH114   |   202302  |   0   |
|   Kevin N.V.  |   PH114   |   202303  |   90  |

When I recreate a regular view I only see data that has been sold for that particular year/month. I managed to do this in Power BI with DAX (ALLSELECTED) but I want to do the same in SQL Server for performance purposes. I attempted to create something in T-SQL to start with the customer table or use CROSS JOINS but it didn't manage to get the results

Can somebody help me with this problem?

Thanks!

Solution

  • Check this fiddle out to see this in action. It's not the most elegant solution, but it does work.

    https://dbfiddle.uk/oGHeZwYK

    This uses a couple of CTEs to

    1. CustProdYears Get a unique list of customers, products and years so we can limit the results to just these customers, products, and years. This is to prevent a year from appearing if the customer did not sell a particular product in that year

    2. CustomerSales Get a list of all sales where the customer, product and year appear in CustProdYears above.

    WITH CustProdYears 
    AS(
        SELECT DISTINCT 
            d.[Year] as SaleYear, s.FK_Product, s.FK_Customer
            FROM @FACT_SALES s       
            JOIN @DIM_DATES d on s.FK_Date = d.BusinessKey
    )
, CustomerSales 
    AS (
        SELECT cpy.FK_Customer, cpy.FK_Product, d.YearMonthText, s.Sales
            FROM CustProdYears cpy 
            JOIN @DIM_DATES d on cpy.[SaleYear] = d.[Year]
            LEFT JOIN @FACT_SALES s 
                on s.FK_Customer = cpy.FK_Customer
                and s.FK_Product = cpy.FK_Product
                and s.FK_Date = d.BusinessKey
    )

SELECT 
      Customer, Product, YearMonthText
    , Sales = ISNULL(Sales, 0)
    FROM @DIM_CUSTOMERS c 
        LEFT JOIN CustomerSales s ON c.BusinessKey = s.FK_Customer
        LEFT JOIN @DIM_PRODUCTS p on s.FK_Product = p.BusinessKey
    ORDER BY Customer, Product, YearMonthText

    The final SELECT just starts with customers (so they all appear in the results) and then left joins our CustomerSales CTE to show all related products/years (and sales if present).