Display lines with 0 for measures if date not in date dimension table

Question

I have difficulties to make a join between a fact table and a date dimension table because I would like to display records with a date not in the dimension table.

Example : I don't have records for 2014-09-08 at 01:00 when I try this query because there is no records in the fact table with these filters.

select *
from FCT_SCAN scan
left join dim_date dt
on cast ( scan.DATE_HEURE as date ) = dt.DATE
and cast(cast ( scan.DATE_HEURE as time(0)) as varchar(5)) = CAST(dt.heure as varchar(5))
where CAST(scan.DATE_HEURE as DATE) = '2014-08-09'
and tranche_1h = '01:00:00'
order by heure

And I would like to display records with NULL or 0 values for the fields if the DATE_HEURE field is not in the dimension table.

Edit 1:
First I rewrite my initial query with the good prefixes for a better understanding.

select *
from FCT_SCAN scan
left join dim_date dt
on cast ( scan.DATE_HEURE as date ) = dt.DATE
and cast(cast ( scan.DATE_HEURE as time(0)) as varchar(5)) = CAST(dt.heure as varchar(5))
where CAST(scan.DATE_HEURE as date) = '2014-08-09'
and dt.tranche_1h = '01:00:00'
order by dt.heure

My problem is the following : I'm searching a special conditional join which will allow me to link my fact table with my date dimension table in Cognos. And this join must allow me to display "empty" records if some datetimes in the dimension table are not in the fact table AND records in the fact table if datetimes are present.

Update : Here are CREATE TABLE and SELECT scripts of DIM_DATE.

