Home Forums SQL Server 2008 T-SQL (SS2K8) Proplem Put Filter ("And" or "Or") in Query RE: Proplem Put Filter ("And" or "Or") in Query

  • September 2, 2013 at 7:33 am

    #1646749

    Instead of selecting the specific counts by a sub-select you could also accomplish this by using the CASE statement. This will give better performance because it will be calculated within the same select on the table and not by executing another select on the table.

    SELECT

    NationalCode, CompanyID, nPercent, NCode, FirstName, LastName, FatherName

    , sum(case when NameTools = N'walker' then 1 else 0 end) as Count_walker

    , sum(case when NameTools = N'Cane' then 1 else 0 end) as Count_Cane

    FROM View_Report

    WHERE (NameTools = N'walker') OR

    (NameTools = N'Cane')

    GROUP BY NationalCode, CompanyID, nPercent, NCode, FirstName, LastName, FatherName

    HAVING (COUNT(NameTools) > 1)

    Also notice the change in the HAVING clause to accomidate the posibility of having multiple Canes and/or Walkers.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **