T-SQL filter question

  • richard.english

    Grasshopper

    Points: 21

    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?

  • Lynn Pettis

    SSC Guru

    Points: 442094

    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: 25632

    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

    Grasshopper

    Points: 21

    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 4 (of 4 total)

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