SQL 2012 Milne and Fraser Daily and Hourly Totals. Milne 3 Floors totals caclulated, totalled, joined and added to corresponding Fraser totals.

HeadCountsMilneFraser.sql

CREATE TABLE HeadCountsMilne
(
ID int identity(1,1) not null primary key,
Date datetime,
Hour varchar(50),
FirstFloor int,
MainFloor int,
ThirdFloor int,
TotalMilneThreeFloors int,
FraserID int,
TotalMilnePlusFraser int
);


CREATE TABLE HeadCountsFraser
(
FraserID int identity(1,1) not null primary key,
DateFraser datetime,
HourFraser varchar(50),
Fraser int
);


ALTER TABLE HeadCountsMilne ADD CONSTRAINT FK_HeadCountsMilne_HeadCountsFraser FOREIGN KEY(FraserID)
REFERENCES HeadCountsFraser (FraserID);

ALTER TABLE HeadCountsMilne CHECK CONSTRAINT FK_HeadCountsMilne_HeadCountsFraser;



INSERT INTO HeadCountsMilne
(Date, Hour, FirstFloor, MainFloor, ThirdFloor,TotalMilneThreeFloors)
VALUES
('5/8/2014 12:00:00 AM', '3PM','102','78','118','298'),
('5/8/2014 12:00:00 AM', '10AM','30','48','199','277'),
('5/7/2014 12:00:00 AM', '1PM','93','98','66','257'),
('5/7/2014 12:00:00 AM', '12PM','84','78','89','251'),
('5/7/2014 12:00:00 AM', '11AM','34','44','233','311'),
('5/7/2014 12:00:00 AM', '10AM','65','101','99','265'),
('5/6/2014 12:00:00 AM', '12PM','88','159','123','370'),
('5/6/2014 12:00:00 AM', '9AM','265','112','49','426'),
('5/6/2014 12:00:00 AM', '8AM','234','97','154','485')
;


INSERT INTO HeadCountsFraser
(DateFraser, HourFraser, Fraser)
VALUES
('5/8/2014 12:00:00 AM', '3PM','78'),
('5/7/2014 12:00:00 AM', '2PM','42'),
('5/7/2014 12:00:00 AM', '1PM','24'),
('5/7/2014 12:00:00 AM', '12PM','53'),
('5/7/2014 12:00:00 AM', '10AM','19'),
('5/6/2014 12:00:00 AM', '4PM','68'),
('5/6/2014 12:00:00 AM', '3PM','24'),
('5/6/2014 12:00:00 AM', '12PM','59'),
('5/6/2014 12:00:00 AM', '12AM','74')
;


select *
	from (select *
		from (SELECT m.ID,f.FraserID, 
					m.Date, 
					m.Hour, 
					f.DateFraser,
					f.HourFraser,
					m.FirstFloor, 
					m.MainFloor, 
					m.ThirdFloor, 
					m.TotalMilneThreeFloors, 
					f.Fraser, 
					(m.TotalMilneThreeFloors + f.Fraser) AS TotalMilnePlusFraser
					FROM HeadCountsMilne m
					left JOIN HeadCountsFraser f 
						ON m.Date=f.DateFraser 
						and m.Hour=f.HourFraser
				union
				SELECT m.ID,f.FraserID, 
					m.Date, 
					m.Hour, 
					f.DateFraser,
					f.HourFraser,
					m.FirstFloor, 
					m.MainFloor, 
					m.ThirdFloor, 
					m.TotalMilneThreeFloors, 
					f.Fraser, 
					(m.TotalMilneThreeFloors + f.Fraser) AS TotalMilnePlusFraser
					FROM HeadCountsMilne m
					right JOIN HeadCountsFraser f  
						ON m.Date=f.DateFraser 
						and m.Hour=f.HourFraser
		) j
	)s

	order by isnull(ID,FraserID)