Solved

Allocating costs to defined phases

Posted on 2010-08-13
14
400 Views
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)

and

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.
0
Comment
Question by:bbaldwin
  • 7
  • 5
14 Comments
 
LVL 16

Expert Comment

by:Sheils
Comment Utility
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

TableA
field1 PK
field2
field3

TableB
field1 PK
field2 FK
field3

Clearly describe the relationship
0
 
LVL 2

Author Comment

by:bbaldwin
Comment Utility
sb9,

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
idWell
idRec

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
dttmStartActual
dttmEndActual

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

Relationships:
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.
Bob
0
 
LVL 2

Author Comment

by:bbaldwin
Comment Utility
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

Allocations:
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


0
 
LVL 16

Expert Comment

by:Sheils
Comment Utility
Ok

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


CostAllocation.mdb
0
 
LVL 16

Expert Comment

by:Sheils
Comment Utility
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


CostAllocation.mdb
0
 
LVL 2

Author Comment

by:bbaldwin
Comment Utility
sb9,

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.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 16

Expert Comment

by:Sheils
Comment Utility
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
0
 
LVL 2

Author Comment

by:bbaldwin
Comment Utility
sb9,

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

Assisted Solution

by:Sheils
Sheils earned 500 total points
Comment Utility
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
0
 
LVL 2

Accepted Solution

by:
bbaldwin earned 0 total points
Comment Utility
sb9,

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

Notes:
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)

Whew!!
0
 
LVL 2

Author Comment

by:bbaldwin
Comment Utility
sb9,

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.
0
 
LVL 2

Author Closing Comment

by:bbaldwin
Comment Utility
I was able to get you points anyway. I accepted your solution even though my comment has the correct answer. Thanks for your help!!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now