nesting a SELECT query into another SELECT query

Question

Post reply

nesting a SELECT query into another SELECT query

ebankaccount

SSC Rookie

Points: 26
More actions
May 20, 2008 at 3:25 pm

#71600

Hi. Thanks in advance for your help.
table name: tbl_Log
This table is used by the surveillance system. The SystemID represents each camera and each row represents a daily log of whether or not the recording process was successful (T or F) at the end of the day. Not all cameras write back the log daily, so sometimes there won't be any log from particular cameras.
Below is a sample data:
[ID] [SystemID] [Confirmation] [RecordDate]
1 11111 True 2008-5-16
2 22222 False 2008-5-16
3 33333 True 2008-5-16
4 11111 False 2008-5-15
5 22222 True 2008-5-15
6 44444 True 2008-5-2
7 55555 True 2008-5-1
8 55555 False 2008-4-28
....
....
what i'm trying to do is this.... (granted that today's date is 2008-5-16)
i want to get a list of rows where the last SystemID confirmation came back as TRUE but hasn't returned any confirmation for the last 2 days.
so, if you look at the sample data above, i would just get the rows where ID = 6 and 7 because both SystemID's last confirmation came back as TRUE, but haven't sent in any recent confirmation for the last 2 days.
Below is what I have written. The problem I'm having with this is it keeps timing out. I let it run on Query Analyzer and the hour glass just sits there for more than 10 minutes then eventually times out.
Thanks in advance...
what i have is below, but it keeps on timing out...(obviously)
SELECT [SystemID] , 'Maybe' as [Confirmation] , Max(RecordDate) as RecordDate
FROM [tblSystem] a
WHERE SystemID <>
(
SELECT sub.SystemID FROM [tblSystem] sub
WHERE convert(datetime, sub.RecordDate, 101) > getdate()-2
)
AND RecordDate =
(
SELECT Max(b.RecordDate) from tblSystem b
WHERE b.SystemID=a.SystemID and convert(datetime, RecordDate, 101) <= getdate()-2
)
GROUP BY SystemID, Confirmation
HAVING Confirmation = 'True'

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Please... see the URL in my signature...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 2

I guess should work (if you'd follow Jeff's advice I could test it):

SELECT [SystemID] , 'Maybe' as [Confirmation] , MAX(convert(datetime, RecordDate, 101)) as RecordDate

FROM [tblSystem] a

WHERE Confirmation = 'True'

GROUP BY SystemID

HAVING MAX(convert(datetime, RecordDate, 101)) <= getdate()-2

And storing dates as datetime, not as string, could not only simplify your queries but also bring you reputation of a smart guy.

_____________
Code for TallyGenerator

raja_saminathan SSCommitted Points: 1607 More actions · Answer 3

Hi,

This code will Help you

SELECT SystemID , Confirmation, MAX (RecordDate) as RecordDate

FROM TimeTable

WHERE Confirmation = 'True' and CameraID not in (select SystemID from TimeTable where recordDate >=(getdate()-2))

GROUP BY SystemID,confirmation

Regards,

Rajesh

ebankaccount SSC Rookie Points: 26 More actions · Answer 4

Thank you all.

I guess I made my query much more complicated than it really was. I ran both queries from Raja and Sergiy and they both return the same result set.

Thanks again.

plund Newbie Points: 5 More actions · Answer 5

You do not need a subquery at all. Just group, use having and where clauses