CODEWITHSUNDEEP
sqlsql-server
user3262364
user3262364

Reputation: 371

filter sql query with case columns in sql server

i have a query like this :

SELECT
  tbl.EmpID,
  CASE
    WHEN emp.status = 'terminated' THEN CONVERT(bit, 1)
    ELSE CONVERT(bit, 0)
  END AS 'Status',
  CASE
    WHEN tbl.LeaveTypeId = 'a' THEN CONVERT(bit, 1)
    ELSE CONVERT(bit, 0)
  END AS 'Absent',
  CASE
    WHEN (tbl.outime IS NULL) AND
      (tbl.LeaveTypeId <> 'a' and tbl.LeaveTypeId Is  null) THEN CONVERT(bit, 1)
    ELSE CONVERT(bit, 0)
  END AS 'InOut',
  emp.EmpFullName,
  tbl.P_Date,
  cmp.cmplogo,
  emp.nation_id,
  emp.dept_id,
  emp.desig_id AS 'designation_id',
  emp.tradecd,
  emp.comp_id,
  emp.section_id,
  emp.location_id,
  emp.emplcatgry
FROM tblattendance tbl
INNER JOIN EMPLOYEEs emp
  ON emp.empid = tbl.empid
INNER JOIN Company cmp
  ON cmp.comp_id = emp.comp_id 
WHERE CONVERT(varchar(10), tbl.p_date, 112) BETWEEN '20160901' AND '20160902'

my out put coming like this

empid status absent inout
121     1     1      1
122     0      0      0

but only i want to get result with status,absent,inout greater than 0,so how i can write where condition.

Upvotes: 1

Views: 49

Answers (3)

knuckles
knuckles

Reputation: 398

Try CROSS APPLY with a VALUES statement, this allows you to reuse the values in subsequent joins, WHERE clause, etc.

e.g.

SELECT
  tbl.EmpID,
  v.Status,
  v.Absent,
  v.InOut,
  emp.EmpFullName,
  tbl.P_Date,
  cmp.cmplogo,
  emp.nation_id,
  emp.dept_id,
  emp.desig_id AS 'designation_id',
  emp.tradecd,
  emp.comp_id,
  emp.section_id,
  emp.location_id,
  emp.emplcatgry
FROM tblattendance tbl
INNER JOIN EMPLOYEEs emp
  ON emp.empid = tbl.empid
INNER JOIN Company cmp
  ON cmp.comp_id = emp.comp_id 
CROSS APPLY (
  VALUES (
  CASE
    WHEN emp.status = 'terminated' THEN CONVERT(bit, 1)
    ELSE CONVERT(bit, 0)
  END,
  CASE
    WHEN tbl.LeaveTypeId = 'a' THEN CONVERT(bit, 1)
    ELSE CONVERT(bit, 0)
  END,
  CASE
    WHEN (tbl.outime IS NULL) AND
      (tbl.LeaveTypeId <> 'a' and tbl.LeaveTypeId Is  null) THEN CONVERT(bit, 1)
    ELSE CONVERT(bit, 0)
  END
  )
) v (Status, Absent, InOut)
WHERE CONVERT(varchar(10), tbl.p_date, 112) BETWEEN '20160901' AND '20160902'
  AND (v.Status | v.Absent | v.InOut) = 1;

Once you have defined v, you can use it like any other table in the query. I personally find these simpler to use than a CTE and more readable than a subquery. You can define expressions that depend on tables above the CROSS APPLY and then use the values in any joins below, WHERE clause, GROUP BY etc.

Also, note the use of |, the bitwise or operator, to return 1 if any of the BIT values = 1.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You could just add a condition like this:

WHERE . . . AND
      (emp.status = 'terminated' or tbl.LeaveTypeId = 'a' or
       (tbl.outime IS NULL AND (tbl.LeaveTypeId <> 'a' and tbl.LeaveTypeId Is  null)
      )

Alternatively, you could use a subquery or CTE to refer to the columns in the SELECT.

EDIT:

The CTE looks like

with cte as (<your query here>)
select cte.*
from cte
where status = 1 or absent = 1 or inout = 1;

Upvotes: 0

Ritesh Patel
Ritesh Patel

Reputation: 315

SELECT S.* FROM ( SELECT   tbl.EmpID,   CASE
     WHEN emp.status = 'terminated' THEN CONVERT(bit, 1)
     ELSE CONVERT(bit, 0)   END AS 'Status',   CASE
     WHEN tbl.LeaveTypeId = 'a' THEN CONVERT(bit, 1)
     ELSE CONVERT(bit, 0)   END AS 'Absent',   CASE
     WHEN (tbl.outime IS NULL) AND
       (tbl.LeaveTypeId <> 'a' and tbl.LeaveTypeId Is  null) THEN CONVERT(bit, 1)
     ELSE CONVERT(bit, 0)   END AS 'InOut',   emp.EmpFullName,   tbl.P_Date,   cmp.cmplogo,   emp.nation_id,   emp.dept_id,  
 emp.desig_id AS 'designation_id',   emp.tradecd,   emp.comp_id,  
 emp.section_id,   emp.location_id,   emp.emplcatgry FROM tblattendance
 tbl INNER JOIN EMPLOYEEs emp   ON emp.empid = tbl.empid INNER JOIN
 Company cmp   ON cmp.comp_id = emp.comp_id  WHERE CONVERT(varchar(10),
 tbl.p_date, 112) BETWEEN '20160901' AND '20160902' )  S WHERE
 S.Status>0  OR S.Absent>0 OR S.InOut>0

Upvotes: 1

Related Questions