CODEWITHSUNDEEP
sql
Multipass
Multipass

Reputation: 29

same branch same quarter number calculation

What I need to add is when employee title X switches to a different title (Y or Z) within the SAME branch, to modify their Total FTE for the time in the quarter that they were in that new title (Y or Z).

So the logic is:

For position X to Y

IF [Position Start Date] is between [Q2 start date] AND [Q2 end date]
and [Employee Title] of [Employee ID] was X and is now Y
THEN [Total FTE calculated] = (((([Position Start Date] - [Quarter Start Date])/[Days in Quarter]) * [Total FTE]) + ((([Quarter End Date] - [Position Start Date])/[Days in Quarter]) * [Total FTE]) * 0.5)

For position X to Z

AND IF [Position Start Date] is between [Q2 start date] AND [Q2 end date]
AND [Employee Title] of [Employee ID] was X and is now Z
THEN [Total FTE calculated] = ([Position Start Date] - [Quarter Start Date])/[Days in Quarter]) * [Total FTE])

ELSE [Total FTE Calculated] = [Total FTE] * 1
END

As you can see I'm missing knowledge of some of the SQL operators to do this properly. Any help you can provide would be greatly appreciated! Please and thank you!

Upvotes: 0

Views: 31

Answers (1)

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7181

A rough sketch, date arithmetic differs a lot between different vendors of DBMS so I used your expressions. I've used the variable :new_emplyee_title for the new title. 

update Employees
    set [Total FTE calculated] = case when :new_emplyee_title = Y 
                                      then [ expression for Y ]
                                      else [ expression for Z ] 
                                 end
where [Position Start Date] between [Q2 start date] AND [Q2 end date]
  and [Employee Title] = X
  and :new_emplyee_title in (Y,Z);

If you DBMS support triggers, this would fit like hand in glove in an update trigger.

Upvotes: 2

Related Questions