filtered left joins.sql

CREATE TABLE DIMENSIONS
	([Campaign] varchar(1), [Device] varchar(1), [Date] date)
;
	
INSERT INTO DIMENSIONS
	([Campaign], [Device], [Date])
VALUES
	('A', 'M', '2017-10-07'),
	('A', 'D', '2017-10-07'),
	('A', 'T', '2017-10-07'),
   	('B', 'M', '2017-10-07'),
	('B', 'D', '2017-10-07'),
	('B', 'T', '2017-10-07'),
   	('C', 'M', '2017-10-07'),
	('C', 'D', '2017-10-07'),
	('C', 'T', '2017-10-07'),
	('A', 'M', '2017-10-08'),
	('A', 'D', '2017-10-08'),
	('A', 'T', '2017-10-08'),
   	('B', 'M', '2017-10-08'),
	('B', 'D', '2017-10-08'),
	('B', 'T', '2017-10-08'),
   	('C', 'M', '2017-10-08'),
	('C', 'D', '2017-10-08'),
	('C', 'T', '2017-10-08')
;

CREATE TABLE EntryCC
	([Campaign] varchar(1), 
     [Device] varchar(1), 
     [Date] date, 
     [Host] varchar(1),
     [G1] int, 
     [G2] int, 
     [G3] int)
;
	
INSERT INTO EntryCC
	([Campaign], [Device], [Host],[Date],[G1],[G2],[G3])
VALUES
	('A', 'M', 'C', '2017-10-07', 100, 100, 10),
	('A', 'M', 'L', '2017-10-07', 101, 1001, 11), --Duplicate entry due to different host on the same date
    ('A', 'D', 'C','2017-10-07', 200, 6532, 20),
	('A', 'T', 'C','2017-10-07',  30, 3215, 3),
    ('B', 'M', 'C','2017-10-07', 110, 100, 10),
	('B', 'D', 'C','2017-10-07', 220, 3331, 23),
	('B', 'T', 'C','2017-10-07',  11, 4445, 1),
    ('C', 'M', 'C','2017-10-07', 110, 5645, 10),
	('C', 'D', 'C','2017-10-07', 220, 3331, 23),
	('C', 'T', 'C','2017-10-07',  11, 4445, 1),
  	('A', 'M', 'C','2017-10-08', 100, 5575, 10),
	('A', 'D', 'C','2017-10-08', 200, 6532, 20),
	('A', 'T', 'C','2017-10-08',  30, 3215, 3),
	('B', 'M', 'C','2017-10-08', 110, 5645, 10),
	('B', 'D', 'C','2017-10-08', 220, 3331, 23),
	('B', 'T', 'C','2017-10-08',  11, 4445, 1),
    ('C', 'M', 'C','2017-10-08', 110, 5645, 10),
	('C', 'D', 'C','2017-10-08', 220, 3331, 23),
	('C', 'T', 'C','2017-10-08',  11, 4445, 1)
;
CREATE TABLE EntryMain
	([Campaign] varchar(1), 
     [Device] varchar(1), 
     [Date] date, 
     [G4] int, 
     [G5] int, 
     [G6] int)
;

INSERT INTO EntryMain
	([Campaign], [Device], [Date],[G4],[G5],[G6])
VALUES
	('A', 'M', '2017-10-07', 100, 5000, 10),
	('A', 'D', '2017-10-07', 200, 6532, 20),
	('A', 'T', '2017-10-07',  30, 3215, 3),
    ('B', 'M', '2017-10-07', 110, 6000, 10),
	('B', 'D', '2017-10-07', 220, 3331, 23),
	('B', 'T', '2017-10-07',  11, 4445, 1),
    ('C', 'M', '2017-10-07', 110, 5645, 10),
	('C', 'D', '2017-10-07', 220, 3331, 23),
	('C', 'T', '2017-10-07',  11, 4445, 1),
  	('A', 'M', '2017-10-08', 100, 5575, 10),
	('A', 'D', '2017-10-08', 200, 6532, 20),
	('A', 'T', '2017-10-08',  30, 3215, 3),
	('B', 'M', '2017-10-08', 110, 5645, 10),
	('B', 'D', '2017-10-08', 220, 3331, 23),
	('B', 'T', '2017-10-08',  11, 4445, 1),
    ('C', 'M', '2017-10-08', 110, 5645, 10),
	('C', 'D', '2017-10-08', 220, 3331, 23),
	('C', 'T', '2017-10-08',  11, 4445, 1)
;

CREATE TABLE EntryLPages
	([Campaign] varchar(1), 
     [Device] varchar(1), 
     [Date] date, 
     [G7] int, 
     [G8] int, 
     [G9] int)
;

INSERT INTO EntryLPages
	([Campaign], [Device], [Date],[G7],[G8],[G9])
VALUES
	('A', 'M', '2017-10-07', 100, 5000, 10),
	('A', 'D', '2017-10-07', 200, 6532, 20),
	('A', 'T', '2017-10-07',  30, 3215, 3),
    ('B', 'M', '2017-10-07', 110, 6000, 10),
	('B', 'D', '2017-10-07', 220, 3331, 23),
	('B', 'T', '2017-10-07',  11, 4445, 1),
    ('C', 'M', '2017-10-07', 110, 5645, 10),
	('C', 'D', '2017-10-07', 220, 3331, 23),
	('C', 'T', '2017-10-07',  11, 4445, 1),
  	('A', 'M', '2017-10-08', 100, 5575, 10),
	('A', 'D', '2017-10-08', 200, 6532, 20),
	('A', 'T', '2017-10-08',  30, 3215, 3),
	('B', 'M', '2017-10-08', 110, 5645, 10),
	('B', 'D', '2017-10-08', 220, 3331, 23),
	('B', 'T', '2017-10-08',  11, 4445, 1),
    ('C', 'M', '2017-10-08', 110, 5645, 10),
	('C', 'D', '2017-10-08', 220, 3331, 23),
	('C', 'T', '2017-10-08',  11, 4445, 1)
;

CREATE TABLE EntryAW
	([Campaign] varchar(1), 
     [Device] varchar(1), 
     [Date] date, 
     [G7] int, 
     [G8] int, 
     [G9] int)
;
INSERT INTO EntryAW
	([Campaign], [Device], [Date],[G7],[G8],[G9])
VALUES
	('A', 'M', '2017-10-07', 10, 5000, 10),
	('A', 'D', '2017-10-07', 10, 6532, 20),
	('A', 'T', '2017-10-07', 10, 3215, 3),
    ('B', 'M', '2017-10-07', 10, 6000, 10),
	('B', 'D', '2017-10-07', 10, 3331, 23),
	('B', 'T', '2017-10-07', 10, 4445, 1),
    ('C', 'M', '2017-10-07', 10, 5645, 10),
	('C', 'D', '2017-10-07', 10, 3331, 23),
	('C', 'T', '2017-10-07', 10, 4445, 1),
  	('A', 'M', '2017-10-08', 10, 5575, 10),
	('A', 'D', '2017-10-08', 10, 6532, 20),
	('A', 'T', '2017-10-08', 10, 3215, 3),
	('B', 'M', '2017-10-08', 10, 5645, 10),
	('B', 'D', '2017-10-08', 10, 3331, 23),
	('B', 'T', '2017-10-08', 10, 4445, 1),
    ('C', 'M', '2017-10-08', 10, 5645, 10),
	('C', 'D', '2017-10-08', 10, 3331, 23),
	('C', 'T', '2017-10-08', 10, 4445, 1)
;



SELECT d.Campaign, d.Device,d.Date, e.G1,e.G2,e.G3,f.G4,f.G5,f.G6,g.G7,g.G8,g.G9,h.g7 cost
  FROM DIMENSIONS d
  LEFT JOIN EntryCC e
         ON d.Campaign=e.Campaign 
        AND d.Device=e.Device 
        AND d.Date=e.Date 
  --     AND e.Date='2017-10-07'
  LEFT JOIN EntryMain f
         ON d.Campaign=f.Campaign 
        AND d.Device=f.Device 
        AND d.Date=f.Date 
  --    AND f.Date='2017-10-07'
    LEFT JOIN EntryLPages g
         ON d.Campaign=g.Campaign 
        AND d.Device=g.Device 
        AND d.Date=g.Date 
    --  AND g.Date='2017-10-07'
        LEFT JOIN EntryAW h
         ON d.Campaign=h.Campaign 
        AND d.Device=h.Device 
        AND d.Date=h.Date 
    --  AND g.Date='2017-10-07'
Where d.Date='2017-10-07'
;



SELECT sum(h.g7) as UnifiedCost
  FROM DIMENSIONS d
  LEFT JOIN EntryCC e
         ON d.Campaign=e.Campaign 
        AND d.Device=e.Device 
        AND d.Date=e.Date 
  --     AND e.Date='2017-10-07'
  LEFT JOIN EntryMain f
         ON d.Campaign=f.Campaign 
        AND d.Device=f.Device 
        AND d.Date=f.Date 
  --    AND f.Date='2017-10-07'
    LEFT JOIN EntryLPages g
         ON d.Campaign=g.Campaign 
        AND d.Device=g.Device 
        AND d.Date=g.Date 
    --  AND g.Date='2017-10-07'
        LEFT JOIN EntryAW h
         ON d.Campaign=h.Campaign 
        AND d.Device=h.Device 
        AND d.Date=h.Date 
    --  AND g.Date='2017-10-07'
Where d.Date='2017-10-07';


SELECT sum(h.g7) as AWCost
  FROM DIMENSIONS d
        LEFT JOIN EntryAW h
         ON d.Campaign=h.Campaign 
        AND d.Device=h.Device 
        AND d.Date=h.Date 
    --  AND g.Date='2017-10-07'
Where d.Date='2017-10-07';


SELECT count(*) as comibinationsOfDim FROM DIMENSIONS 
;

SELECT count(*) as comibinationsOfUnified
  FROM DIMENSIONS d
  LEFT JOIN EntryCC e
         ON d.Campaign=e.Campaign 
        AND d.Device=e.Device 
        AND d.Date=e.Date 
  --     AND e.Date='2017-10-07'
  LEFT JOIN EntryMain f
         ON d.Campaign=f.Campaign 
        AND d.Device=f.Device 
        AND d.Date=f.Date 
  --    AND f.Date='2017-10-07'
    LEFT JOIN EntryLPages g
         ON d.Campaign=g.Campaign 
        AND d.Device=g.Device 
        AND d.Date=g.Date 
    --  AND g.Date='2017-10-07'
        LEFT JOIN EntryAW h
         ON d.Campaign=h.Campaign 
        AND d.Device=h.Device 
        AND d.Date=h.Date 
    --  AND g.Date='2017-10-07'
;

this is a basis for dimension table testing of star schema DWH layouts