Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 423
  • Last Modified:

Allocating costs to defined phases

Ok SQL gurus, I think I have a great challenge here for you. I have two tables and I need to allocate costs. One table stores job costs that are related to a header record. The header record stores among other things dtStart and dtEnd for each day which is usually 6AM to 6AM the next day but is not necessarily those times especially for the first and last day. I have a 2nd table that stores phases of the job and actual dtPhaseStart and dtPhaseEnd is stored. This table is usually around 15-20 records of job phases.

Structure looks like this:
Jobs => JobReports (daily records) => JobCosts (many records for each day)


Jobs => JobPhases (usually around 15-20 records. As one phase ends, the dtPhaseEnd actual end date is updated and the dtPhaseStart actual start date of the next phase is updated)

The JobPhases dtPhaseStart and dtPhaseEnd datetime values do not usually match up with the JobReport dtStart and dtEnd datetime values.

Here is some actual data:

Sum of Costs per Report Day
dttmstart                            dttmend                           Sum of Costs
2010-06-30 08:00:00.000      2010-07-01 06:00:00.000      24440
2010-07-01 06:00:00.000      2010-07-01 13:00:00.000      2215
2010-08-03 13:00:00.000      2010-08-04 06:00:00.000      43640
2010-08-04 06:00:00.000      2010-08-05 06:00:00.000      60032.65
2010-08-05 06:00:00.000      2010-08-06 06:00:00.000      62056.92
2010-08-06 06:00:00.000      2010-08-07 06:00:00.000      49725.69
2010-08-07 06:00:00.000      2010-08-08 06:00:00.000      103298.43
2010-08-08 06:00:00.000      2010-08-09 06:00:00.000      49969.8
2010-08-09 06:00:00.000      2010-08-10 06:00:00.000      55214.46
2010-08-10 06:00:00.000      2010-08-11 06:00:00.000      56005.32
2010-08-11 06:00:00.000      2010-08-12 06:00:00.000      68516.64
2010-08-12 06:00:00.000      2010-08-13 06:00:00.000      60680.76

Job Phases
sysseq      dttmstartactual      dttmendactual
1      NULL                           NULL
2      2010-06-30 08:00:00.000      2010-07-01 13:00:00.000
3      NULL                           NULL
4      2010-08-03 13:30:00.000      2010-08-04 06:00:00.000
5      2010-08-04 06:00:00.000      2010-08-04 20:30:00.000
6      2010-08-04 20:30:00.000      2010-08-05 06:00:00.000
7      2010-08-05 06:00:00.000      2010-08-05 18:30:00.000
8      2010-08-05 18:30:00.000      2010-08-07 03:00:00.000
9      2010-08-07 03:00:00.000      2010-08-07 13:30:00.000
10      2010-08-07 13:30:00.000      2010-08-07 17:30:00.000
11      2010-08-07 17:30:00.000      2010-08-12 08:00:00.000
12      2010-08-12 08:00:00.000      NULL
13      NULL                           NULL
14      NULL                           NULL
15      NULL                           NULL
16      NULL                           NULL
17      NULL                           NULL
18      NULL                           NULL
19      NULL                           NULL

Ok, now I need to allocate the daily report costs to the job phases. Let me know if you have any questions.
  • 7
  • 5
2 Solutions
The question is very confusing so I don't know if I am on the right track. It would have been clearer if you had provided the structure of the tables used to store the data that you want to manipulate or better still post a sample of your DB.

Having said that, if the table are correctly structured whatever your question is should be easily addressed by a queries.

Please post a sample of your db or the its structure.

By structure I mean something like

field1 PK

field1 PK
field2 FK

Clearly describe the relationship
bbaldwinAuthor Commented:

Thank you for responding. Sorry I did not describe the issue good enough. Here is the table structure. This is not all the of the fields but it is all the fields that count:

tblWellHeader - one record for each oil or gas well
idWell - PK

tblJob - one record for each job performed on a well

tblJobPhase - usually around 10-20 records describing the phases of a job
idWell - PK - related to a tblWellHeader table
idRecParent - related to idRec tblJob
idRec - PK - GUID

tblJobReport - Daily records during the work on a job
idWell - PK
idRecParent - related to the idRec in tblJob
idrec - GUID - PK
dttmStart - DateTime
dttmEnd - DateTime

tblJobReportCost - records of different costs associated to a daily reports
idWell - PK - links to tblWell
idrecparent - links to idRec in tblJobReport
idrec - PK - GUID
Amount - currency

tblWell => tblJob => tblJobReport => tblJobReportCost
tblWell => tblJob => tblJobPhases

Let me know what else I can do to describe this. My guess is that this will require a stored procedure to get done but I would rather not.
bbaldwinAuthor Commented:
Now what I need to get is an a cost for each phase. A phase can span multiple report days or there can be multiple phase accomplished in a single report day. However, the costs are only single date related. When a JobPhase is complete, the users enter the actual datetime of the end datetime of the phase just finished and the same time goes to the next start datetime.

