Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
You shouldn't cross-post on Stackoverflow and here, too. Also: this is really more of a programming-related question and thus it's better on Stackoverflow but really a bit off-topic here... –  marc_s Jan 6 '13 at 10:15
    
Sadly, the reality is that in a lot of projects a simple employee has no control over the decision when they're asked to do programming logic in the DB-side, as opposed to handling it in the software's codebehind. And programming logic is an integral part of what makes TSQL so valuable, in my opinion. :) –  Kahn Jan 7 '13 at 13:53

1 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.