SubQuery from Where Clause 1

[Anddmx]

Hello,

I am haven bit of problem trying to get this subquery to work, what I want to do is filter out data if exist in another table with today date as timestamp.

I just need some help making sure this is proper way to do subquery.

SELECT reservationID FROM ResTans WHERE ReservationID NOT IN ( Select reservationID from FaxTransactions Where CONVERT(Char (10), SubmitDate, 101) <> CONVERT(Char (10), GetDate(), 101))



Thanks



Thanks

 

[AlexCuse]

I think you should try this using a join rather than NOT IN. You can accomplish this using a left join, and taking only the rows without a match on the right side (in this case your subquery from FaxTransactions).

like so:

[COLOR=blue]SELECT[/color] a.reservationID 
[COLOR=blue]FROM[/color] ResTrans a
[COLOR=#FF00FF]left[/color] [COLOR=blue]join[/color] (
	[COLOR=blue]Select[/color] reservationID [COLOR=blue]from[/color] FaxTransactions
	[COLOR=blue]where[/color] [COLOR=#FF00FF]dateadd[/color]([COLOR=#FF00FF]day[/color], 0, [COLOR=#FF00FF]datediff[/color]([COLOR=#FF00FF]day[/color], 0, SubmitDate)) = 
		[COLOR=#FF00FF]dateadd[/color]([COLOR=#FF00FF]day[/color], 0, [COLOR=#FF00FF]datediff[/color]([COLOR=#FF00FF]day[/color], 0, [COLOR=#FF00FF]getdate[/color]()))
) b
[COLOR=blue]on[/color] a.reservationID = b.reservationID
[COLOR=blue]where[/color] b.reservationID [COLOR=blue]is[/color] null

the dateadd thing is doing the same as CONVERT in your code, I am sure there are about a billion ways to remove the time portion from a date. I just really don't like convert for it.

Hope this helps,

Alex


[small]----signature below----[/small]
I can't compete with you physically, and you're no match for my brains.
You're that smart?
Let me put it this way. Have you ever heard of Plato, Aristotle, Socrates?
Yes.
Morons!

 

[ESquared]

For what it's worth, if performance is SUPER crucial and you have a LOT of reservations in FaxTransactions each day, you might also try this to see if it helps at all:

    Select reservationID from FaxTransactions
    where
        SubmitDate >= dateadd(day, 0, datediff(day, 0, getdate())) =
        and SubmitDate < dateadd(day, 0, datediff(day, 0, getdate())) + 1

An index on the SubmitDate column would make this make even more difference, but the total improvement could be minuscule.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]

 

[Anddmx]

Thanks for all help!

I used left join to check and see if reservation exist for today's date.

Thanks again.