CODEWITHSUNDEEP
sqlsql-server
user2510547
user2510547

Reputation: 69

adding alias name in where condition of sql query

I have a query like this

SELECT
  employee_tbl.emp_maxid,
  emp_name AS 'Employee Name',
  Designation_tbl.Des_Name AS Designation,
  emp_LabourID,
  emp_IBAN,
  emp_monthlysalary AS Salary,
  0 AS commission,
  ISNULL(emp_monthlysalary - sum(S.Paid), emp_monthlysalary) AS Total
FROM dbo.employee_tbl
INNER JOIN dbo.Designation_tbl
  ON Designation_tbl.Des_id = employee_tbl.Des_id
LEFT JOIN SalaryProcessLog_tbl S
  ON S.emp_maxid = employee_tbl.emp_maxid
WHERE (emp_deleted = 0
OR emp_deleted IS NULL)
AND employee_tbl.emp_maxid NOT IN (SELECT
  emp_maxid
FROM SalaryProcessLog_tbl
WHERE Balance = 0)
group by employee_tbl.emp_maxid,dbo.employee_tbl.emp_name,Designation_tbl.Des_Name,
employee_tbl.emp_LabourID, emp_IBAN,emp_monthlysalary

my out put getting like thisenter image description here

in my query i want to add filter result by

Where Total <> 0.. how i can do the same?

Any help is very appriciable

Upvotes: 0

Views: 51

Answers (2)

EzLo
EzLo

Reputation: 14189

To filter grouping results you should use HAVING.

SELECT
  employee_tbl.emp_maxid,
  emp_name AS 'Employee Name',
  Designation_tbl.Des_Name AS Designation,
  emp_LabourID,
  emp_IBAN,
  emp_monthlysalary AS Salary,
  0 AS commission,
  ISNULL(emp_monthlysalary - sum(S.Paid), emp_monthlysalary) AS Total
FROM dbo.employee_tbl
INNER JOIN dbo.Designation_tbl
  ON Designation_tbl.Des_id = employee_tbl.Des_id
LEFT JOIN SalaryProcessLog_tbl S
  ON S.emp_maxid = employee_tbl.emp_maxid
WHERE (emp_deleted = 0
OR emp_deleted IS NULL)
AND employee_tbl.emp_maxid NOT IN (SELECT
      emp_maxid
    FROM SalaryProcessLog_tbl
    WHERE Balance = 0)
group by employee_tbl.emp_maxid,dbo.employee_tbl.emp_name,Designation_tbl.Des_Name,
employee_tbl.emp_LabourID, emp_IBAN,emp_monthlysalary

HAVING
    ISNULL(emp_monthlysalary - sum(S.Paid), emp_monthlysalary) <> 0

Upvotes: 1

jarlh
jarlh

Reputation: 44696

Wrap your query up in a derived table. Add Total condition to outer query's WHERE.

select *
from
(
    SELECT
      employee_tbl.emp_maxid,
      emp_name AS 'Employee Name',
      Designation_tbl.Des_Name AS Designation,
      emp_LabourID,
      emp_IBAN,
      emp_monthlysalary AS Salary,
      0 AS commission,
      ISNULL(emp_monthlysalary - sum(S.Paid), emp_monthlysalary) AS Total
    FROM dbo.employee_tbl
    INNER JOIN dbo.Designation_tbl
      ON Designation_tbl.Des_id = employee_tbl.Des_id
    LEFT JOIN SalaryProcessLog_tbl S
      ON S.emp_maxid = employee_tbl.emp_maxid
    WHERE (emp_deleted = 0
    OR emp_deleted IS NULL)
    AND employee_tbl.emp_maxid NOT IN (SELECT
      emp_maxid
    FROM SalaryProcessLog_tbl
    WHERE Balance = 0)
    group by employee_tbl.emp_maxid,dbo.employee_tbl.emp_name,Designation_tbl.Des_Name,
    employee_tbl.emp_LabourID, emp_IBAN,emp_monthlysalary
) dt
WHERE Total <> 0

Upvotes: 2

Related Questions