Track Leave Days - how many left

Transact-SQL
1

I have a table that tracks leave days for each user:

   ID   |  Start      |       End     |    IDUser
    1   | 02-02-2020  |   03-02-2020  |       2
    2   | 01-02-2020  |   21-02-2020  |       2

IDUser connects to the Users Table, that has IDUser and Username columns

I have a view / exhibition / query that shows the previous mentioned data PLUS a column named UsedDays that counts how many leave days were used:

DATEDIFF(DAY, dbo.leavedays.start, dbo.leavedays.[end]) + 1

This is what I have now:

   Start      |       End     |  IDUser  | UsedDays
 02-02-2020   |   03-02-2020  |     2    |     1     
 01-02-2020   |   21-02-2020  |     1    |     20

Each user has a total available number of days per year so I would like to have a column that subtracts from those total possible days of each user, and show how many are left. Example:

John (IDUser = 2) has 30 days available this year and he already used 1, so there are 29 left

   Start      |       End     |  IDUser  | TotalDaysYear | UsedDays | LeftDays
 02-02-2020   |   03-02-2020  |     2    |      30       |     1    |    29
 01-02-2020   |   21-02-2020  |     1    |      20       |     20   |     0

I believe I have to create a table for TotalDaysYear, probably with:

   ID   |  Year   |  TotalDaysYear | IDUser
    1   |  2020   |       30       |    2
    2   |  2020   |       20       |    1

IDUser connects to the Users Table, that has IDUser and Username columns

But I'm having trouble finding the logic for the relationship and how to find the result that I want, since it depends also on the year (available days may change per year, per user).
Can anyone give me a hand, please?
Thanks in advance

2

A derived column
Using cte

Maybe enough

Please see below link

https://www.google.com/url?sa=t&source=web&rct=j&url=https://stackoverflow.com/questions/19185043/how-to-use-a-calculated-column-to-calculate-another-column-in-the-same-view/19185374&ved=2ahUKEwiN-vO_14joAhVtzzgGHQSMDNwQFjAUegQICRAB&usg=AOvVaw0bCkqW2-i_Vpfm_j65enYR

1 Like
3

join leavedays to TotalDaysYear by

select *
  from leavedays  ld
   left join TotalDaysYear tdy on year(ld.Start) = tdy.Year

But this will need some good testing. For example what if user has no entry in TotalDaysYear
performance issues on this year(ld.Start)

1 Like
4

Haven't made multiple tests but for now it works!
I've tested with users having already available days for the year inserted and with others where I didn't had inserted them and for what we need, it works so far.
So, thank you!