Allocating costs to defined phases

Posted on 2010-08-13
Last Modified: 2012-05-10
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.
Question by:bbaldwin
  • 7
  • 5
LVL 16

Expert Comment

ID: 33435890
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

Author Comment

ID: 33450301

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.

Author Comment

ID: 33450581
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

LVL 16

Expert Comment

ID: 33458275

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

LVL 16

Expert Comment

ID: 33459689
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


Author Comment

ID: 33460050

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.
LVL 16

Expert Comment

ID: 33462269
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

Author Comment

ID: 33468891

Well, this is Microsoft SQL Server. Most of these reports are done from 6 AM one day till 6 AM the next day.
LVL 16

Assisted Solution

Sheils earned 500 total points
ID: 33469965
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

Accepted Solution

bbaldwin earned 0 total points
ID: 33494066

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)


Author Comment

ID: 33494073

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.

Author Closing Comment

ID: 33494078
I was able to get you points anyway. I accepted your solution even though my comment has the correct answer. Thanks for your help!!

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question