CREATE TABLE [dbo].[DIM_DATE](
    [DATE_HEURE] [datetime] NOT NULL,
    [ANNEE] [int] NULL,
    [MOIS] [int] NULL,
    [JOUR] [int] NULL,
    [DATE] [date] NULL,
    [JOUR_SEM_DATE] [varchar](10) NULL,
    [NUM_JOUR_SEM_DATE] [int] NULL,
    [HEURE] [time](0) NULL,
    [TRANCHE_1H] [time](0) NULL,
    [TRANCHE_DEMIH] [time](0) NULL,
    [TRANCHE_QUARTH] [time](0) NULL,
    [TRANCHE_10M] [time](0) NULL,
 CONSTRAINT [PK_DIM_DATE] PRIMARY KEY CLUSTERED 
(
    [DATE_HEURE] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

DATE_HEURE  ANNEE   MOIS    JOUR    DATE    JOUR_SEM_DATE   NUM_JOUR_SEM_DATE   HEURE   TRANCHE_1H  TRANCHE_DEMIH   TRANCHE_QUARTH  TRANCHE_10M
2013-01-01 00:00:00.000 2013    1   1   2013-01-01  Tuesday 3   00:00:00    00:00:00    00:00:00    00:00:00    00:00:00
2013-01-01 00:01:00.000 2013    1   1   2013-01-01  Tuesday 3   00:01:00    00:00:00    00:00:00    00:00:00    00:00:00
2013-01-01 00:02:00.000 2013    1   1   2013-01-01  Tuesday 3   00:02:00    00:00:00    00:00:00    00:00:00    00:00:00
2013-01-01 00:03:00.000 2013    1   1   2013-01-01  Tuesday 3   00:03:00    00:00:00    00:00:00    00:00:00    00:00:00
2013-01-01 00:04:00.000 2013    1   1   2013-01-01  Tuesday 3   00:04:00    00:00:00    00:00:00    00:00:00    00:00:00
2013-01-01 00:05:00.000 2013    1   1   2013-01-01  Tuesday 3   00:05:00    00:00:00    00:00:00    00:00:00    00:00:00
2013-01-01 00:06:00.000 2013    1   1   2013-01-01  Tuesday 3   00:06:00    00:00:00    00:00:00    00:00:00    00:00:00
2013-01-01 00:07:00.000 2013    1   1   2013-01-01  Tuesday 3   00:07:00    00:00:00    00:00:00    00:00:00    00:00:00
2013-01-01 00:08:00.000 2013    1   1   2013-01-01  Tuesday 3   00:08:00    00:00:00    00:00:00    00:00:00    00:00:00
2013-01-01 00:09:00.000 2013    1   1   2013-01-01  Tuesday 3   00:09:00    00:00:00    00:00:00    00:00:00    00:00:00

There is 1 record by minute from 2013-01-01 00:00:00 to 2017-12-31 23:59:00 stored in this table. We make the join between a DATE_HEURE field in FCT_SCAN and the fields of DIM_DATE.

Here is the DATE_HEURE field in FCT_SCAN :

DATE_HEURE
2014-10-17 21:39:27.000
2014-10-17 21:44:37.000
2014-10-17 23:14:05.000
2014-10-17 23:14:01.000
2014-10-17 21:40:09.000
2014-10-17 21:44:25.000
2014-10-17 21:41:41.000
2014-10-17 21:41:51.000
2014-10-17 21:48:12.000
2014-10-17 23:09:32.000

I don't show you all the fields of FCT_SCAN because there is about 180 fields so...

Edit 2:
For information, my desired output looks like this if there is no data between 01:00 and 01:30 :

DATE_HEURE   FIELD0   FIELD1   FIELD2   MEASURE0   MEASURE1   MEASURE2

2015-02-03 00:00:00   XXX   XXX   XXX   5   42   23
2015-02-03 00:30:00   XXX   XXX   XXX   5   42   23
2015-02-03 01:00:00   NULL   NULL   NULL   0   0   0
2015-02-03 01:30:00   NULL   NULL   NULL   0   0   0
2015-02-03 02:00:00   XXX   XXX   XXX   5   42   23
2015-02-03 02:30:00   XXX   XXX   XXX   5   42   23

you should consider restructuring your tables so you can implement Foreign Keys. — mxix, Feb 02 '15 at 17:06
Do you mean there is no data for that hour in the *fact* table? Not sure why it should be missing from the *dimension* table. If you want to include rows from the dimension table even when there is no data in the fact table, your left join is backwards. Also, please prefix *all* columns with table aliases so people don't have to guess which columns come from which table. — Aaron Bertrand, Feb 02 '15 at 17:08
Still not to clear for me. But it looks as your condition `and dt.tranche_1h = '01:00:00'` in the where part will filter out any row not fitting the condition. Since you are using a left join, move this condition into the join part to get the desired result. — bummi, Mar 05 '15 at 08:16

Giorgi Nakeuri · Answer 1 · 2015-02-02T17:11:01.283

Try Outer Apply:

select *
from FCT_SCAN scan
OUTER APPLY( select * from dim_date dt
where cast ( scan.DATE_HEURE as date ) = dt.DATE
and cast(cast ( scan.DATE_HEURE as time(0)) as varchar(5)) = CAST(dt.heure as varchar(5))) o
where CAST(scan.DATE_HEURE as DATE) = '2014-08-09'
and tranche_1h = '01:00:00'
order by heure

If tranche_1h column is from dim_date then use:

select *
from FCT_SCAN scan
OUTER APPLY( select * from dim_date dt
where cast ( scan.DATE_HEURE as date ) = dt.DATE
and cast(cast ( scan.DATE_HEURE as time(0)) as varchar(5)) = CAST(dt.heure as varchar(5)) and tranche_1h = '01:00:00') o
where CAST(scan.DATE_HEURE as DATE) = '2014-08-09'
order by heure

Sorry it doesn't work... Check out my new post for more explanations. — Jean-Christophe CASTELLANA, Feb 03 '15 at 09:58

Christian Barron · Answer 2 · 2015-02-03T15:41:02.490

0

You may just be having issues as you need the time to the minute rather than second?

select *
from fct_scan scan
RIGHT JOIN dim_date dt 
on dt.Date = cast ( scan.DATE_HEURE as date )
and  cast(cast(cast(scan.DATE_HEURE as time(0)) as varchar(5)) as time) = dt.heure
order by heure

edited Feb 03 '15 at 15:41

answered Feb 02 '15 at 17:06

Christian Barron

2,695
1
14
22

Sorry it doesn't work... Check out my new post for more explanations. – Jean-Christophe CASTELLANA Feb 03 '15 at 09:58
I've added an answer. – Jean-Christophe CASTELLANA Feb 03 '15 at 14:59

Display lines with 0 for measures if date not in date dimension table

2 Answers2