September 27, 2011 at 3:01 am
Hi All,
I'm having a problem constructing a SQL Server 2008 stored procedure and any help would be gratefully received...
A bit of background info...
I have a list of contractors doing jobs. Each contractor has one or more master linked rate bands which form a guide as to which rates to use according to a job they have undertaken's start and end date\time.
If a Job has a Start Date and Time and an End Date and Time (which may span more than 24 hours) I need to split the hours worked up over the different bands.
Data is structured as follows...
tblContractor
intContractorID int (auto inc)
strName
tblMasterRateBand
intRateID int (auto inc)
intContractorID int
intDayType int (1 - 7 is Monday through Sunday, 8 is Holiday, 9 is Weekend, 0 is normal weekday)
dtStartDateTime DateTime (Value stored has a date and time, date is set to 1900-01-01 as it's not used, only the time part is required).
dtEndDateTime DateTime (Value stored has a date and time, date is set to 1900-01-01 as it's not used, only the time part is required).
monRate Money
tblJob
intJobID int (auto inc)
intClientID int
dtStart DateTime
dtEnd DateTime
decHoursElapsed decimal(18,2)
tblJobRate
intJobRateID (auto inc)
intJobID int
intDayType int
StartDateTime DateTime
EndDateTime DateTime
decHoursElapsed decimal(18,2)
monRate Money
tblHoliday
intHolidayID int (auto inc)
intContractorID int
dtHolidayDate DateTime
What I need to do when I add a job is take a copy of the master rates for the contractor and insert them into the tblJobRate table then take the job start and end date\time and split the elapsed hours between them over the records in the tblJobRate table.
What I need to do is look at the the Day or Days the job spans (a job could start one evening and continue into the next morning) to see their type is e.g. is it a holiday for the contractor - if yes then I need to look at any holiday rate bands - if there are none then I need to look at are they at the weekend if yes and the team has rate bands for weekend then use those, if not then is it a specific day of the week (eg Sunday) then check and use the Sunday rate bands if there are any. Failing that then use normal weekday rate bands.
A sample of master rate bands is below...
intRateID, intContractorID, intDayType, dtStartDateTime, dtEndDateTime, monRate
1, 1, 8, 1900-01-01 00:00:00, 1900-01-01 06:00:00, 24.00
2, 1, 8, 1900-01-01 06:00:00, 1900-01-01 10:00:00, 22.00
2, 1, 8, 1900-01-01 10:00:00, 1900-01-01 00:00:00, 27.75
2, 1, 9, 1900-01-01 00:00:00, 1900-01-01 00:00:00, 19.95 (00:00:00 - 00:00:00 spans full 24 hour period)
2, 1, 1, 1900-01-01 00:00:00, 1900-01-01 00:00:00, 18.95
2, 1, 0, 1900-01-01 00:00:00, 1900-01-01 06:00:00, 20.50
2, 1, 0, 1900-01-01 06:00:00, 1900-01-01 00:00:00, 16.50
Can anyone help with this?
Many Thanks
Charlotte CB
September 27, 2011 at 3:43 am
Charlottecb (9/27/2011)
Hi All,I'm having a problem constructing a SQL Server 2008 stored procedure and any help would be gratefully received...
A bit of background info...
I have a list of contractors doing jobs. Each contractor has one or more master linked rate bands which form a guide as to which rates to use according to a job they have undertaken's start and end date\time.
If a Job has a Start Date and Time and an End Date and Time (which may span more than 24 hours) I need to split the hours worked up over the different bands.
Data is structured as follows...
tblContractor
intContractorID int (auto inc)
strName
tblMasterRateBand
intRateID int (auto inc)
intContractorID int
intDayType int (1 - 7 is Monday through Sunday, 8 is Holiday, 9 is Weekend, 0 is normal weekday)
dtStartDateTime DateTime (Value stored has a date and time, date is set to 1900-01-01 as it's not used, only the time part is required).
dtEndDateTime DateTime (Value stored has a date and time, date is set to 1900-01-01 as it's not used, only the time part is required).
monRate Money
tblJob
intJobID int (auto inc)
intClientID int
dtStart DateTime
dtEnd DateTime
decHoursElapsed decimal(18,2)
tblJobRate
intJobRateID (auto inc)
intJobID int
intDayType int
StartDateTime DateTime
EndDateTime DateTime
decHoursElapsed decimal(18,2)
monRate Money
tblHoliday
intHolidayID int (auto inc)
intContractorID int
dtHolidayDate DateTime
What I need to do when I add a job is take a copy of the master rates for the contractor and insert them into the tblJobRate table then take the job start and end date\time and split the elapsed hours between them over the records in the tblJobRate table.
What I need to do is look at the the Day or Days the job spans (a job could start one evening and continue into the next morning) to see their type is e.g. is it a holiday for the contractor - if yes then I need to look at any holiday rate bands - if there are none then I need to look at are they at the weekend if yes and the team has rate bands for weekend then use those, if not then is it a specific day of the week (eg Sunday) then check and use the Sunday rate bands if there are any. Failing that then use normal weekday rate bands.
A sample of master rate bands is below...
intRateID, intContractorID, intDayType, dtStartDateTime, dtEndDateTime, monRate
1, 1, 8, 1900-01-01 00:00:00, 1900-01-01 06:00:00, 24.00
2, 1, 8, 1900-01-01 06:00:00, 1900-01-01 10:00:00, 22.00
2, 1, 8, 1900-01-01 10:00:00, 1900-01-01 00:00:00, 27.75
2, 1, 9, 1900-01-01 00:00:00, 1900-01-01 00:00:00, 19.95 (00:00:00 - 00:00:00 spans full 24 hour period)
2, 1, 1, 1900-01-01 00:00:00, 1900-01-01 00:00:00, 18.95
2, 1, 0, 1900-01-01 00:00:00, 1900-01-01 06:00:00, 20.50
2, 1, 0, 1900-01-01 06:00:00, 1900-01-01 00:00:00, 16.50
Can anyone help with this?
Many Thanks
Charlotte CB
Good morning and welcome to SSC!
It seems that your DDL script seems to have become detached from your post, or perhaps you were unaware of the benefits of providing one.
When you have time, please read this article[/url] about the best way to provide us with working sample data and DDL scripts. This will allow the unpaid volunteers of this site to provide you with working, tested code for your particular problem.
Thanks!
September 27, 2011 at 7:36 am
Cadavre (9/27/2011)
Charlottecb (9/27/2011)
Hi All,I'm having a problem constructing a SQL Server 2008 stored procedure and any help would be gratefully received...
A bit of background info...
I have a list of contractors doing jobs. Each contractor has one or more master linked rate bands which form a guide as to which rates to use according to a job they have undertaken's start and end date\time.
If a Job has a Start Date and Time and an End Date and Time (which may span more than 24 hours) I need to split the hours worked up over the different bands.
Data is structured as follows...
tblContractor
intContractorID int (auto inc)
strName
tblMasterRateBand
intRateID int (auto inc)
intContractorID int
intDayType int (1 - 7 is Monday through Sunday, 8 is Holiday, 9 is Weekend, 0 is normal weekday)
dtStartDateTime DateTime (Value stored has a date and time, date is set to 1900-01-01 as it's not used, only the time part is required).
dtEndDateTime DateTime (Value stored has a date and time, date is set to 1900-01-01 as it's not used, only the time part is required).
monRate Money
tblJob
intJobID int (auto inc)
intClientID int
dtStart DateTime
dtEnd DateTime
decHoursElapsed decimal(18,2)
tblJobRate
intJobRateID (auto inc)
intJobID int
intDayType int
StartDateTime DateTime
EndDateTime DateTime
decHoursElapsed decimal(18,2)
monRate Money
tblHoliday
intHolidayID int (auto inc)
intContractorID int
dtHolidayDate DateTime
What I need to do when I add a job is take a copy of the master rates for the contractor and insert them into the tblJobRate table then take the job start and end date\time and split the elapsed hours between them over the records in the tblJobRate table.
What I need to do is look at the the Day or Days the job spans (a job could start one evening and continue into the next morning) to see their type is e.g. is it a holiday for the contractor - if yes then I need to look at any holiday rate bands - if there are none then I need to look at are they at the weekend if yes and the team has rate bands for weekend then use those, if not then is it a specific day of the week (eg Sunday) then check and use the Sunday rate bands if there are any. Failing that then use normal weekday rate bands.
A sample of master rate bands is below...
intRateID, intContractorID, intDayType, dtStartDateTime, dtEndDateTime, monRate
1, 1, 8, 1900-01-01 00:00:00, 1900-01-01 06:00:00, 24.00
2, 1, 8, 1900-01-01 06:00:00, 1900-01-01 10:00:00, 22.00
2, 1, 8, 1900-01-01 10:00:00, 1900-01-01 00:00:00, 27.75
2, 1, 9, 1900-01-01 00:00:00, 1900-01-01 00:00:00, 19.95 (00:00:00 - 00:00:00 spans full 24 hour period)
2, 1, 1, 1900-01-01 00:00:00, 1900-01-01 00:00:00, 18.95
2, 1, 0, 1900-01-01 00:00:00, 1900-01-01 06:00:00, 20.50
2, 1, 0, 1900-01-01 06:00:00, 1900-01-01 00:00:00, 16.50
Can anyone help with this?
Many Thanks
Charlotte CB
Good morning and welcome to SSC!
It seems that your DDL script seems to have become detached from your post, or perhaps you were unaware of the benefits of providing one.
When you have time, please read this article[/url] about the best way to provide us with working sample data and DDL scripts. This will allow the unpaid volunteers of this site to provide you with working, tested code for your particular problem.
Thanks!
Thanks for your help.
September 27, 2011 at 8:43 am
Charlottecb (9/27/2011)
Thanks for your help.
No problem, glad you're satisfied!
I see you're obviously too busy to provide DDL and sample data as per this article[/url]. It's a shame, because I'm sure that if you could spare 5 minutes to set-up a working DDL and sample data script with expected outcome, then a lot of the members of this forum would be happy to help.
Unfortunately, without DDL and sample data scripts[/url], you make it too difficult to help :crying: The only way would be to guess at your table structures and data from your brief description, which has the potential of leading you down the wrong route.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply