Column value to be computed

Ramco Ramco

3y811SQL

In c# i have value computed like below . But i want at Sql column level. How it can be done. Sql column name is Attendance. If all records have present = false then Attendance column should have value 1.

var result = objList.GroupBy(x => x.CalendarID)
             .Count(g => g.All(x => x.Present == false));

Thanks

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty3y ago

Accepted answer

UPDATE YourTable
SET Attendance = CASE
    WHEN (
        SELECT COUNT(*) 
        FROM YourTable AS sub 
        WHERE sub.CalendarID = YourTable.CalendarID 
        AND sub.Present = 0
    ) = COUNT(*) THEN 1
    ELSE 0
END;

Deepak Rawat3y ago

UPDATE YourTable
SET Attendance = CASE
    WHEN (
        SELECT COUNT(*) FROM YourTable t
        WHERE t.CalendarID = YourTable.CalendarID AND t.Present = 0
    ) = (
        SELECT COUNT(*) FROM YourTable t2
        WHERE t2.CalendarID = YourTable.CalendarID
    ) THEN 1
    ELSE 0
END;