Simple example:
Job Phases
Phase 1 - start 8/1/2010 06:00AM - end 8/3/2010 10:00PM
Phase 2 - start 8/3/2010 10:00PM - end 8/4/2010 01:00PM

Jost Costs
8/1/2010 06:00AM to 8/2/2010 06:00AM - $1,000
8/2/2010 06:00AM to 8/3/2010 06:00AM - $2,000
8/3/2010 06:00AM to 8/4/2010 06:00AM - $3,000
8/4/2010 06:00AM to 8/5/2010 06:00AM - $4,000

Phase 1 - $1,000 + $2,000 + $2 000 (16 hours of 24 = 16/24 * $3000 = $2,000) = $5,000
Phase 2 - $1,000 (8 hours of 24 = 8/24 * $3,000 = $1,000) + $833 (5 hours of 24 = 5/24 * $4,000 = $833) = $1,833

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.


Firstly I'd suggest some restructuring.

tblReport is not required. Replace it by a query

tblReportCost should be replaced by a table that provides the hourly rate of each phase. Then a query can be used to calculate the total cost of each phase.

It will help to have an additional date field in tblJobPhase. Then record the time start and end on each day. This will make it a lot easier to calculate the total time for each phase. You can come up with a formula that looks at the number of full days within the interval, multiple that by your normal daily hour and add to the number of hours on the partly completed day. But this can get complicated and it does not take into consideration any down time in phase.

You do not need idWell in tblJobPhase since relationship is Well ->Jobs ->JobPhase. So a phase is linked to a job which is in turn related to a well.

I have attached a demo based on my understanding of your db. Let me know if I am on the right track and we can use it as the basis for for working through this problem

I have made some further modification and added a few queries to calculates the daily phase cost and the total cost of each phase. I am not getting the same answer as you thought and I don't understand why your total allocated cost ($6,833) is not the same as the cost over the entire work period ($10,000). As you will see the total allocation in my qryPhaseCost is $10,000

bbaldwinAuthor Commented:

Thanks, I will look things over. Unfortunately, the database is a retail product and I do not have control over the data structure. I am writing a SQL report that is supposed to produce the results that I am looking for here. I will look over what you have done and see what you have. I could have created an .mdb and written code to solve my problem but I wanted it all to be self-contained in the query that is related to the SQL report. My guess is that I will have to look at doing this in a stored procedure to report back the results.
You do not need to use stored procedure in this particular case. Just a few query should do the job. Structuring your base data to start and end within a normal day would have made things easier but the structure that you've got can be worked around if there is not alternative.

Are you using  mysql database
bbaldwinAuthor Commented:

Well, this is Microsoft SQL Server. Most of these reports are done from 6 AM one day till 6 AM the next day.
OK It's a bad design but still workable.

I can't see an easy way to split the allocation by day. But allocating cost per phase over a period of time is possible.

The formula is (TotalCost x TotalPhaseTime)/TotalWorkTime
bbaldwinAuthor Commented:

I worked on this query for 4 days and finally got it to work. Ths allocation portion of the query is what makes the rest of the query work. Here is the allocation portion: Since you gave it your best shot, I am giving you that points anyway. Thanks for giving it a try!!

select wvjobreport.idwell, apc.idrec, sum((CASE WHEN CAST(DATEDIFF(n, apc.dttmstartactual, wvjobreport.dttmstart) AS float) / 60 <= 0 THEN CAST(DATEDIFF(n,
         apc.dttmstartactual, wvjobreport.dttmend) AS float) / 60 WHEN CAST(DATEDIFF(n, apc.dttmendactual, wvjobreport.dttmend) AS float)
         / 60 > 0 THEN 24 WHEN CAST(DATEDIFF(n, wvjobreport.dttmstart, apc.dttmendactual) AS float) / 60 > 24 THEN 24 ELSE CAST(DATEDIFF(n,
         wvjobreport.dttmstart, apc.dttmendactual) AS float) / 60 END) / 24 * dc.DailyCost) as TotalCost
FROM wvjobreport INNER JOIN
       wvjobprogramphase apc ON wvjobreport.idwell = apc.idwell inner join wvjob on wvjobreport.idrecparent = wvjob.idrec AND
       wvjobreport.dttmend >= apc.dttmstartactual AND wvjobreport.dttmstart <= apc.dttmendactual INNER JOIN
         (SELECT        idrecparent, SUM(cost) AS DailyCost
            FROM            wvjobreportcostgen
            GROUP BY idrecparent) AS dc ON wvjobreport.idrec = dc.idrecparent
            group by wvjobreport.idwell, apc.idrec

1. wvJobReport is the daily report record
2. wvJobProgramPhase is the phase table that have the odd start dates and end dates
3. wvJobReportCostGen is the detail costs for each day (wvJobReport)

bbaldwinAuthor Commented:

After I accepted my own answer, I don't see how to award you the points. I am going to object and see if I can still get you the points.
bbaldwinAuthor Commented:
I was able to get you points anyway. I accepted your solution even though my comment has the correct answer. Thanks for your help!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now