Create a gist now

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQL 2012 Milne and Fraser Daily and Hourly Totals. Milne 3 Floors totals caclulated, totalled, joined and added to corresponding Fraser totals.
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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment