How to get Sum and put condition in SQL Server database?

Question

I have two tables, Timesheet and Overtime

Timesheet :

Card_No Name    1   2   3   4   5   TotalA
123 GGG A   H   P   F   P   1
123 GGG A   H   A   F   P   2
123 GGG A   P   P   F   P   1
123 GGG A   A   A   F   P   3

Overtime:

Card_No Name    1   2   3   4   5
123 GGG 2   2   2   12  2
123 GGG 0   2   0   12  2
123 GGG 0   3   2   12  2
123 GGG 0   0   0   12  2

Now I want to total over time in two columns Normal Overtime and Holiday overtime

• if in Timesheet column 4 is F (Friday) and column 2 is H (Holiday) then overtime of column 4 and column 2 should be added to holiday overtime

• if column 1 is A (absence) then overtime table column1 does not allow to put in any overtime.

Result should be like this

Card_No Name    1   2   3   4   5   Normal OT   Holiday OT
123 GGG it’s A  2   2   12  2   4   14
123 GGG 0   2   0   12  2   2   14
123 GGG 0   3   2   12  2   4   14
123 GGG 0   0   0   12  2   2   14

How can I do this? Please help me .

Thank you

Answer 1

While I don't have time to write a full script, you could use a sub-query with a CASE expression to get the desired results.

Basically like so:

SELECT * FROM 
  (SELECT *, CASE 2 + 1 
    WHEN 'FH' THEN '1'
    ELSE '0'
  END AS 'IsHoliday'
  FROM TimeSheet) SRC
--JOIN OverTime OT ON OT.Card_No = SRC.Card_No AND SRC.IsHoliday = '1'

Then join the result of the sub-query to OverTime table with an added condition of IsHoliday = 1, where you can then make the necessary calculations and insert / update etc based on the results.

Applying a similar logic, you can also filter out the absences from these overtimes. I'll have to leave some of the work in your hands for not having the time to do this myself, but that should hopefully give you an idea of one way to approach the solution. :) I haven't checked the syntax on the query above, but it should be viable.