CODEWITHSUNDEEP
sqlsql-server
user14743092
user14743092

Reputation:

How can I get count of sql query using WHERE clause ( SQL Server )

I have this query that can return the leaveID , Id and day .

select DAY,employeeId,(select lv.EmployeeId 
from employee_Leaves lv
where Date between lv.start_date and lv.end_date and lv.EmployeeId=c.employeeId and c.isActive=1) as Leave
from employee_c c,holiday hl,Get_Calendar_Date(DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST('2021-01-01' AS datetime))+2, 0) ,DATEADD(MONTH, DATEDIFF(MONTH, -1, CAST('2021-01-01' AS datetime))+2, -1)) 
where DATENAME(DD, Weekday) IN (select dayId+1 from days) and Date not between hl.startDate and hl.endDate and c.isActive=1 order by employeeID,DAY

the output of this query is something like this:

enter image description here

What I'm searching for is , return the count of the leaveID ( where LeaveID is null ) depending on the employee.

I suppose that the count of leaveID for employee 1 that have NULL values is 19 and 25 for the employee 2.

So , the result expected should be something like this :

employeeId    leavecount

    1             19
    2             25

I have tried this query , but it returns the count of all leaves for the all employees , what I need is the leavecount for every employee.

    select count(*) from (select (select lv.EmployeeId from employee_Leaves lv where Date between lv.start_date and lv.end_date and lv.EmployeeId=c.employeeId and c.isActive=1) as leaveID from employee_c c,holiday hl,Get_Calendar_Date(DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST('2021-01-01' AS datetime))+1, 0) ,DATEADD(MONTH, DATEDIFF(MONTH, -1, CAST('2021-01-01' AS datetime))+1, -1))
where DATENAME(DD, Weekday) IN (select dayId+1 from days) and Date not between hl.startDate and hl.endDate and c.isActive=1 ) sc

How can I do that?

Upvotes: 0

Views: 40

Answers (2)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15905

You can use group by on your result to have your desired output:

select employeeid , count(*) from 
(
    select DAY,employeeId,(select lv.EmployeeId 
    from employee_Leaves lv
    where Date between lv.start_date and lv.end_date and lv.EmployeeId=c.employeeId and c.isActive=1) as Leave
    from employee_c c,holiday hl,Get_Calendar_Date(DATEADD(MONTH, DATEDIFF(MONTH, 0, CAST('2021-01-01' AS datetime))+2, 0) ,DATEADD(MONTH, DATEDIFF(MONTH, -1, CAST('2021-01-01' AS datetime))+2, -1)) 
    where DATENAME(DD, Weekday) IN (select dayId+1 from days) and Date not between hl.startDate and hl.endDate and c.isActive=1 
) t
where leave is null
group by employeeid

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Just use conditional aggregation:

select employeeId,
       sum(case when leaveID is null then 1 else 0 end)
from employee_leave el
group by employeeId;

Upvotes: 1

Related Questions