SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to use SELECT query within the WHERE clause


How to use SELECT query within the WHERE clause

Author
Message
brett.y
brett.y
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 166
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');

ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15988 Visits: 19524
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
HanShi
HanShi
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4355 Visits: 3668
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’! **
brett.y
brett.y
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 166
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.
HanShi
HanShi
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4355 Visits: 3668
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’! **
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15988 Visits: 19524
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
brett.y
brett.y
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 166
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
????????
John Mitchell-245523
John Mitchell-245523
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13892 Visits: 15926
Could it be because you have ">=" in the first and ">" in the second?

John
HanShi
HanShi
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4355 Visits: 3668
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’! **
brett.y
brett.y
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 166
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search