views:

168

answers:

5

A UI (before the report shows) shows a look up (Combo) that has

  • (ID = 0).All Organization Units
  • (ID =4).HR
  • (ID = 5).DEV

I need to:

  1. Be able to show data of (4) + (5) if (0) is selected.
  2. Only (4) OR (5) if either HR or DEV is selected.


Lookup combo code (Selected Feeds the parameter in the below query.)


Select 0 AS ID,'All Org' AS Name from  DP_ORG_OrganizationUnit
where DP_ORG_OrganizationUnit.Code IN {AccessData}
Union
SELECT 
DP_ORG_OrganizationUnit.ID,
DP_ORG_OrganizationUnit.Name
FROM DP_ORG_OrganizationUnit  where DP_ORG_OrganizationUnit.Code IN ('HR','DEV')



Report data row query


SET CONCAT_NULL_YIELDS_NULL OFF

DECLARE @EmpID as int; 
DECLARE @OrganizationUnit as int; 
DECLARE @StartDate as datetime;
DECLARE @EndDate as datetime;

SET @EmpID = ?;
SET @StartDate = ?;
SET @EndDate = ?;
SET @OrganizationUnit = ?;

SELECT
Employee.Code,
Employee.Name1+' '+Employee.Name2+' '+Employee.Name3+' '+Employee.Name4+' '+Employee.Name5 AS FullName,
Employee.OrganizationUnit,  
ContractType.Name,
EmployeeContract.StartDate,
EmployeeContract.EndDate
FROM Employee INNER JOIN (ContractType INNER JOIN EmployeeContract 
ON ContractType.ID = EmployeeContract.ContractType) 
ON Employee.ID = EmployeeContract.Employee
WHERE (Employee.ID = @EmpID  OR  @EmpID=0)
AND
(Employee.OrganizationUnit = @OrganizationUnit  OR  @OrganizationUnit=0)
AND  NOT((EndDate <  @StartDate or StartDate > @EndDate));


Any way I can achieve it from the looks of it? 0=0 would show all the data from other departments too..

Anybody :-o?

A: 

something like this should work

Where (Employee.OrganizationUnit = case when @OrganizationUnit=0 then 4 else @OrganizationUnit end OR case when @OrganizationUnit=0 then 5 else @OrganizationUnit end)
Booji Boy
A: 

How about

WHERE (Employee.ID = @EmpID  OR  @EmpID=0)
AND
(Employee.OrganizationUnit BETWEEN ISNULL(NULLIF(@OrganizationUnit,0),0) AND ISNULL(NULLIF(@OrganizationUnit,0),99))
AND  NOT((EndDate <  @StartDate or StartDate > @EndDate));
Ed Harper
Incorrect syntax near the keyword 'BETWEEN'.
abmv
Syntax corrected
Ed Harper
A: 

Try this, which should use indexes on your query...

DECALRE @FilterValues (FilterValue   int not null primary key)

IF @Param=0
BEGIN
    INSERT INTO @FilterValues VALUES (4)
    INSERT INTO @FilterValues VALUES (5)
END
ELSE ID @PAram IS NOT NULL
BEGIN
    INSERT INTO @FilterValues VALUES (@Param)
END

SELECT
    ....
    FROM YourTable                y
        INNER JOIN @FilterValues  f ON y.Value=f.Value
    WHERE .....
KM
+2  A: 

First off, your lookup combo code could be tightened up a bit:

-- the FROM clause was superfluous
SELECT 0 AS ID,'All Org' AS Name 
UNION ALL
-- the two-part identifiers were superfluous (only one table)
SELECT ID, Name
FROM DP_ORG_OrganizationUnit
WHERE Code IN ('HR','DEV')

For the report query, the simplest form would be:

WHERE 
  ((@OrganizationUnit > 0 AND Employee.OrganizationUnit = @OrganizationUnit) OR 
   (@OrganizationUnit = 0 AND Employee.OrganizationUnit IN (4,5)))
Peter
A: 

KM's version will work, but this query does not need a temp table...

SELECT *
FROM Employee
WHERE (
         @OrganizationUnit = 0
         OR 
         (
             @OrganizationUnit <> 0
             AND
             Employee.OrganizationUnit = @OrganizationUnit
         )
      )
Jon
do that on a million row table and you'll wish you had a temp table while you scan the whole thing!
KM
i want to believe you, but can you show me why the temp table is better? I tested this on a 500k row table, my way has shown to be about 5% to 10% faster. Maybe as the # of rows gets larger, mine become more inefficient? Im really curious as to which is better.The query plan on mine says: 1. Select Cost 2%2. Filter Cost 18%3. Index Scan Cost 80%Your query plan says: 1. Select Cost 0%2. Hash Match (Inner Join) Cost 53%3a. Clustered Index Scan @FilterValues Cost 1%3b. Index Scan Cost 46%
Jon
Your version will never use an index, only scan. for my version, it will depend on how many unique values are in the Employee.OrganizationUnit column (and there is an index on it), if there are not many different values relative to rows in the table it will scan. however, if there are many different values relative to number of rows (a good index), it will use that index. In your example it is not a good index and did a scan.
KM