CODEWITHSUNDEEP
sql-servert-sql
user2046734
user2046734

Reputation:

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

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

enter image description here

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

enter image description here

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'

Upvotes: 1

Views: 158

Answers (3)

Antonio Calo
Antonio Calo

Reputation: 53

The fastest method is to use a numbers table, you can get a date list between 2 dates with 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

If you go go in LEFT JOIN this select, whit your table, you have the result that you want.

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'

Upvotes: 0

mithderler
mithderler

Reputation: 33

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

Upvotes: 0

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89006

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.

Upvotes: 5

Related Questions