|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
|
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
|
Click Here
|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
|
| Link Time and Custom Prices |
| Author |
Message |
| JimK |
| |
| Group | : Members |
| Posts | : 34 |
| Joined | : 2006-08-08 |
|
| Profile |
|
| Posted : 2006-08-31 23:45:59 |
| Tom, I have customers/jobs with custom prices and I'm trying to compose the SQL statement so I can see our WIP for a client. In other words I want to take all of the unbilled (billable) time from a specific customer and then get the custom price for that Item so I can multiply the duration times the rate to get the $ amount of the work in progress. I know it's an outer join on TimeTracking, Customer (to get the PriceLevelRefFullName) and PriceLevelPerItem (to get the PriceLevelPerItemCustomPrice from the combination of Name and PriceLevelPerItemItemRefFullName). Is it possible or do I have to break it down into multiple queries? Thanks! |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-09-01 11:05:24 |
You can start by trying this:
SELECT TimeTracking.ItemServiceRefFullName, TimeTracking.DurationMinutes,
PriceLevelPerItem.PriceLevelPerItemCustomPrice,
(TimeTracking.DurationMinutes / 60) * PriceLevelPerItem.PriceLevelPerItemCustomPrice as "Custom Amount to be Billed"
FROM TimeTracking, PriceLevelPerItem
where TimeTracking.ItemServiceRefListID = PriceLevelPerItem.PriceLevelPerItemItemRefListID
and TimeTracking.IsBillable = 1
and TimeTracking.IsBilled = 0
and TimeTracking.CustomerRefFullName like 'Pretell Real Estate%'
and PriceLevelPerItem.Name like 'Pretell Real Estate%'
The key is the link between TimeTracking.CustomerRefFullName and PriceLevelPerItem.Name as they are not the same. To get the actual PriceLevelPerItem.Name we would need to add the Customer table to the above statement .... but I've keep it simple for now. |
|
|
|
|