January 5, 2009 at 2:58 pm
For some reason I cant seem to figure this out without having to slice and dice with temp tables. Here is the data......
typemonth_nbrmonth_nameamount
Billing1Jan0.0000
Labor1Jan96862.7200
Billing2Feb0.0000
Labor2Feb125233.2200
Billing3Mar0.0000
Labor3Mar102244.6200
Billing4Apr0.0000
Labor4Apr84076.2100
Billing5May0.0000
Labor5May137787.8500
Billing6Jun0.0000
Labor6Jun105135.5700
Billing7Jul0.0000
Labor7Jul113227.9800
Billing8Aug0.0000
Labor8Aug144289.1100
Billing9Sep380500.0050
Labor9Sep116142.8500
Billing10Oct985231.0814
Labor10Oct142803.6700
Billing11Nov902050.0530
Labor11Nov113975.2100
Billing12Dec623420.2364
Labor12Dec83180.1200
I want to take Labor row and divide by Billing grouped by Month. So the results would be.....
1 Jan 0.00
2 Feb 0.00
3 Mar 0.00
4 Apr 0.00
5 May 0.00
6 Jun 0.00
7 Jul 0.00
8 Aug 0.00
9 Sep 0.305237
10 Oct 0.144944
11 Nov 0.126351
12 Dec 0.133425
January 5, 2009 at 3:29 pm
If your table was
CREATE TABLE someData
(
type varchar(20),
month_nbr int,
month_name varchar(20),
amount decimal(10, 4)
)
then your query would be
SELECT month_nbr,
month_name,
case when billingData.amount != 0.0
then laborData.amount / billingData.amount
else 0.0
end as result
FROM someData as laborData INNER JOIN
someData as billingData
on laborData.month_nbr = billingData.month_nbr
and billingData.type = 'Billing'
WHERE laborData.Type = 'Labor'
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
January 5, 2009 at 4:19 pm
thank you!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply