Forgive my ignorance around anything SQL I might say as a novice to the language!
I have a data set created from a simple query and I need to find the capped pay rate between step down rate over a period...so for example....
A shift is worked on a date 01/04/2018 and is worked by a staff member on lets say a grade 4, this shift falls into a period where the rate for a grade 4 member of staff is £45 an hour
Another shift that is worked by the same grade but later in the year...lets say 26/08/2018 and a grade 4 at this period of time is on a capped rate of £42.20.
I need the join to check the date between and the grade and bring back the capped rate, at the moment my left join is duplicating records as it linked to a spread sheet that is updated with the grades and an effective date from.
This is to check rates of pay against the capped rate to ensure there are no breaches and if there are this will report them.
To help the headers on the linked mapping file and dummy data are as follows
[Shift]------[Grade]---[Capped rate]
01/04/18----4----------45
01/07/18----4----------42.50
01/10/18----4----------40.10
and so on
The outcome I am looking for is
[Shift]------[Grade]---[Capped rate]
21/04/18 ----4 ---------45
16/05/18 ----4 ---------45
03/07/18 ----4 ---------42.50
21/10/18 ----4 ---------40.10
Again dummy data applied
Any help with this would be greatly appreciated.
Thanks
Matt
I have a data set created from a simple query and I need to find the capped pay rate between step down rate over a period...so for example....
A shift is worked on a date 01/04/2018 and is worked by a staff member on lets say a grade 4, this shift falls into a period where the rate for a grade 4 member of staff is £45 an hour
Another shift that is worked by the same grade but later in the year...lets say 26/08/2018 and a grade 4 at this period of time is on a capped rate of £42.20.
I need the join to check the date between and the grade and bring back the capped rate, at the moment my left join is duplicating records as it linked to a spread sheet that is updated with the grades and an effective date from.
This is to check rates of pay against the capped rate to ensure there are no breaches and if there are this will report them.
To help the headers on the linked mapping file and dummy data are as follows
[Shift]------[Grade]---[Capped rate]
01/04/18----4----------45
01/07/18----4----------42.50
01/10/18----4----------40.10
and so on
The outcome I am looking for is
[Shift]------[Grade]---[Capped rate]
21/04/18 ----4 ---------45
16/05/18 ----4 ---------45
03/07/18 ----4 ---------42.50
21/10/18 ----4 ---------40.10
Again dummy data applied
Any help with this would be greatly appreciated.
Thanks
Matt
