Geek asks and answers

How to select records that do not exist on the Sql server

This is not a test to see if a record exists before selecting it, or vice versa.

The problem is that I have a database with many records, they are stored by date, by day, to be specific, every day should have a set of values, but some days may not have any values ​​at all, they may not exist. Therefore, when I try to execute a query, I need to show all the data, even if it does not technically exist, it can be displayed at least as an empty space, but it should be displayed.

Think about this table:

 ___________________________________
| id |    date    | value1 | value2 |
|  1 | 02/01/2014 |    1   |    1   |
|  2 | 02/03/2014 |    2   |    2   |
|  3 | 02/04/2014 |    3   |    3   |
|  4 | 02/06/2014 |    4   |    4   |

Now, what needs to be done is to return all data between a certain date range, for example, from 02/01/2014to 02/03/2014, it should return all values ​​between this date range, for example:

SELECT * FROM myTable WHERE date>=`02/01/2014` AND date<=`02/03/2014`

But this will only return this:

 ___________________________________
| id |    date    | value1 | value2 |
|  1 | 02/01/2014 |    1   |    1   |
|  2 | 02/03/2014 |    2   |    2   |

Now I know that this is pretty obvious, it selects all the data that exists in the table, given the range that I sent to the request, but I work with a report that does not care whether there were values ​​for this given day or not, it must return every day, even if there were records for that day or not, which means that even if 02/02/2014it is not in the table, it must also be returned.

Example:

 ___________________________________
| id |    date    | value1 | value2 |
|  1 | 02/01/2014 |    1   |    1   |
|    | 02/02/2014 |        |        |
|  2 | 02/03/2014 |    2   |    2   |

It does not matter that he has no data, he simply can not leave the day, as if this did not happen.

COALESCE ISNULL, , , Null - .

- ?

+3
3

CTE. - :

DECLARE @startDate datetime = '2/1/2014'
DECLARE @endDate datetime = '2/6/2014'

;WITH DateRange(RunningDate) AS
(
    SELECT @startDate AS RunningDate
    UNION ALL
    SELECT RunningDate + 1
    FROM DateRange
    WHERE RunningDate < @endDate
)

SELECT id, RunningDate date, value1, value2 
FROM DateRange LEFT JOIN myTable ON myTable.date = DateRange.RunningDate

... ( Aaron Bertrand ), , max recursion, 3 . 100. , , , 101 (100 ).

OPTION (MAXRECURSION 0) SELECT, . 0 . , .

+3

:

WITH
    -- Numbers CTE, courtesy Itzik Ben-Gan
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L4),
    -- Turn into Dates
  Dates AS(SELECT CAST('18991231' AS DATETIME)+n AS d FROM Nums)
SELECT * 
FROM Dates d
LEFT JOIN myTable t ON d.d = t.[date]
WHERE d.d>='02/01/2014' AND d.d<='02/03/2014'

, :

DECLARE @startDate datetime = '2/1/2014'
DECLARE @endDate datetime = '2/3/2014'
DECLARE @days as INT = DATEDIFF(dd, @startDate, @enddate) + 1
;
WITH
    -- Numbers CTE, courtesy Itzik Ben-Gan
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  T4   AS(SELECT TOP (@days) 1 AS c FROM L4),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1 AS n FROM T4),
    -- Turn into Dates
  Dates AS(SELECT @startDate+n AS d FROM Nums)
SELECT * 
FROM Dates d
LEFT JOIN myTable t ON d.d = t.[date]
WHERE d.d>=@startDate AND d.d<=@endDate
+3

Eric Fan had the right idea, but I thought it might help to see the implementation:

Here is the function that will return the table of inclusive dates:

CREATE FUNCTION dbo.fDatesBetween(@startDate Date, @endDate Date)
    RETURNS @tbl TABLE
        (
            a_date      Date
        )
AS BEGIN
    DECLARE @thisDt Date
    SET @thisDt = @startDate

    WHILE @thisDt <= @endDate
    BEGIN
        INSERT INTO @tbl SELECT @thisDt
        SET @thisDt = DateAdd(day, 1, @thisDt)
    END
    RETURN
END

Now, if you are doing an outer join to your table from the results of a function, you will have what you are looking for:

SELECT DATES.a_date, SampleTable.value1, SampleTable.value2
  FROM dbo.fDatesBetween(@startDate, @endDate) DATES
       LEFT JOIN SampleTable
          ON DATES.a_date = SampleTable.dt

That should give you what you asked for.

+1
source

More articles:


All Articles