For LOOP in SQL Server

Question

Total EmpId in Database = 74. Active Days for September 2013 in Databse = 22 I want to segregate the dates when the employee was not filled the production in MIS_Opus table.

Since FULL OUTER Join was not worked, I m using the except query. Through this query, I can get the unfilled dates for each employee by passing the empid thru c# function. For that loop will going to SQL & back to C# for 74 times. Please let me know how can I get the unfilled dates for all employees at one shot in SQL itself. I m using SQL 2012 and VS 2012 c#.

Thank you,

select _Date from MIS_BM_Calendar c
where c.Month = 'September 2013'  and c.DayShiftStatus = 'active'
except
select _Date from MIS_Opus o
where o.EmpId=@Empid

We don't write for loops in SQL - and we try to avoid writing loops at all. SQL is (generally) a declarative, set-based language - you tell the system what the entire result set should look like, and it works out whether to build up that result set using loops, hash tables, etc. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Oct 22, 2013 at 8:25

Andomar · Accepted Answer · 2013-10-22 08:21:46Z

One way is to build a list of all employee + day combinations using a cross join. Then you can use a left join to check if there is an employee entry for that day.

select  days._Date as TheDay
,       emps.EmpId as EmployeeWithMissingEntry
from    (
        select  distinct _Date
        from    MIS_BM_Calendar
        where   Month = 'September 2013'
                and DayShiftStatus = 'active'
        ) days
cross join -- One row for each combination of employee and date
        (
        select  distinct EmpId
        from    MIS_Opus
        ) emps
left join
        MIS_Opus o
on      o._Date = days._Date
        and o.EmpId = emps.EmpId
where   o._Date is null -- Employee entry for day not found

Thank you very much Andomar. This query shows me a very different attitude how to think programmatically.

Collectives™ on Stack Overflow

For LOOP in SQL Server

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related