August 27, 2013 at 3:45 am
How do I write a query using the result of a second query in the WHERE clause where the second query returns two columns of data both dates PLUS it can return any number of rows.
If I was to write it long hand it would be:
SELECT SUM(Boiler1)
FROM Energy_Centre_Availability
WHERE
t_stamp > '20130824' and t_stamp < '20130825'
or
t_stamp > '20130827' and t_stamp < '20130828'
or
t_stamp > '20130829' and t_stamp < '20130830'
But I need to 'automate' the WHERE clause depending on the 'start' and 'end' dates returned from a 'calendar' table which can/will have more than one row:
SELECT SUM(Boiler1)
FROM Energy_Centre_Availability
WHERE t_stamp > (SELECT startDate FROM Energy_Centre_Boiler_Maintenance_Schedule)
and t_stamp < (SELECT endDate FROM Energy_Centre_Boiler_Maintenance_Schedule)
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,'20130825')
INSERT INTO @Energy_Centre_Availability VALUES (1,1,1,1,1,1,1,1,1,1,'20130826')
INSERT INTO @Energy_Centre_Availability VALUES (1,1,1,1,1,1,1,1,1,1,'20130827')
INSERT INTO @Energy_Centre_Availability VALUES (1,1,1,1,1,1,1,1,1,1,'20130828')
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-22 01:00','2013-08-23 01:00','red','Boiler1');
INSERT INTO @Energy_Centre_Boiler_Maintenance_Schedule VALUES ('2013-08-24 01:00','2013-08-25 01:00','red','Boiler1');
INSERT INTO @Energy_Centre_Boiler_Maintenance_Schedule VALUES ('2013-08-26 01:00','2013-08-27 01:00','red','Boiler1');
August 27, 2013 at 3:55 am
SELECT SUM(Boiler1)
FROM Energy_Centre_Availability eca
INNER JOIN Energy_Centre_Boiler_Maintenance_Schedule s
ON eca.t_stamp > s.startDate
AND eca.t_stamp < s.endDate
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 27, 2013 at 3:57 am
Would this do?
select *
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
August 27, 2013 at 4:27 am
Yes! That is exactly what I have spent the last week trying to achieve.
Thanks so much.
Could I just asked if I wanted the opposite of the results would the only way be this:
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)
Thanks again.
August 27, 2013 at 4:35 am
A more efficient way to get the opposite result is an OUTER JOIN and selecting the non-matching rows (WHERE ... IS NULL).
SELECT SUM(Boiler1)
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
August 27, 2013 at 4:38 am
SELECT SUM(Boiler1)
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
)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 27, 2013 at 5:04 am
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
????????
August 27, 2013 at 5:13 am
Could it be because you have ">=" in the first and ">" in the second?
John
August 27, 2013 at 5:15 am
You have changed some datetime values compared to your initial post/sample. If you alter the filters to include the (startdate and) enddate, both solutions will give the correct results
--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
)
August 27, 2013 at 6:06 am
Yes it was the addition of the = to my efforts without doing the same to the answers provided.:blush:
Both answers were (of course) correct.
Thanks again.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply