Chartjs-Sql Server query that returns prices from each shop on each date and adds a 0 if no data is present for the shopId on a particular date

by

1👍

✅

Ideally what you should be doing here is creating a Calendar Table. I’m not going to cover how you create a calendar table here, as a search in your favourite search engine of something like "Calendar Table SQL Server" will give you a huge wealth of resources and give some great explanations and their use cases.

Once you have a Calendar table, you need to CROSS JOIN that to your Shops table; which I also assume you have. Then you can simply LEFT JOIN to your Prices table.

So a parametrised query might look like this:

SELECT S.ShopID,
       COUNT(P.Price) AS Prices,
       C.CalendarDate AS Datefound
FROM dbo.Calendar C
     CROSS JOIN dbo.Shops S
     LEFT JOIN dbo.Prices P ON C.CalendarDate = P.DateFound --Though DateFound is a datetime, I assume it's time portion is 00:00:00.000
                           AND S.ShopId = P.ShopID
WHERE C.CalendarDate >= @StartDate
  AND C.CalendarDate < DATEADD(DAY, 1, @EndDate)
GROUP BY S.ShopID,
         C.CalendarDate;

If you can’t, for some reason, create a Calendar table and you don’t have (and can’t create) a Shop table (I strongly suggest you do create one though) then you’ll need to use an inline tally to create your Calendar, and DISTINCT to get the Shop IDs.

A parametrised query would look something like this:

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT 0 AS I
    UNION ALL
    SELECT TOP(DATEDIFF(DAY, @StartDate, @EndDate))
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2, N N3), --1,000 rows. Add more cross joins for more rows
Calendar AS(
    SELECT DATEADD(DAY, T.I, @StartDate) AS CalendarDate
    FROM Tally T),
Shops AS(
    SELECT DISTINCT P.ShopID
    FROM dbo.Prices P)
SELECT S.ShopID,
       COUNT(P.Price) AS Prices,
       C.CalendarDate AS Datefound
FROM dbo.Calendar C
     CROSS JOIN dbo.Shops S
     LEFT JOIN dbo.Prices P ON C.CalendarDate = P.DateFound --Though DateFound is a datetime, I assume it's time portion is 00:00:00.000
                           AND S.ShopId = P.ShopID
GROUP BY S.ShopID,
         C.CalendarDate;

db<>fiddle

Leave a comment