Fetch date as null if empty table

Question

I have a table of Locations

SELECT 
    "location_id",
    "location_code",
    "location_line_1",
    "location_line_2",
    "location_line_3",
    "location_line_4"
FROM
    "location"

and brings

1    AM00758    line1    line2    line3    line4
2    PF00517    line1    line2    line3    line4
3    RFTA967    line1    line2    line3    line4

And a second table to archive the number of exclusions made of that day on which location

SELECT 
    "daily_exclusion_id",
    "location_id", --From Location
    "exclusion_date",
    "exclution_absence",
    "exclution_no_info",
    "exclusion_foregin_info"
FROM
    "daily_exclusion"

and brings

1    1    2014-06-01    15    32    45
2    2    2014-06-01    23    10    81
3    3    2014-06-01    18    30    70
4    1    2014-06-02    63    34    12
5    2    2014-06-02    34    16    2
6    3    2014-06-02    51    18    9
7    1    2014-06-04    13    18    7
8    2    2014-06-04    18    36    19
9    3    2014-06-04    5     7     29

So... to fill this on a screen, it prints every locations for a selected date, and you fill the 3 exclusions with the number of people that used that exclusion (absence, no info and foregin info)

THE PROBLEM: is, some days the info is empty, if the user register day 1, 2, and misses 3, but keeps going on 4, 5, etc... when i do the select it doesn't bring those missing dates as null

SELECT 
    l.*, de.*
FROM
    location l
LEFT OUTER JOIN
    daily_exclusion de
ON (
    de.location_id = l.location_id
AND
    de.exclusion_date between '20140601' and '20140604' --HERE'S THE DIFFERENCE
)
WHERE
vl.location_id IN (1,2,3)

But it only brings the registered days

1    AM00758    line1    line2    line3    line4    1    1    2014-06-01    15    32    45
1    AM00758    line1    line2    line3    line4    4    1    2014-06-02    63    34    12
1    AM00758    line1    line2    line3    line4    7    1    2014-06-04    13    18    7
2    PF00517    line1    line2    line3    line4    2    2    2014-06-01    23    10    81
2    PF00517    line1    line2    line3    line4    5    2    2014-06-02    34    16    2
2    PF00517    line1    line2    line3    line4    8    2    2014-06-04    18    36    19
3    RFTA967    line1    line2    line3    line4    3    3    2014-06-01    18    30    70
3    RFTA967    line1    line2    line3    line4    6    3    2014-06-02    51    18    9
3    RFTA967    line1    line2    line3    line4    9    3    2014-06-04    5     7     29

And i need the nulls for day 3

1    AM00758    line1    line2    line3    line4    1    1    2014-06-01    15    32    45
1    AM00758    line1    line2    line3    line4    4    1    2014-06-02    63    34    12
1    AM00758    line1    line2    line3    line4    null null 2014-06-03    null  null null
1    AM00758    line1    line2    line3    line4    7    1    2014-06-04    13    18    7
2    PF00517    line1    line2    line3    line4    2    2    2014-06-01    23    10    81
2    PF00517    line1    line2    line3    line4    5    2    2014-06-02    34    16    2
2    PF00517    line1    line2    line3    line4    null null 2014-06-03    null  null null
2    PF00517    line1    line2    line3    line4    8    2    2014-06-04    18    36    19
3    RFTA967    line1    line2    line3    line4    3    3    2014-06-01    18    30    70
3    RFTA967    line1    line2    line3    line4    6    3    2014-06-02    51    18    9
3    RFTA967    line1    line2    line3    line4    null null 2014-06-03    null  null  null
3    RFTA967    line1    line2    line3    line4    9    3    2014-06-04    5     7     29

And to do this i found a little work around... but i can't afford to do this dynamical query

SELECT * FROM (
SELECT
    convert(date, '20140601', 120) as the_date
    l.*, de.*
FROM
    location l
LEFT OUTER JOIN
    daily_exclusion de
ON (
    de.location_id = l.location_id
AND
    de.exclusion_date = '20140601' --HERE'S THE DIFFERENCE
)
WHERE
vl.location_id IN (1,2,3)
UNION ALL --CAN'T AFFORD TO DO THIS
SELECT
    convert(date, '20140602', 120) as the_date
    l.*, de.*
FROM
    location l
LEFT OUTER JOIN
    daily_exclusion de
ON (
    de.location_id = l.location_id
AND
    de.exclusion_date = '20140602' --HERE'S THE DIFFERENCE
)
WHERE
vl.location_id IN (1,2,3)
UNION ALL --CAN'T AFFORD TO DO THIS
SELECT
    convert(date, '20140603', 120) as the_date
    l.*, de.*
FROM
    location l
LEFT OUTER JOIN
    daily_exclusion de
ON (
    de.location_id = l.location_id
AND
    de.exclusion_date = '20140603' --HERE'S THE DIFFERENCE
)
WHERE
vl.location_id IN (1,2,3)
)
as t
ORDER BY the_date asc

And so on... It's like Returning NULLs in SQL if joined table is missing records for that Date but i don't have a table with dates registeded

Could you do it as a sub-select? Like: Select *, (select l.location_id from location l where l.location_id = de.location_id) from daily_exclusion de where de.exclusion_date = '20140602' — Justin Killen
– Justin Killen, Commented Jun 30, 2014 at 23:31

Monty Wild · Accepted Answer · 2014-06-30 23:58:44Z

1

You could consider using a recursive Common Table Expression (CTE) to generate a list of all dates between the criteria dates.

E.g.:

WITH cteDates (ADate) AS
(
SELECT CAST('20140601' as date) as ADate

UNION ALL

SELECT DATEADD(day, 1, ADate)
FROM cteDates
WHERE DATEADD(day, 1, ADate) <= CAST('20140604' as date)
)

Insert that at the head of your query, and use cteDates as another table reference, probably in a CROSS JOIN with Location.

answered Jun 30, 2014 at 23:58

Monty Wild

3,9991 gold badge26 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Fetch date as null if empty table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related