SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE [dbo].[Prices](
	[ProductPricesId] [int],
	[ProductId] [int] NULL,
	[shopid] [int] NULL,
	[price] [decimal](18, 2) NULL,
	[dateFound] [datetime] NULL,
	[scrapeUrl] [varchar](255) NULL) ON [PRIMARY]
CREATE TABLE [dbo].[Calendar](
	[CalendarDate] [datetime] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Shops](
	[ShopId] [int] NULL,
	[ShopName] [varchar](255) NULL
) ON [PRIMARY]
INSERT INTO Shops (ShopId, ShopName)
VALUES (1, 'Amazon'), --VALUES supports multiple rows. It is FAR slower to insert each row individually.
       (7, 'Google'),
       (8, 'Facebook');
3 rows affected

INSERT INTO Calendar (CalendarDate)
VALUES ('2022-01-01T00:00:00.000'), --VALUES supports multiple rows. It is FAR slower to insert each row individually.
       ('2022-01-02T00:00:00.000'),
       ('2022-01-03T00:00:00.000'),
       ('2022-01-04T00:00:00.000'),
       ('2022-01-05T00:00:00.000'),
       ('2022-01-06T00:00:00.000'),
       ('2022-01-07T00:00:00.000'),
       ('2022-01-08T00:00:00.000'),
       ('2022-01-09T00:00:00.000'),
       ('2022-01-10T00:00:00.000'),
       ('2022-01-11T00:00:00.000'),
       ('2022-01-12T00:00:00.000'),
       ('2022-01-13T00:00:00.000'),
       ('2022-01-14T00:00:00.000');
14 rows affected

INSERT INTO Prices (shopid, datefound, price)
VALUES (1,'2022-01-03T00:00:00.000', '69.95'),--VALUES supports multiple rows. It is FAR slower to insert each row individually.
       (7,'2022-01-03T00:00:00.000', '79.95'),
       (8,'2022-01-03T00:00:00.000', '89.95'),
       (7,'2022-01-04T00:00:00.000', '69.95'),
       (8,'2022-01-04T00:00:00.000', '79.95'),
       (1,'2022-01-05T00:00:00.000', '69.95'),
       (7,'2022-01-05T00:00:00.000', '99.95'),
       (8,'2022-01-05T00:00:00.000', '109.95');
8 rows affected

DECLARE @StartDate datetime = '2022-01-01T00:00:00';
DECLARE @EndDate datetime =DATEADD(DAY, 365, @StartDate);

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;


ShopID
Prices
Datefound


1
0
2022-01-01 00:00:00.000

7
0
2022-01-01 00:00:00.000

8
0
2022-01-01 00:00:00.000

1
0
2022-01-02 00:00:00.000

7
0
2022-01-02 00:00:00.000

8
0
2022-01-02 00:00:00.000

1
1
2022-01-03 00:00:00.000

7
1
2022-01-03 00:00:00.000

8
1
2022-01-03 00:00:00.000

1
0
2022-01-04 00:00:00.000

7
1
2022-01-04 00:00:00.000

8
1
2022-01-04 00:00:00.000

1
1
2022-01-05 00:00:00.000

7
1
2022-01-05 00:00:00.000

8
1
2022-01-05 00:00:00.000

1
0
2022-01-06 00:00:00.000

7
0
2022-01-06 00:00:00.000

8
0
2022-01-06 00:00:00.000

1
0
2022-01-07 00:00:00.000

7
0
2022-01-07 00:00:00.000

8
0
2022-01-07 00:00:00.000

1
0
2022-01-08 00:00:00.000

7
0
2022-01-08 00:00:00.000

8
0
2022-01-08 00:00:00.000

1
0
2022-01-09 00:00:00.000

7
0
2022-01-09 00:00:00.000

8
0
2022-01-09 00:00:00.000

1
0
2022-01-10 00:00:00.000

7
0
2022-01-10 00:00:00.000

8
0
2022-01-10 00:00:00.000

1
0
2022-01-11 00:00:00.000

7
0
2022-01-11 00:00:00.000

8
0
2022-01-11 00:00:00.000

1
0
2022-01-12 00:00:00.000

7
0
2022-01-12 00:00:00.000

8
0
2022-01-12 00:00:00.000

1
0
2022-01-13 00:00:00.000

7
0
2022-01-13 00:00:00.000

8
0
2022-01-13 00:00:00.000

1
0
2022-01-14 00:00:00.000

7
0
2022-01-14 00:00:00.000

8
0
2022-01-14 00:00:00.000


Warning: Null value is eliminated by an aggregate or other SET operation.


DECLARE @StartDate datetime = '2022-01-01T00:00:00';
DECLARE @EndDate datetime = DATEADD(DAY, 365, @StartDate);

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;


ShopID
Prices
Datefound


1
0
2022-01-01 00:00:00.000

7
0
2022-01-01 00:00:00.000

8
0
2022-01-01 00:00:00.000

1
0
2022-01-02 00:00:00.000

7
0
2022-01-02 00:00:00.000

8
0
2022-01-02 00:00:00.000

1
1
2022-01-03 00:00:00.000

7
1
2022-01-03 00:00:00.000

8
1
2022-01-03 00:00:00.000

1
0
2022-01-04 00:00:00.000

7
1
2022-01-04 00:00:00.000

8
1
2022-01-04 00:00:00.000

1
1
2022-01-05 00:00:00.000

7
1
2022-01-05 00:00:00.000

8
1
2022-01-05 00:00:00.000

1
0
2022-01-06 00:00:00.000

7
0
2022-01-06 00:00:00.000

8
0
2022-01-06 00:00:00.000

1
0
2022-01-07 00:00:00.000

7
0
2022-01-07 00:00:00.000

8
0
2022-01-07 00:00:00.000

1
0
2022-01-08 00:00:00.000

7
0
2022-01-08 00:00:00.000

8
0
2022-01-08 00:00:00.000

1
0
2022-01-09 00:00:00.000

7
0
2022-01-09 00:00:00.000

8
0
2022-01-09 00:00:00.000

1
0
2022-01-10 00:00:00.000

7
0
2022-01-10 00:00:00.000

8
0
2022-01-10 00:00:00.000

1
0
2022-01-11 00:00:00.000

7
0
2022-01-11 00:00:00.000

8
0
2022-01-11 00:00:00.000

1
0
2022-01-12 00:00:00.000

7
0
2022-01-12 00:00:00.000

8
0
2022-01-12 00:00:00.000

1
0
2022-01-13 00:00:00.000

7
0
2022-01-13 00:00:00.000

8
0
2022-01-13 00:00:00.000

1
0
2022-01-14 00:00:00.000

7
0
2022-01-14 00:00:00.000

8
0
2022-01-14 00:00:00.000


Warning: Null value is eliminated by an aggregate or other SET operation.


DROP TABLE dbo.Calendar;
DROP TABLE dbo.Shops;
DROP TABLE dbo.Prices;

ShopID	Prices	Datefound
1	0	2022-01-01 00:00:00.000
7	0	2022-01-01 00:00:00.000
8	0	2022-01-01 00:00:00.000
1	0	2022-01-02 00:00:00.000
7	0	2022-01-02 00:00:00.000
8	0	2022-01-02 00:00:00.000
1	1	2022-01-03 00:00:00.000
7	1	2022-01-03 00:00:00.000
8	1	2022-01-03 00:00:00.000
1	0	2022-01-04 00:00:00.000
7	1	2022-01-04 00:00:00.000
8	1	2022-01-04 00:00:00.000
1	1	2022-01-05 00:00:00.000
7	1	2022-01-05 00:00:00.000
8	1	2022-01-05 00:00:00.000
1	0	2022-01-06 00:00:00.000
7	0	2022-01-06 00:00:00.000
8	0	2022-01-06 00:00:00.000
1	0	2022-01-07 00:00:00.000
7	0	2022-01-07 00:00:00.000
8	0	2022-01-07 00:00:00.000
1	0	2022-01-08 00:00:00.000
7	0	2022-01-08 00:00:00.000
8	0	2022-01-08 00:00:00.000
1	0	2022-01-09 00:00:00.000
7	0	2022-01-09 00:00:00.000
8	0	2022-01-09 00:00:00.000
1	0	2022-01-10 00:00:00.000
7	0	2022-01-10 00:00:00.000
8	0	2022-01-10 00:00:00.000
1	0	2022-01-11 00:00:00.000
7	0	2022-01-11 00:00:00.000
8	0	2022-01-11 00:00:00.000
1	0	2022-01-12 00:00:00.000
7	0	2022-01-12 00:00:00.000
8	0	2022-01-12 00:00:00.000
1	0	2022-01-13 00:00:00.000
7	0	2022-01-13 00:00:00.000
8	0	2022-01-13 00:00:00.000
1	0	2022-01-14 00:00:00.000
7	0	2022-01-14 00:00:00.000
8	0	2022-01-14 00:00:00.000