SQL data distribution to calendar table by months

Question

I am using Microsoft SQL Server. Could you help me please with correct joins to this data?

create table #test 
(
    calYear int, 
    calMonth int,
    Qty int, 
    Part nvarchar(10)
)

Insert #test 
values (2024, 01, 1, 'part1'),
       (2023, 02, 4, 'part1'),
       (2023, 04, 8, 'part1'),
       (2023, 05, 14, 'part1')

CREATE TABLE #Calendar
(
    CalendarYear int,
    [CalendarMonth] int
)

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @EndDate = DATEADD(m,-1,GETDATE())
SET @StartDate = DATEADD(m, -11, @EndDate)

WHILE @StartDate <= @EndDate
BEGIN
    INSERT INTO #Calendar (CalendarYear, CalendarMonth)
        SELECT
            YEAR(@StartDate),
            MONTH(@StartDate)

    SET @StartDate = DATEADD(m, 1, @StartDate)
END

SELECT
    CASE 
        WHEN t.CalYear = c.CalendarYear 
             AND t.CalMonth = c.CalendarMonth 
            THEN Qty 
            ELSE 0 
    END, *
FROM  
    #Calendar c
CROSS JOIN
    #test t

DROP TABLE #Calendar
DROP TABLE #test

I have calendar table with only month and years and have data table that I need to cross join, so data table have ALL of those months in the period, but with that Qty from data table distribute itself through these months. The thing I managed is cross joining every line of data and putting data in the correct month, but I need so it looked like this:

https://i.sstatic.net/xrpRg.png

To one part there would be only one set of calendar range...

I tried some outer apply stuff but no luck, Thank you!

Answer 1

Firstly, to get the the names of "part2" into the output you will need that to exist in the #test table.

Insert #test 
values (2024, 01, 1, 'part1'),
       (2023, 02, 4, 'part1'),
       (2023, 04, 8, 'part1'),
       (2023, 05, 14, 'part1'),
       (1998, 12, 0, 'part2')

Cross joins get every combination of rows between two tables. This isn't really what you need to do. You want every combination of calendar date and part name (which will be a cross join) but only the relevant quantities for each of those combinations, without losing data where there is no quantity. So rather than cross join you need a left join

Change the query to do a cross join onto a distinct list of part names, then left join onto the table with quantites which on the Year, Month and Part Name.

SELECT  
    c.CalendarYear
    ,c.CalendarMonth
    ,t.Qty
    ,UniqueParts.Part
FROM    
    #Calendar c

CROSS JOIN (
    SELECT DISTINCT 
        Part
    FROM
        #test
    ) UniqueParts

LEFT JOIN 
    #test t
ON c.CalendarYear = t.calYear
    AND c.CalendarMonth = t.calMonth
    AND t.Part = UniqueParts.Part