March 28, 2011 at 2:56 am
I have a scenario like the following
CREATE TABLE #bond
(bondId VARCHAR(3),
rateID CHAR(1),
bondWks INT,
bondForecast NUMERIC(8,4)
)
CREATE TABLE #rates
(rateID CHAR(1),
wk1 NUMERIC(2,2),
wk2 NUMERIC(2,2),
wk3 NUMERIC(2,2),
wk4 NUMERIC(2,2))
INSERT INTO #Bond SELECT 'abc','A',2,500.00
INSERT INTO #Bond SELECT 'dgh','A',3,600.50
INSERT INTO #Bond SELECT 'wfe','B',1,50.00
INSERT INTO #Bond SELECT 'sgi','C',3,800.70
INSERT INTO #rates SELECT 'A',.3,.3,.2,.2
INSERT INTO #rates SELECT 'B',.4,.4,.2,.2
INSERT INTO #rates SELECT 'C',.2,.3,.2,.2
DROP TABLE #Bond
DROP TABLE #rates
What I would like to be able to do, is where there is bond 'abc' and two weeks
perform the following calculations
Wk1
500.00 * .3 / (.3 + .3 )
Wk2
500.00 * .3 / (.3 + .3 )
because bondwks = 2
on bond dgh it would be
Wk1
600.50 *.3 /(.3+.3+.2)
Wk2
600.50 *.3 /(.3+.3+.2)
Wk3
600.50 *.2 /(.3+.3+.2)
I need to show this with bonds going down one column and then wk1, wk2, wk3, wk4, going across the top (in realisty I have 26 weeks going across the top) with a null value for where there is no value for that week.
I also need to show it with a wk for each bond (I already have the data for what weeks there are bonds)
ie
Bond,Week,Result
March 28, 2011 at 7:06 am
This might help explain in better,
SELECT
a.bondID,
a.rateID,
a.BondWks,
a.BondForecast,
x1= CASE WHEN a.bondWks >=1 THEN (a.bondForecast * wk1)/
CASE WHEN A.bondWks =1 THEN (wk1)
WHEN a.bondWks = 2 THEN (Wk1+Wk2)
WHEN a.bondWks = 3 THEN (wk1+Wk2+Wk3)
WHEN a.bondWks = 4 THEN (wk1+Wk2+Wk3+Wk4)
END
ELSE 0 END,
x2= CASE WHEN a.bondWks >=2 THEN (a.bondForecast * wk2)/
CASE WHEN a.bondWks = 2 THEN (Wk1+Wk2)
WHEN a.bondWks = 3 THEN (wk1+Wk2+Wk3)
WHEN a.bondWks = 4 THEN (wk1+Wk2+Wk3+Wk4)
END
ELSE 0 END,
x3= CASE WHEN a.bondWks >=3 THEN (a.bondForecast * wk3)/
CASE WHEN a.bondWks = 3 THEN (wk1+Wk2+Wk3)
WHEN a.bondWks = 4 THEN (wk1+Wk2+Wk3+Wk4)
END ELSE 0 END ,
x4 = CASE WHEN a.bondWks = 4 THEN (a.bondForecast * wk4)/
(wk1+Wk2+Wk3+Wk4)
ELSE 0 END
FROM #bond a
LEFT JOIN #rates b ON b.rateId = a.rateid
As mentioned, in reality I have 26 columns going across the top, I'm sure there is a better way of acheiving this, rather than doing cases for each of the 26 levels
March 28, 2011 at 12:24 pm
Approaching it with a CTE may make your code a bit more readable. Your original query plus an ORDER BY is first followed by mine which uses a CTE. It will allow you to only define the CASE statement once which may help. The two queries return the same resultset using your test set of data but please be sure to test to make sure they are equivalent.
SELECT a.bondID,
a.rateID,
a.BondWks,
a.BondForecast,
x1 = CASE WHEN a.bondWks >= 1 THEN (a.bondForecast * wk1) / CASE WHEN A.bondWks = 1 THEN (wk1)
WHEN a.bondWks = 2 THEN (Wk1 + Wk2)
WHEN a.bondWks = 3 THEN (wk1 + Wk2 + Wk3)
WHEN a.bondWks = 4 THEN (wk1 + Wk2 + Wk3 + Wk4)
END
ELSE 0
END,
x2 = CASE WHEN a.bondWks >= 2 THEN (a.bondForecast * wk2) / CASE WHEN a.bondWks = 2 THEN (Wk1 + Wk2)
WHEN a.bondWks = 3 THEN (wk1 + Wk2 + Wk3)
WHEN a.bondWks = 4 THEN (wk1 + Wk2 + Wk3 + Wk4)
END
ELSE 0
END,
x3 = CASE WHEN a.bondWks >= 3 THEN (a.bondForecast * wk3) / CASE WHEN a.bondWks = 3 THEN (wk1 + Wk2 + Wk3)
WHEN a.bondWks = 4 THEN (wk1 + Wk2 + Wk3 + Wk4)
END
ELSE 0
END,
x4 = CASE WHEN a.bondWks = 4 THEN (a.bondForecast * wk4) / (wk1 + Wk2 + Wk3 + Wk4)
ELSE 0
END
FROM #bond a
LEFT JOIN #rates b ON b.rateId = a.rateid
ORDER BY a.bondWks ;
WITH cte
AS (SELECT a.bondId,
a.rateID AS bond_rate_id,
a.bondWks,
a.bondForecast,
b.wk1,
b.wk2,
b.wk3,
b.wk4,
CASE WHEN a.bondWks = 1 THEN (b.wk1)
WHEN a.bondWks = 2 THEN (b.Wk1 + b.Wk2)
WHEN a.bondWks = 3 THEN (b.wk1 + b.Wk2 + b.Wk3)
WHEN a.bondWks = 4 THEN (b.wk1 + b.Wk2 + b.Wk3 + b.Wk4)
-- ... more here
END AS bond_wk_sum
FROM #bond a
LEFT JOIN #rates b ON b.rateId = a.rateid
)
SELECT a.bondID,
a.bond_rate_id AS rateID,
a.BondWks,
a.BondForecast,
CASE WHEN a.bondWks >= 1 THEN (a.bondForecast * wk1) / a.bond_wk_sum
ELSE 0
END AS x1,
CASE WHEN a.bondWks >= 2 THEN (a.bondForecast * wk2) / a.bond_wk_sum
ELSE 0
END AS x2,
CASE WHEN a.bondWks >= 3 THEN (a.bondForecast * wk3) / a.bond_wk_sum
ELSE 0
END AS x3,
CASE WHEN a.bondWks = 4 THEN (a.bondForecast * wk4) / a.bond_wk_sum
ELSE 0
END AS x4
FROM cte a
ORDER BY a.bondWks ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply