Can I "left join" days between 2 dates in sql server?-CodePudding

There is a table in SQL Server where data is entered day by day. In this table, data is not filled in some days.

Therefore, there are no records in the table.

Sample: dataTable

I need to generate a report like the one below from this table.

Create a table with all the days of the year. I know that I can output a report by "joining" the "dataTable" table.

But this solution seems a bit strange to me.

Is there another way?

the code i use for temp date table

CREATE TABLE tempDate (
  calendarDate date,
  PRIMARY KEY (calendarDate)
)

DECLARE
  @start DATE= '2021-01-01',
  @dateCount   INT= 730,
  @rowNumber   INT=1
  
WHILE (@rowNumber < @dateCount)
BEGIN
  INSERT INTO tempDate values (DATEADD(DAY, @rowNumber, @start))
  set @rowNumber=@rowNumber 1
END

GO
select * from tempDate

This is how I join using this table

SELECT
        *
FROM
        tempDate td WITH (NOLOCK)
        LEFT JOIN dataTable dt WITH (NOLOCK) ON dt.reportDate = td.calendarDate
WHERE
        td.calendarDate BETWEEN '2021-09-05' AND '2021-09-15'

CodePudding user response：

Create a table with all the days of the year. I know that I can output a report by "joining" the "dataTable" table.

This is the way. You can generate that "table" on the fly if you really want to, but normally the best way is to simply have a calendar table.

CodePudding user response：

You can use common expression tables for dates. The code you need:

IF(OBJECT_ID('tempdb..#t') IS NOT NULL)
BEGIN
    DROP TABLE #t
END

CREATE TABLE #t
    (
    id int,
    dt date,
    dsc varchar(100),
    )

INSERT INTO #t 
VALUES
    (1, '2021.09.08', 'a'),
    (1, '2021.09.09', 'b'),
    (1, '2021.09.12', 'c')

DECLARE @minDate AS DATE
SET @minDate = (SELECT MIN(dt) FROM #t)
DECLARE @maxDate AS DATE
SET @maxDate = (SELECT MAX(dt) FROM #t)

;WITH cte
AS
(
    SELECT @minDate AS [dt]

    UNION ALL

    SELECT DATEADD(DAY, 1, [dt])
    FROM cte
    WHERE DATEADD(DAY, 1, [dt])<=@maxDate
)

SELECT 
    ISNULL(CAST(t.id AS VARCHAR(10)), '') AS [id],
    cte.dt AS [dt],
    ISNULL(t.dsc, 'No record has been entered in the table.') AS [dsc]
FROM 
    cte
    LEFT JOIN #t t on t.dt=cte.dt

CodePudding user response：

the fastest method is to use a numbers table, you can get a date list between 2 dates whit that:

DECLARE @Date1 DATE, @Date2 DATE
SET @Date1 = '20200528'
SET @Date2 = '20200625'

SELECT DATEADD(DAY,number 1,@Date1) [Date]
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY,number 1,@Date1) < @Date2

so if you go go in LEFT JOIN this select, whit your table, you have the result that you whant.

SELECT *
FROM (  SELECT DATEADD(DAY,number 1,@Date1) [Date]
        FROM master..spt_values WITH (NOLOCK) 
        WHERE type = 'P'
        AND DATEADD(DAY,number 1,@Date1) < @Date2 ) as a
LEFT JOIN yourTable dt WITH (NOLOCK) 
    ON a.date = dt.reportDate 
WHERE td.[Date] BETWEEN '2021-09-05' AND '2021-09-15'