Sunday January 21st 2018




Real world example of usage of Case statement in SQL Server Stored Procedure

This post is our practical experience of using Case statement in Calculating monthly overtime amount. Issue was actually usage of different no hours for Overtime worked for different departments.

In this case Security departments overtime was calculated based on 12 hours and in all other departments base was 8 hours.

Salary was categorize by monthly type as [monthlytype] SG for Security Guard where ovetime was calculated based on 12 hours.

code is given below and was in included in select statement;

CASE monthlytype WHEN ‘sg’ THEN dbo.wrkshopattndnc.TOTAL_OVERTIME * dbo.[wrkshop employees].Salary / DAY(dbo.wrkshopattndnc.month)/12

ELSE dbo.wrkshopattndnc.TOTAL_OVERTIME * dbo.[wrkshop employees].Salary / DAY(dbo.wrkshopattndnc.month) / 8

END AS ovtamnt