Advanced Query Help Needed

Haas C

2012-06-20 15:22:27 UTC

Well, not sure if it is too advanced for most people in this group,
but it is an advanced query for a newbie like me. Recently, I posted
help with a query to help produce results for me for a range of
values. My desired output yielded a table with the following fields:

ROL_1, ROL_2, Year, Net Profit

ROL_1 and ROL_2 were ranges based on values in the ROL field in
tblProfit - basically I wanted to display Net Profit by Year which
fell into the following ROL ranges:

0 to 7.500%
7.501 % to 15.000%
15.001% to 25.000%
and so on

The output looked like this
ROL_1, ROL_2, Year, Net Profit
0.0000, 0.075, 2007, 152522.14
0.0000, 0.075, 2008, 65652.54
0.0000, 0.075, 2009, 324546.89
0.0000, 0.075, 2010, 5656.89
...and so on

The query to generate the above is

Use TestDB

; WITH All_ROL AS (
SELECT 0 AS ROL_1, 0.075 AS ROL_2
UNION ALL
SELECT 0.07501, 0.150
UNION ALL
SELECT 0.1501, 0.250
UNION ALL
SELECT 0.2501, 0.350
UNION ALL
SELECT 0.3501, 1.00

), All_Year AS (
SELECT DISTINCT Year FROM [TestDB].[dbo].[tblProfit$]
), Profitdata AS (
SELECT Year, NetKnownProfit,
ROL_1 = CASE WHEN ROL <= 0.075 THEN 0
WHEN ROL <= 0.15 THEN 0.07501
WHEN ROL <= 0.250 Then 0.1501
WHEN ROL <= 0.350 Then 0.2501
ELSE 0.3501
END,
ROL_2 = CASE WHEN ROL <= 0.075 THEN 0.075
WHEN ROL <= 0.15 THEN 0.15
WHEN ROL <= 0.25 Then 0.25
WHEN ROL <= 0.35 Then 0.35
ELSE 1
END
FROM [TestDB].[dbo].[tblProfit$]
)
SELECT r.ROL_1, r.ROL_2, y.Year, coalesce(SUM(p.NetKnownProfit), 0)
as 'Net Profit'
FROM All_ROL r
CROSS JOIN All_Year y
LEFT JOIN ProfitData p ON r.ROL_1 = p.ROL_1
AND y.Year = p.Year
GROUP BY r.ROL_1, r.ROL_2, y.Year
ORDER BY r.ROL_1, r.ROL_2, y.Year

What I needed added to this query is a count of entires that fall
within the ROL range as well as the Premium (a field in tblProfit)
associated with it. Here's what I'm looking for:

ROL_1, ROL_2, Year, Net Profit, Premium, Count
0.0000, 0.075, 2007, 152522.14, 4545545, 10
0.0000, 0.075, 2008, 65652.54, 12354888, 5
0.0000, 0.075, 2009, 324546.89, 45656, 8
0.0000, 0.075, 2010, 5656.89, 546546, 3

Any and all help will be appreciated - thanks!!!