Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to use SELECT query within the WHERE clause Expand / Collapse
Author
Message
Posted Tuesday, August 27, 2013 3:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 8, 2014 7:19 AM
Points: 42, Visits: 161
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');







Post #1488649
Posted Tuesday, August 27, 2013 3:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:44 AM
Points: 6,828, Visits: 14,066
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



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1488651
Posted Tuesday, August 27, 2013 3:57 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 8:18 AM
Points: 2,404, Visits: 2,940

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



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1488654
Posted Tuesday, August 27, 2013 4:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 8, 2014 7:19 AM
Points: 42, Visits: 161
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.
Post #1488663
Posted Tuesday, August 27, 2013 4:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 8:18 AM
Points: 2,404, Visits: 2,940
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



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1488664
Posted Tuesday, August 27, 2013 4:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:44 AM
Points: 6,828, Visits: 14,066
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
)



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1488665
Posted Tuesday, August 27, 2013 5:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 8, 2014 7:19 AM
Points: 42, Visits: 161
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
????????
Post #1488675
Posted Tuesday, August 27, 2013 5:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:45 AM
Points: 5,430, Visits: 10,104
Could it be because you have ">=" in the first and ">" in the second?

John
Post #1488680
Posted Tuesday, August 27, 2013 5:15 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 8:18 AM
Points: 2,404, Visits: 2,940
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
)



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1488681
Posted Tuesday, August 27, 2013 6:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 8, 2014 7:19 AM
Points: 42, Visits: 161
Yes it was the addition of the = to my efforts without doing the same to the answers provided.

Both answers were (of course) correct.

Thanks again.
Post #1488703
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse