Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Proplem Put Filter ("And" or "Or") in Query Expand / Collapse
Author
Message
Posted Monday, September 2, 2013 6:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 10:36 PM
Points: 12, Visits: 12
Hi Test This Query
Select * From
(
SELECT FirstName
, LastName
, CompanyID
, NCode
, FatherName
, nPercent
, NationalCode
, (select COUNT (*) From Tools_Information K Where K.NationalCode = R.NationalCode And K.NameTools = N'Cane')COUNT_cane
, (select COUNT (*) From Tools_Information K Where K.NationalCode = R.NationalCode And K.NameTools = N'Walker')COUNT_walker
FROM View_Report R
GROUP BY FirstName
, LastName
, CompanyID
, NCode
, FatherName
, nPercent
, NationalCode
)V
Where (Isnull(V.COUNT_cane , 0) > 0 And Isnull(V.COUNT_walker,0) > 0)
Post #1490608
Posted Monday, September 2, 2013 7:33 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:04 AM
Points: 2,449, Visits: 2,992
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’! **
Post #1490621
Posted Thursday, September 5, 2013 5:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 7, 2013 2:46 PM
Points: 23, Visits: 60
excuseme
Contact information table was forgotten
Plz Download New Database Attachment

SELECT     FirstName, LastName, CompanyID, NCode, FatherName, nPercent, NationalCode
FROM View_Report
WHERE (NameTools = N'Walker') OR
(NameTools = N'Cane')
GROUP BY FirstName, LastName, CompanyID, NCode, FatherName, nPercent, NationalCode
HAVING (COUNT(NameTools) = 2)

I want someone who uses both(Walker and Cane ) to exhibit = Mike
But it does not display anything.


  Post Attachments 
BackupDatabaseSampleSql2008.rar (2 views, 111.34 KB)
Post #1491717
Posted Thursday, September 5, 2013 5:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:04 AM
Points: 2,449, Visits: 2,992
You need to change the HAVING clause to get your desired results. With the code below you can check if both 'Walker' and 'Cane' exists at least once.
HAVING sum(case when NameTools = N'walker' then 1 else 0 end) > 0
AND sum(case when NameTools = N'Cane' then 1 else 0 end) > 0



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1491728
Posted Thursday, September 5, 2013 5:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 7, 2013 2:46 PM
Points: 23, Visits: 60
HanShi (9/5/2013)
You need to change the HAVING clause to get your desired results. With the code below you can check if both 'Walker' and 'Cane' exists at least once.
HAVING sum(case when NameTools = N'walker' then 1 else 0 end) > 0
AND sum(case when NameTools = N'Cane' then 1 else 0 end) > 0



Thank you so much master
Post #1491734
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse