Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 tough formula only few tables

Author  Topic 

tnichols333
Starting Member

14 Posts
Posted - 2010-03-29 : 12:43:08
Thank you in advance for any help you can provide...

I have three tables and here are there fields that are of importance.

Truck (truckid)

Annual Expenses (truckid, amount, startdate, enddate)

monthly details (truckid, fueldollars, partsexpenses, miscexpenses, mileage, numberdelivered)

basically I need two numbers "cost per mile" and "cost per delivery"

In the web form report I allow them to select a start month and ending month and year (so no crossing years).

Basically I need to add up all my annual expenses and divide them by twelve and then multiply by how many months there are (end month - start month). I also need to add all my monthly expenses (partsexpenses, miscexpenses, fueldollars). I then need to add them all together and divide them by 1. mileage and 2. numberdelivered.

So basically after all is said and done I need to end up with two "results" as stated above. "Cost per mile" cost per delivery".

Please help!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-03-29 : 12:54:29
[code]SELECT t.truckid,
(((ae.AnnualTotal/12.0) * (@end month - @start month+1)) + COALESCE(md.partsexpenses,0) + COALESCE(md.miscexpenses,0) + COALESCE(md.fueldollars,0))/md.mileage AS [Cost per mile],
(((ae.AnnualTotal/12.0) * (@end month - @start month+1)) + COALESCE(md.partsexpenses,0) + COALESCE(md.miscexpenses,0) + COALESCE(md.fueldollars,0))/md.numberdelivered AS [cost per delivery]
FROM Truck t
INNER JOIN (SELECT truckid,
SUM(amount) AS AnnualTotal
FROM AnnualExpenses
WHERE YEAR(startdate)=@year
AND YEAR(enddate) = @year
GROUP BY truckid)ae
ON ae.truckid = t.truckid
INNER JOIN MonthlyDetails md
ON md.truckid = t.truckid[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

tnichols333
Starting Member

14 Posts
Posted - 2010-03-29 : 17:56:38
quote:
Originally posted by visakh16

SELECT t.truckid,
(((ae.AnnualTotal/12.0) * (@end month - @start month+1)) + COALESCE(md.partsexpenses,0) +  COALESCE(md.miscexpenses,0) + COALESCE(md.fueldollars,0))/md.mileage AS [Cost per mile],
(((ae.AnnualTotal/12.0) * (@end month - @start month+1)) + COALESCE(md.partsexpenses,0) +  COALESCE(md.miscexpenses,0) + COALESCE(md.fueldollars,0))/md.numberdelivered AS [cost per delivery]
FROM Truck t
INNER JOIN (SELECT truckid,
            SUM(amount) AS AnnualTotal
            FROM AnnualExpenses
            WHERE YEAR(startdate)=@year 
            AND YEAR(enddate) = @year
            GROUP BY truckid)ae
ON ae.truckid = t.truckid
INNER JOIN MonthlyDetails md
ON md.truckid = t.truckid


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




This is what I have:

SELECT t.truckid, t.truckidentifier, 
(((ae.AnnualTotal/12.0) * (@endmonth - @startmonth+1)) + COALESCE(md.partsexpenses,0) +  COALESCE(md.miscexpenses,0) + COALESCE(md.fueldollars,0)) / md.mileage AS CostPerMile, 
(((ae.AnnualTotal/12.0) * (@endmonth - @startmonth+1)) + COALESCE(md.partsexpenses,0) + COALESCE(md.miscexpenses,0) + COALESCE(md.fueldollars,0)) / md.numberdelivered AS CostPerDelivery 
FROM Truck t 
INNER JOIN (SELECT truckid, SUM(amount) AS AnnualTotal FROM AnnualConstants WHERE YEAR(startdate)=@year AND YEAR(enddate) = @year GROUP BY truckid) ae 
ON ae.truckid = t.truckid 
INNER JOIN MonthlyDetail md ON md.truckid = t.truckid


The results come back with two records - and it should only return one per truck.truckid.

For instance if you select multiple months it should return what all of them are together. If you select only one month you should get the results just for that month. Also the results do not appear to be correct.
Go to Top of Page

DBA in the making
Aged Yak Warrior

638 Posts
Posted - 2010-03-29 : 18:34:41
quote:
Originally posted by tnichols333
The results come back with two records - and it should only return one per truck.truckid.

For instance if you select multiple months it should return what all of them are together. If you select only one month you should get the results just for that month. Also the results do not appear to be correct.


Sounds like you have one record per truck per month in the monthly details table. Try this:

SELECT t.truckid, t.truckidentifier, 
SUM((((ae.AnnualTotal/12.0) * (@endmonth - @startmonth+1)) + COALESCE(md.partsexpenses,0) +  COALESCE(md.miscexpenses,0) + COALESCE(md.fueldollars,0)) / md.mileage) AS CostPerMile, 
SUM((((ae.AnnualTotal/12.0) * (@endmonth - @startmonth+1)) + COALESCE(md.partsexpenses,0) + COALESCE(md.miscexpenses,0) + COALESCE(md.fueldollars,0)) / md.numberdelivered) AS CostPerDelivery 
FROM Truck t 
INNER JOIN (SELECT truckid, SUM(amount) AS AnnualTotal 
			FROM AnnualConstants 
			WHERE YEAR(startdate)=@year 
				AND YEAR(enddate) = @year 
			GROUP BY truckid) ae 
	ON ae.truckid = t.truckid 
INNER JOIN MonthlyDetail md 
	ON md.truckid = t.truckid
GROUP BY t.truckid, t.truckidentifier


There are 10 types of people in the world, those that understand binary, and those that don't.
Go to Top of Page

tnichols333
Starting Member

14 Posts
Posted - 2010-03-29 : 18:52:52
quote:
Originally posted by DBA in the making

quote:
Originally posted by tnichols333
The results come back with two records - and it should only return one per truck.truckid.

For instance if you select multiple months it should return what all of them are together. If you select only one month you should get the results just for that month. Also the results do not appear to be correct.


Sounds like you have one record per truck per month in the monthly details table. Try this:

SELECT t.truckid, t.truckidentifier, 
SUM((((ae.AnnualTotal/12.0) * (@endmonth - @startmonth+1)) + COALESCE(md.partsexpenses,0) +  COALESCE(md.miscexpenses,0) + COALESCE(md.fueldollars,0)) / md.mileage) AS CostPerMile, 
SUM((((ae.AnnualTotal/12.0) * (@endmonth - @startmonth+1)) + COALESCE(md.partsexpenses,0) + COALESCE(md.miscexpenses,0) + COALESCE(md.fueldollars,0)) / md.numberdelivered) AS CostPerDelivery 
FROM Truck t 
INNER JOIN (SELECT truckid, SUM(amount) AS AnnualTotal 
			FROM AnnualConstants 
			WHERE YEAR(startdate)=@year 
				AND YEAR(enddate) = @year 
			GROUP BY truckid) ae 
	ON ae.truckid = t.truckid 
INNER JOIN MonthlyDetail md 
	ON md.truckid = t.truckid
GROUP BY t.truckid, t.truckidentifier


There are 10 types of people in the world, those that understand binary, and those that don't.


Oh we are getting there!!!

Only thing I do not see is where are we specifying the months that we want? For instance - I only want the results for the month of March (MonthlyDetail.Month - this is stored as int - so March would = 3) Also - my results still seem not to be correct - they seem very high.
Go to Top of Page

DBA in the making
Aged Yak Warrior

638 Posts
Posted - 2010-03-29 : 18:59:08
Does the MonthlyDetail table have a Year Column?

There are 10 types of people in the world, those that understand binary, and those that don't.
Go to Top of Page

tnichols333
Starting Member

14 Posts
Posted - 2010-03-30 : 09:08:13
quote:
Originally posted by DBA in the making

Does the MonthlyDetail table have a Year Column?

There are 10 types of people in the world, those that understand binary, and those that don't.


Yes
Go to Top of Page

DBA in the making
Aged Yak Warrior

638 Posts
Posted - 2010-03-30 : 09:25:09
This version is modified to only join to records in the MonthlyDetail table from the appropriate year and month range.

SELECT t.truckid, t.truckidentifier, 
SUM((((ae.AnnualTotal/12.0) * (@endmonth - @startmonth+1)) + COALESCE(md.partsexpenses,0) +  COALESCE(md.miscexpenses,0) + COALESCE(md.fueldollars,0)) / md.mileage) AS CostPerMile, 
SUM((((ae.AnnualTotal/12.0) * (@endmonth - @startmonth+1)) + COALESCE(md.partsexpenses,0) + COALESCE(md.miscexpenses,0) + COALESCE(md.fueldollars,0)) / md.numberdelivered) AS CostPerDelivery 
FROM Truck t 
INNER JOIN (SELECT truckid, SUM(amount) AS AnnualTotal 
			FROM AnnualConstants 
			WHERE YEAR(startdate)=@year 
				AND YEAR(enddate) = @year 
			GROUP BY truckid) ae 
	ON ae.truckid = t.truckid 
INNER JOIN MonthlyDetail md 
	ON md.truckid = t.truckid
	AND md.[Month] BETWEEN @startmonth AND @endmonth
	AND md.[Year] = @Year
GROUP BY t.truckid, t.truckidentifier


There are 10 types of people in the world, those that understand binary, and those that don't.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-03-30 : 13:32:27
I would have done that already if MonthDetail had month as well as year fields. but as per OP's initial post that does not seem to be the case.So not sure what field designates month year info in monthdetails

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

DBA in the making
Aged Yak Warrior

638 Posts
Posted - 2010-03-30 : 13:45:06
quote:
Originally posted by visakh16

I would have done that already if MonthDetail had month as well as year fields. but as per OP's initial post that does not seem to be the case.So not sure what field designates month year info in monthdetails


The OP didn't include all the fields, only those they (s)he thought were important. I'd say it's a fair be that MonthDetails has a month (INT) and year (INT) column, and is used to store various details for each specific month.

Perhaps a full script for the tables would be a good idea tnichols333.

There are 10 types of people in the world, those that understand binary, and those that don't.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-03-30 : 13:47:25
quote:
Originally posted by DBA in the making

quote:
Originally posted by visakh16

I would have done that already if MonthDetail had month as well as year fields. but as per OP's initial post that does not seem to be the case.So not sure what field designates month year info in monthdetails


The OP didn't include all the fields, only those they (s)he thought were important. I'd say it's a fair be that MonthDetails has a month (INT) and year (INT) column, and is used to store various details for each specific month.

Perhaps a full script for the tables would be a good idea tnichols333.

There are 10 types of people in the world, those that understand binary, and those that don't.

Unless (s)he posts it we cant assume. It might be even that there's a single column with info in format like CCYYMM

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

DBA in the making
Aged Yak Warrior

638 Posts
Posted - 2010-03-30 : 13:52:46
quote:
Originally posted by visakh16
Unless (s)he posts it we cant assume. It might be even that there's a single column with info in format like CCYYMM

Maybe. I did ask if it had a year column, but what I meant to ask was "Does it have a month and year column?".


There are 10 types of people in the world, those that understand binary, and those that don't.
Go to Top of Page

tnichols333
Starting Member

14 Posts
Posted - 2010-03-30 : 14:35:04
quote:
Originally posted by DBA in the making

This version is modified to only join to records in the MonthlyDetail table from the appropriate year and month range.

SELECT t.truckid, t.truckidentifier, 
SUM((((ae.AnnualTotal/12.0) * (@endmonth - @startmonth+1)) + COALESCE(md.partsexpenses,0) +  COALESCE(md.miscexpenses,0) + COALESCE(md.fueldollars,0)) / md.mileage) AS CostPerMile, 
SUM((((ae.AnnualTotal/12.0) * (@endmonth - @startmonth+1)) + COALESCE(md.partsexpenses,0) + COALESCE(md.miscexpenses,0) + COALESCE(md.fueldollars,0)) / md.numberdelivered) AS CostPerDelivery 
FROM Truck t 
INNER JOIN (SELECT truckid, SUM(amount) AS AnnualTotal 
			FROM AnnualConstants 
			WHERE YEAR(startdate)=@year 
				AND YEAR(enddate) = @year 
			GROUP BY truckid) ae 
	ON ae.truckid = t.truckid 
INNER JOIN MonthlyDetail md 
	ON md.truckid = t.truckid
	AND md.[Month] BETWEEN @startmonth AND @endmonth
	AND md.[Year] = @Year
GROUP BY t.truckid, t.truckidentifier


There are 10 types of people in the world, those that understand binary, and those that don't.


This works great if I only select one month - but if I select multiple months the results are very high... any suggestions?
Go to Top of Page

DBA in the making
Aged Yak Warrior

638 Posts
Posted - 2010-03-30 : 17:00:40
I think the problem is here:

SUM((((ae.AnnualTotal/12.0) * (@endmonth - @startmonth+1)) + COALESCE(md.partsexpenses,0) +  COALESCE(md.miscexpenses,0) + COALESCE(md.fueldollars,0)) / md.mileage) AS CostPerMile, 
SUM((((ae.AnnualTotal/12.0) * (@endmonth - @startmonth+1)) + COALESCE(md.partsexpenses,0) + COALESCE(md.miscexpenses,0) + COALESCE(md.fueldollars,0)) / md.numberdelivered) AS CostPerDelivery

This is summing the cost per mile from each month. Try this:

SUM((((ae.AnnualTotal/12.0) * (@endmonth - @startmonth+1)) + COALESCE(md.partsexpenses,0) +  COALESCE(md.miscexpenses,0) + COALESCE(md.fueldollars,0))) / SUM(md.mileage) AS CostPerMile, 
SUM((((ae.AnnualTotal/12.0) * (@endmonth - @startmonth+1)) + COALESCE(md.partsexpenses,0) + COALESCE(md.miscexpenses,0) + COALESCE(md.fueldollars,0))) / SUM(md.numberdelivered) AS CostPerDelivery

This divides the total cost across all relevant month by the total mileage.

There are 10 types of people in the world, those that understand binary, and those that don't.
Go to Top of Page

tnichols333
Starting Member

14 Posts
Posted - 2010-03-31 : 09:48:03
quote:
Originally posted by DBA in the making

I think the problem is here:

SUM((((ae.AnnualTotal/12.0) * (@endmonth - @startmonth+1)) + COALESCE(md.partsexpenses,0) +  COALESCE(md.miscexpenses,0) + COALESCE(md.fueldollars,0)) / md.mileage) AS CostPerMile, 
SUM((((ae.AnnualTotal/12.0) * (@endmonth - @startmonth+1)) + COALESCE(md.partsexpenses,0) + COALESCE(md.miscexpenses,0) + COALESCE(md.fueldollars,0)) / md.numberdelivered) AS CostPerDelivery

This is summing the cost per mile from each month. Try this:

SUM((((ae.AnnualTotal/12.0) * (@endmonth - @startmonth+1)) + COALESCE(md.partsexpenses,0) +  COALESCE(md.miscexpenses,0) + COALESCE(md.fueldollars,0))) / SUM(md.mileage) AS CostPerMile, 
SUM((((ae.AnnualTotal/12.0) * (@endmonth - @startmonth+1)) + COALESCE(md.partsexpenses,0) + COALESCE(md.miscexpenses,0) + COALESCE(md.fueldollars,0))) / SUM(md.numberdelivered) AS CostPerDelivery

This divides the total cost across all relevant month by the total mileage.

There are 10 types of people in the world, those that understand binary, and those that don't.


I got it I think!!! Here is what I have and it seems to work... let me know if you disagree with anything in it:


SELECT t.truckid, t.truckidentifier, 
(((ae.AnnualTotal/12.0) * (@endmonth - @startmonth+1)) + SUM(md.partsexpenses) + SUM(md.miscexpenses) + SUM(md.fueldollars)) / SUM(md.mileage) AS CostPerMile, 
(((ae.AnnualTotal/12.0) * (@endmonth - @startmonth+1)) + SUM(md.partsexpenses) + SUM(md.miscexpenses) + SUM(md.fueldollars)) / SUM(md.numberdelivered) AS CostPerDelivery 
FROM Truck t 
INNER JOIN (SELECT truckid, SUM(amount) AS AnnualTotal 
FROM AnnualConstants WHERE YEAR(startdate) = @year AND YEAR(enddate) = @year GROUP BY truckid) ae 
ON ae.truckid = t.truckid 
INNER JOIN MonthlyDetail md ON md.truckid = t.truckid AND md.[Month] 
BETWEEN @startmonth AND @endmonth AND md.[Year] = @year GROUP BY t.truckid, t.truckidentifier, ae.annualtotal


Thank you soooo much for all your help!!! I never could have got it without you!!!
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site