T-SQL filter question

Question

T-SQL filter question

richard.english

SSC Rookie

Points: 31
More actions
September 19, 2018 at 12:56 pm

#77374

I'm new to T-SQL and I'm guessing this is an easy question.
I'm using a T-SQL query within Access and running it with ADO. I then send the results to Excel.
My problem is that I need to filter the results for a particular state. I put the statement "AND LI.LocState IN ('MN')" in the SQL statement in two locations since it's a UNION query. It's still pulling other states, however.
str = str & "FROM Provider.dbo.T_ProviderInformation AS PRV With(Nolock) "
str = str & "INNER JOIN Provider.dbo.T_ProviderLocationPointer AS LP With(Nolock) ON PRV.ProviderID = LP.ProviderID "
str = str & "INNER JOIN Provider.dbo.T_OfficeLocationInfo AS LI With(Nolock) ON LP.OfficeLocationID = LI.OfficeLocationID "
str = str & "INNER JOIN Provider.dbo.T_ProvLocTinPointer AS LTP With(Nolock) ON LP.ProviderID = LTP.ProviderID AND LP.OfficeLocationID = LTP.OfficeLocationID "
str = str & "INNER JOIN Provider.dbo.T_TinInfo AS TIN With(Nolock) ON LTP.TinNumber = TIN.TinNumber "
str = str & "INNER JOIN Provider.dbo.T_ProviderStatusHistory AS PSH With(Nolock) ON PRV.ProviderID = PSH.ProviderID "
str = str & "WHERE PSH.MainStatus = 'A' "
str = str & "AND PSH.Specialty IN ('DC','PT','OT','SLP') "
str = str & "AND PSH.TermDate IS NULL "
str = str & "AND LP.NonPar = 'N' "
str = str & "AND PRV.ProviderID NOT IN ('111111', '111112', '663910', '693301', '693302', '699619', '999998', '999999') "
str = str & "AND (NOT (LP.EffDate IS NULL)) "
str = str & "AND LP.TermDate IS NULL "
str = str & "AND LTP.TermDate IS NULL "
str = str & "AND LI.LocState IN ('MN') "
Should I be putting the statement somewhere else?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

richard.english - Wednesday, September 19, 2018 12:56 PM
I'm new to T-SQL and I'm guessing this is an easy question.
I'm using a T-SQL query within Access and running it with ADO. I then send the results to Excel.
My problem is that I need to filter the results for a particular state. I put the statement "AND LI.LocState IN ('MN')" in the SQL statement in two locations since it's a UNION query. It's still pulling other states, however.
str = str & "FROM Provider.dbo.T_ProviderInformation AS PRV With(Nolock) "
str = str & "INNER JOIN Provider.dbo.T_ProviderLocationPointer AS LP With(Nolock) ON PRV.ProviderID = LP.ProviderID "
str = str & "INNER JOIN Provider.dbo.T_OfficeLocationInfo AS LI With(Nolock) ON LP.OfficeLocationID = LI.OfficeLocationID "
str = str & "INNER JOIN Provider.dbo.T_ProvLocTinPointer AS LTP With(Nolock) ON LP.ProviderID = LTP.ProviderID AND LP.OfficeLocationID = LTP.OfficeLocationID "
str = str & "INNER JOIN Provider.dbo.T_TinInfo AS TIN With(Nolock) ON LTP.TinNumber = TIN.TinNumber "
str = str & "INNER JOIN Provider.dbo.T_ProviderStatusHistory AS PSH With(Nolock) ON PRV.ProviderID = PSH.ProviderID "
str = str & "WHERE PSH.MainStatus = 'A' "
str = str & "AND PSH.Specialty IN ('DC','PT','OT','SLP') "
str = str & "AND PSH.TermDate IS NULL "
str = str & "AND LP.NonPar = 'N' "
str = str & "AND PRV.ProviderID NOT IN ('111111', '111112', '663910', '693301', '693302', '699619', '999998', '999999') "
str = str & "AND (NOT (LP.EffDate IS NULL)) "
str = str & "AND LP.TermDate IS NULL "
str = str & "AND LTP.TermDate IS NULL "
str = str & "AND LI.LocState IN ('MN') "
Should I be putting the statement somewhere else?

Really need to see the full query, preferably not as a concatenation of strings.

Michael L John One Orange Chip Points: 27038 More actions · Answer 2

This looks like a place to start
str = str & "AND (NOT (LP.EffDate IS NULL)) "
How about
AND LP.EffDate IS NOT NULL

And, why are you using NOLOCK??? If you are beginner, that's a really bad thing to learn first.

Like Lynn said, without the entire SQL statement, it's guessing.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

richard.english SSC Rookie Points: 31 More actions · Answer 3

Thanks for replying.

I actually didn't compose the entire statement. I'm just modifying it for my particular purpose.

As it turns out, I was using the wrong statement and that's why my modification wasn't working.

Thanks to all for your attention and responses.