merging multiple Exists

• sql_2005_fan

  SSCommitted

  Points: 1871

  More actions

  January 20, 2017 at 9:50 am

  #325769

  Hi I have the following query  with multiple exists run against the same table.

  Can I merger all these exists statements into one?

  Following is the sample code.

  IF EXISTS (
      SELECT TOP 1 EmplyeeNumber
      FROM EmplyeeCodes
      WHERE EmplyeeNumber = @EmplyeeNumber
          AND AmountRemaining > 0
          AND ItemNumber IN('ACCT','ACCT-STR','ACCT-SLR','ACCT-UDCC','ACCT-VLIT')
  )
  BEGIN
      SET @DeptCodes = @DeptCodes + 'ACCTEmplyeeBillpartiallyPAid,'
  END

  IF EXISTS (
      SELECT TOP 1 EmplyeeNumber
      FROM EmplyeeCodes
      WHERE EmplyeeNumber = @EmplyeeNumber
          AND DATEDIFF(DAY, DatePaid, GETDATE()) <= 17
          AND AmountRemaining = 0
          AND TotalAmount > 0
          AND ItemNumber IN('ACCT','ACCT-MBRSHP','ACCT-SLR','ACCT-UDCC','ACCT-STL')
  )
  BEGIN
      SELECT @DeptCodes = @DeptCodes + 'ACCTBillFullyPaid,'
  END

  IF EXISTS (SELECT TOP 1 mo.EmplyeeId
           FROM EmplyeeCodes i
           JOIN ACCT.Emplyeeship.Emplyeedept mo on i.EmplyeeID = mo.EmplyeeID and i.deptNumber = mo.deptID
          
           where mo.deptID = @deptNumber and
                i.AmountRemaining > 0 and
                i.ItemNumber IN('ACCT','ACCT-MBRSHP','ACCT-SLR','ACCT-STL','ACCT-IDR','ACCT-NTR','ACCTN-RDT')
  )
  BEGIN
      SELECT @DeptCodes = @DeptCodes + 'ACCTEmplyeeBillPending,'
  END

  Thanks.

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 20, 2017 at 11:40 am

  #1924124

  sql_2005_fan - Friday, January 20, 2017 9:50 AM

  Hi I have the following query  with multiple exists run against the same table.

  Can I merger all these exists statements into one?

  Following is the sample code.

  IF EXISTS (
      SELECT TOP 1 EmplyeeNumber
      FROM EmplyeeCodes
      WHERE EmplyeeNumber = @EmplyeeNumber
          AND AmountRemaining > 0
          AND ItemNumber IN('ACCT','ACCT-STR','ACCT-SLR','ACCT-UDCC','ACCT-VLIT')
  )
  BEGIN
      SET @DeptCodes = @DeptCodes + 'ACCTEmplyeeBillpartiallyPAid,'
  END

  IF EXISTS (
      SELECT TOP 1 EmplyeeNumber
      FROM EmplyeeCodes
      WHERE EmplyeeNumber = @EmplyeeNumber
          AND DATEDIFF(DAY, DatePaid, GETDATE()) <= 17
          AND AmountRemaining = 0
          AND TotalAmount > 0
          AND ItemNumber IN('ACCT','ACCT-MBRSHP','ACCT-SLR','ACCT-UDCC','ACCT-STL')
  )
  BEGIN
      SELECT @DeptCodes = @DeptCodes + 'ACCTBillFullyPaid,'
  END

  IF EXISTS (SELECT TOP 1 mo.EmplyeeId
           FROM EmplyeeCodes i
           JOIN ACCT.Emplyeeship.Emplyeedept mo on i.EmplyeeID = mo.EmplyeeID and i.deptNumber = mo.deptID
          
           where mo.deptID = @deptNumber and
                i.AmountRemaining > 0 and
                i.ItemNumber IN('ACCT','ACCT-MBRSHP','ACCT-SLR','ACCT-STL','ACCT-IDR','ACCT-NTR','ACCTN-RDT')
  )
  BEGIN
      SELECT @DeptCodes = @DeptCodes + 'ACCTEmplyeeBillPending,'
  END

  Thanks.

  Why? They have different functionality. It seems right for them to be different.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Thom A

  SSC Guru

  Points: 99130

  More actions

  January 20, 2017 at 11:58 am

  #1924127

  The only thing I would suggest is using END ELSE IF BEGIN as, unless I am mistaken, in your logic only one option will ever be valid. This means if a prior IF requirement is met, the others won't be checked.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• salliven

  SSCrazy

  Points: 2644

  More actions

  January 23, 2017 at 5:48 am

  #1924374

  Maybe this one:

  ;WITH cte as (
    SELECT TOP 1 'ACCTEmplyeeBillpartiallyPAid,' as string, 1 as orderby
    FROM EmplyeeCodes
    WHERE EmplyeeNumber = @EmplyeeNumber
    AND AmountRemaining > 0
    AND ItemNumber IN('ACCT','ACCT-STR','ACCT-SLR','ACCT-UDCC','ACCT-VLIT')
    
    union all
    
    SELECT TOP 1 'ACCTBillFullyPaid,', 2 as orderby
    FROM EmplyeeCodes
    WHERE EmplyeeNumber = @EmplyeeNumber
    AND DATEDIFF(DAY, DatePaid, GETDATE()) <= 17
    AND AmountRemaining = 0
    AND TotalAmount > 0
    AND ItemNumber IN('ACCT','ACCT-MBRSHP','ACCT-SLR','ACCT-UDCC','ACCT-STL')
    
    union all
    
    SELECT TOP 1 'ACCTEmplyeeBillPending,', 3 as orderby
    FROM EmplyeeCodes i
    JOIN ACCT.Emplyeeship.Emplyeedept mo on i.EmplyeeID = mo.EmplyeeID and i.deptNumber = mo.deptID
    where mo.deptID = @deptNumber and
    i.AmountRemaining > 0 and
    i.ItemNumber IN('ACCT','ACCT-MBRSHP','ACCT-SLR','ACCT-STL','ACCT-IDR','ACCT-NTR','ACCTN-RDT')
)

  select
    @DeptCodes = @DeptCodes + string
from cte
order by orderby    

• sql_2005_fan

  SSCommitted

  Points: 1871

  More actions

  January 24, 2017 at 4:42 am

  #1924589

  The reason why I wanted to do this is.. I wanted to minimize the usage of the view 'EmplyeeCodes'.
   Each time i used 'exists' this object is queried once... I was thinking if there is way I can check all the condition with one select.

  Unfortunately, I was not able to think a way of doing this.

  Thanks.

• Thom A

  SSC Guru

  Points: 99130

  More actions

  January 24, 2017 at 4:48 am

  #1924592

  sql_2005_fan - Tuesday, January 24, 2017 4:42 AM

  The reason why I wanted to do this is.. I wanted to minimize the usage of the view 'EmplyeeCodes'.
   Each time i used 'exists' this object is queried once... I was thinking if there is way I can check all the condition with one select.

  Unfortunately, I was not able to think a way of doing this.

  Thanks.

  Your statements are all quite different, so 3 does seem the logical choice here. If you are meant to only get one result out of the 3, then using END ELSE IF BEGIN will cut them down.

  Are you saying that your query runs slowly then? Perhaps the problem is not with doing 3 EXiSTS, and that being slow, but that your VIEW is, and that needs a review.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 24, 2017 at 5:03 am

  #1924595

  sql_2005_fan - Tuesday, January 24, 2017 4:42 AM

  The reason why I wanted to do this is.. I wanted to minimize the usage of the view 'EmplyeeCodes'.
   Each time i used 'exists' this object is queried once... I was thinking if there is way I can check all the condition with one select.

  Unfortunately, I was not able to think a way of doing this.

  Thanks.

  You can easily reduce the number of reads...
  

  SELECT

  DatePaid, TotalAmount, AmountRemaining, ItemNumber

  INTO #EmployeeCodes

  FROM EmplyeeCodes

  WHERE EmplyeeNumber = @EmplyeeNumber

  IF EXISTS (

  SELECT 1

  FROM #EmplyeeCodes

  WHERE AmountRemaining > 0

  AND ItemNumber IN ('ACCT','ACCT-STR','ACCT-SLR','ACCT-UDCC','ACCT-VLIT')

  )

  BEGIN

  SET @DeptCodes = @DeptCodes + 'ACCTEmplyeeBillpartiallyPAid,'

  END

  IF EXISTS (

  SELECT 1

  FROM #EmplyeeCodes

  WHERE DATEDIFF(DAY, DatePaid, GETDATE()) <= 17

  AND TotalAmount > 0

  AND AmountRemaining = 0

  AND ItemNumber IN ('ACCT','ACCT-MBRSHP','ACCT-SLR','ACCT-UDCC','ACCT-STL')

  )

  BEGIN

  SELECT @DeptCodes = @DeptCodes + 'ACCTBillFullyPaid,'

  END

   

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden