2

I have these two tables

DECLARE @Site TABLE (
  SiteCode varchar(4) NOT NULL
);

INSERT INTO @Site (SiteCode)
VALUES 
('S1'),
('S2'),
('S3'),
('S4')

DECLARE @SiteStatus TABLE (
  SiteCode varchar(4) NOT NULL,
  Year int NOT NULL,
  Month varchar(10) NOT NULL,
  UploadCount int NOT NULL
);

INSERT INTO @SiteStatus (SiteCode, Year, Month, UploadCount)
VALUES 
('S1', 2024, 'March', 1),
('S1', 2024, 'April', 1),
('S1', 2024, 'May', 1),
('S1', 2024, 'June', 1),
('S2', 2024, 'March', 1),
('S2', 2024, 'April', 1),
('S2', 2024, 'May', 0),
('S2', 2024, 'June', 0),
('S3', 2024, 'March', 1),
('S3', 2024, 'April', 0)

And I want to write a query that will return the following

SELECT ... ?

'S1', 2024, 'March', 1
'S1', 2024, 'April', 1
'S1', 2024, 'May', 1
'S1', 2024, 'June', 1

'S2', 2024, 'March', 1
'S2', 2024, 'April', 1
'S2', 2024, 'May', 0
'S2', 2024, 'June', 0

'S3', 2024, 'March', 1
'S3', 2024, 'April', 0
'S3', 2024, 'May', 0
'S3', 2024, 'June', 0

'S4', 2024, 'March', 0
'S4', 2024, 'April', 0
'S4', 2024, 'May', 0
'S4', 2024, 'June', 0

Basically, add zero records for all site-date combos missing from SiteStatus.

Thanks!

1
  • Do you have a list/table of months you want to query for? Apr 2 at 1:45

2 Answers 2

4

You can use CTEs to first get all Distinct year/month combinations, then cross join that with all sites and the result can be used to LEFT JOIN to site_status.

But as a side note, it would be better to save dates as dates and then extract the year and month, that saves space and it would help to sort correctly.

 WITH y_m AS ( 
SELECT DISTINCT Year, Month FROM @SiteStatus 
 )
,  C_j AS ( SELECT * FROM @Site  CROSS JOIN y_m )
SELECT c.SiteCode, c.year, c.month, COALESCE(sis.UploadCount,0)
 FROM C_j c  LEFT JOIN @SiteStatus AS sis 
  On c.SiteCode = sis.SiteCode AND c.year = sis.year AND c.month = sis.month
ORDEr BY c.SiteCode, c.year, c.month
SiteCode year month (No column name)
S1 2024 April 1
S1 2024 June 1
S1 2024 March 1
S1 2024 May 1
S2 2024 April 1
S2 2024 June 0
S2 2024 March 1
S2 2024 May 0
S3 2024 April 0
S3 2024 June 0
S3 2024 March 1
S3 2024 May 0
S4 2024 April 0
S4 2024 June 0
S4 2024 March 0
S4 2024 May 0

fiddle

0
2
WITH SiteMonths
AS
(
    SELECT DISTINCT S.[SiteCode], YM.[Year], YM.[Month]
    FROM @Site S
        CROSS JOIN @SiteStatus YM
)
SELECT M.[SiteCode], M.[Year], M.[Month], COALESCE(S.UploadCount, 0) AS UploadCount
FROM SiteMonths M
    LEFT JOIN @SiteStatus S
        ON M.[SiteCode] = S.[SiteCode]
            AND M.[Year] = S.[Year]
            AND M.[Month] = S.[Month]
ORDER BY SiteCode, [Year], DATEPART(month, CONCAT(M.[Month], '01 1900'));

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.