Erm... tried both answers and got different (and unexpected) results.
I'll explain better perhaps in case I didn't before (guilty of that way too often).
How would I get say the SUM of Boiler where it was outside of the calendar dates rather than where it was in between the set of dates.
Below is my attempts in SQL. I have 7 rows of data, 3 exist within the calendar (second table) and therefore 4 exist 'outside' of these dates.
DECLARE @Energy_Centre_Availability TABLE (
[ndx] [int] IDENTITY(1,1) NOT NULL,
[Boiler1] [int] NULL,
[Boiler1_req] [int] NULL,
[Boiler2] [int] NULL,
[Boiler2_req] [int] NULL,
[Engine1_status] [int] NULL,
[Engine1_req] [int] NULL,
[Engine1_Power] [float] NULL,
[Engine2_status] [int] NULL,
[Engine2_req] [int] NULL,
[Engine2_Power] [float] NULL,
[t_stamp] [datetime] NULL
);
INSERT INTO @Energy_Centre_Availability VALUES (1,1,1,1,1,1,1,1,1,1,'20130827 11:00:00')
INSERT INTO @Energy_Centre_Availability VALUES (1,1,1,1,1,1,1,1,1,1,'20130827 11:01:00')
INSERT INTO @Energy_Centre_Availability VALUES (1,1,1,1,1,1,1,1,1,1,'20130827 11:02:00')
INSERT INTO @Energy_Centre_Availability VALUES (1,1,1,1,1,1,1,1,1,1,'20130827 11:03:00')
INSERT INTO @Energy_Centre_Availability VALUES (1,1,1,1,1,1,1,1,1,1,'20130827 11:04:00')
INSERT INTO @Energy_Centre_Availability VALUES (1,1,1,1,1,1,1,1,1,1,'20130827 11:05:00')
INSERT INTO @Energy_Centre_Availability VALUES (1,1,1,1,1,1,1,1,1,1,'20130827 11:06:00')
DECLARE @Energy_Centre_Boiler_Maintenance_Schedule TABLE
(
[ndx] [int] IDENTITY(1,1) NOT NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL,
[DisplayColor] [nvarchar](50) NULL,
[Display] [nvarchar](50) NULL);
INSERT INTO @Energy_Centre_Boiler_Maintenance_Schedule VALUES ('2013-08-27 11:04:00','2013-08-27 11:05:00','red','Boiler1');
INSERT INTO @Energy_Centre_Boiler_Maintenance_Schedule VALUES ('2013-08-27 11:02:00','2013-08-27 11:02:00','red','Boiler1');
--Get Boiler1 when it is AT or BETWEEN Calendar dates
select sum(Boiler1) AS [INSIDE THE DATES]
from @Energy_Centre_Availability ECA
inner join @Energy_Centre_Boiler_Maintenance_Schedule ECBMS
on ECA.t_stamp >= ECBMS.StartDate
and ECA.t_stamp <= ECBMS.EndDate
--Get Boiler1 when it is OUTSIDE of Calendar dates
SELECT
(select sum(Boiler1)
from @Energy_Centre_Availability ECA)
-
(select sum(Boiler1)
from @Energy_Centre_Availability ECA
inner join @Energy_Centre_Boiler_Maintenance_Schedule ECBMS
on ECA.t_stamp >= ECBMS.StartDate
and ECA.t_stamp <= ECBMS.EndDate) AS [OUTSIDE THE DATES]
--Answer 1
SELECT SUM(Boiler1)AS [OUTSIDE THE DATES 2]
from @Energy_Centre_Availability ECA
LEFT OUTER join @Energy_Centre_Boiler_Maintenance_Schedule ECBMS
on ECA.t_stamp >= ECBMS.StartDate
and ECA.t_stamp < ECBMS.EndDate
WHERE ECBMS.ndx is null
--Answer 2
SELECT SUM(Boiler1) AS [OUTSIDE THE DATES 3]
FROM @Energy_Centre_Availability eca
WHERE NOT EXISTS (
SELECT 1
FROM @Energy_Centre_Boiler_Maintenance_Schedule s
WHERE eca.t_stamp > s.startDate
AND eca.t_stamp < s.endDate
)
I found answer 1 gave a result of 6 and answer 2 gave a result of 7
????????