September 19, 2018 at 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?
September 19, 2018 at 1:06 pm
richard.english - Wednesday, September 19, 2018 12:56 PMI'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.
September 19, 2018 at 1:27 pm
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/
September 20, 2018 at 5:59 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy