Query help needed

[Haas C]

Hello all!

I'm a newbie with SQL and I've run into a wall here trying to figure
the best way to write a query in SQL Server 2008 to get my desired
results. Here's the scenario:

I have a table named tblProfit. The three fields I am concerned with
are: ROL, Year, and NetProfit - here's what I need to achieve:

I want the query to return aggregated values of NetProfit by Year
based on the following ROL ranges:

0% to 25%
25.1% to 50%
50.1% to 100%

I have four Years worth of data in the table: 2008, 2009, 2010, and
2011. I'd like my output show the ranges above - so something like
this:

ROL_1, ROL_2, Year, NetProfit
0%, 25%, 2008, 1000000
25.1%, 50%, 2008, 4000000
50.1%, 100%, 2008, 3500000
etc. for each of the years (Also, ROL field has everything in
decimals, i.e. 0.25 is 25% but I can make that formatting once I have
the output)

Any and all help would be appreciated - thanks!

[Erland Sommarskog]

Post by Haas C
I have a table named tblProfit. The three fields I am concerned with
I want the query to return aggregated values of NetProfit by Year
0% to 25%
25.1% to 50%
50.1% to 100%
I have four Years worth of data in the table: 2008, 2009, 2010, and
2011. I'd like my output show the ranges above - so something like
ROL_1, ROL_2, Year, NetProfit
0%, 25%, 2008, 1000000
25.1%, 50%, 2008, 4000000
50.1%, 100%, 2008, 3500000
etc. for each of the years (Also, ROL field has everything in
decimals, i.e. 0.25 is 25% but I can make that formatting once I have
the output)

If I understand this correctly:

WITH CTE AS (
SELECT Year, NetProfit,
ROL_1 = CASE WHEN ROL <= 25 THEN 0
WHEN ROL <= 50 THEN 25.1
ELSE 10.1
END
)
SELECT Year, ROL_1, SUM(NetProfit)
FROM tblProfit
GROUP BY Year, ROL_1

I first define a CTE (Common Table Expression) to be able to define the
CASE experssion in one place and use it in two. The expression for ROL_2
is left as an exercise. :-)

[Haas C]

Post by Haas C
I have a table named tblProfit. The three fields I am concerned with
I want the query to return aggregated values of NetProfit by Year
0% to 25%
25.1% to 50%
50.1% to 100%
I have four Years worth of data in the table: 2008, 2009, 2010, and
2011. I'd like my output show the ranges above - so something like
ROL_1, ROL_2, Year, NetProfit
0%, 25%, 2008, 1000000
25.1%, 50%, 2008, 4000000
50.1%, 100%, 2008, 3500000
etc. for each of the years (Also, ROL field has everything in
decimals, i.e. 0.25 is 25% but I can make that formatting once I have
the output)

   WITH CTE AS (
     SELECT Year, NetProfit,
            ROL_1 = CASE WHEN ROL <= 25 THEN 0
                         WHEN ROL <= 50 THEN 25.1
                         ELSE 10.1
                    END
   )
   SELECT Year, ROL_1, SUM(NetProfit)
   FROM   tblProfit
   GROUP  BY Year, ROL_1
I first define a CTE (Common Table Expression) to be able to define the
CASE experssion in one place and use it in two. The expression for ROL_2
is left as an exercise. :-)
--
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx- Hide quoted text -
- Show quoted text -

Thanks for that, but I get the following error message when running
the query:

Msg 207, Level 16, State 1, Line 2
Invalid column name 'Year'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'NetProfit'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'ROL'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'ROL'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'ROL_1'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'ROL_1'.

Not sure what to do with this...thanks!

[Erland Sommarskog]

Post by Haas C
Thanks for that, but I get the following error message when running
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Year'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'NetProfit'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'ROL'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'ROL'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'ROL_1'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'ROL_1'.
Not sure what to do with this...thanks!

There is a FROM clause missing. Where, I leave to you as an exercise
to find out. It should not be too difficult.

If you want a tested solution, you should post:

o CREATE TABLE statements for your table.
o INSERT statement with sample data.
o The desired result given the sample.
o Which version of SQL Server you are using.

This makes it easy to copy and paste into SSMS to develop a tested solution.
It also helps to clarify what you are looking for.

If you are not making this effort, the answer you get is likely to be
untested and may include occasional errors.

[Haas C]

Thanks but I copied and pasted your text...

Post by Erland Sommarskog

Post by Haas C
Thanks for that, but I get the following error message when running
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Year'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'NetProfit'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'ROL'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'ROL'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'ROL_1'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'ROL_1'.
Not sure what to do with this...thanks!

There is a FROM clause missing. Where, I leave to you as an exercise
to find out. It should not be too difficult.
o  CREATE TABLE statements for your table.
o  INSERT statement with sample data.
o  The desired result given the sample.
o  Which version of SQL Server you are using.
This makes it easy to copy and paste into SSMS to develop a tested solution.
It also helps to clarify what you are looking for.
If you are not making this effort, the answer you get is likely to be
untested and may include occasional errors.
--
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx- Hide quoted text -
- Show quoted text -

[Erland Sommarskog]

Post by Haas C
Thanks but I copied and pasted your text...

======

Post by Haas C

Post by Erland Sommarskog
o  CREATE TABLE statements for your table.
o  INSERT statement with sample data.
o  The desired result given the sample.
o  Which version of SQL Server you are using.
This makes it easy to copy and paste into SSMS to develop a tested
solution. It also helps to clarify what you are looking for.

=======================================================================

Post by Haas C

Post by Erland Sommarskog
If you are not making this effort, the answer you get is likely to be
untested and may include occasional errors.

=======================================================================

As I said, there was a FROM clause missing in my query. And

==================================================================
Where, I leave to you as an exercise to find out. It should not be too
difficult.
==================================================================

[Haas C]

OK, here's the SQL statement to CREATE the table:
----

Create table dbo.tblProfit
(
YEAR INT NOT NULL,
ROL FLOAT NOT NULL,
NetProfit FLOAT NOT NUll
);

INSERT INTO dbo.tblProfit (YEAR, ROL, NetProfit)
VALUES
(2007, 0.25, 34733.40),
(2007, 0.04, 27786.72),
(2007, 0.06, 18589.29),
(2007, 0.20, 28546.58),
(2007, 0.11, 23550.27),
(2007, 0.05, 17841.50),
(2007, 0.16, 223554.84),
(2007, 0.07, 137885.83),
(2007, 0.04, 116038.62),
(2007, 0.09, 32580.68),
(2007, 0.04, 14155.01),
(2007, 0.12, 171204.77),
(2007, 0.08, 133402.88),
(2007, 0.23, 509006.36),
(2007, 0.23, 47462.65),
(2007, 0.14, 56888.65),
(2008, 0.15, 45918.07),
(2008, 0.06, 36951.60),
(2008, 0.25, 7521.23),
(2008, 0.02, 8110.72),
(2008, 0.02, 33587.24),
(2008, 0.58, 41911.10),
(2008, 0.02, 32174.18),
(2008, 0.08, 13854.66),
(2008, 0.12, 139866.08),
(2008, 0.03, 69273.29),
(2008, 0.02, 84037.41),
(2008, 0.06, 43822.48),
(2008, 0.03, 65922.60),
(2008, 0.25, 13254.68),
(2008, 0.05, 39896.18),
(2008, 0.31, 13628.92),
(2008, 0.17, 37449.70),
(2008, 0.06, 19398.90),
(2008, 0.15, 58041.45),
(2008, 0.03, 51592.40),
(2008, 0.25, 23061.82),
(2008, 0.16, 17890.76),
(2008, 0.05, 16126.63),
(2008, 0.02, 9784.23),
(2008, 0.09, 48263.48),
(2008, 0.25, 40226.46),
(2009, 0.07, 50564.60),
(2009, 0.11, 3742.75),
(2009, 0.07, 24653.70),
(2009, 0.03, 61277.31),
(2009, 0.13, 1013238.56),
(2009, 0.14, 33917.02),
(2009, 0.09, 43498.96),
(2009, 0.16, 35488.66),
(2009, 0.10, 29404.89),
(2009, 0.16, 16077.72),
(2009, 0.69, 28042.54),
(2009, 0.03, 26546.94),
(2009, 0.02, 17573.33),
(2009, 0.08, 14555.48),
(2009, 0.12, 145554.85),
(2009, 0.03, 100035.88),
(2010, 0.02, 53385.27),
(2010, 0.02, 47985.06),
(2010, 0.04, 58558.50),
(2010, 0.05, 41644.72),
(2010, 0.38, 31814.37),
(2010, 0.10, 30271.85),
(2010, 0.02, 14014.87),
(2010, 0.08, 50028.04),
(2010, 0.62, 56310.32),
(2010, 0.12, 13746.47),
(2010, 0.05, 11976.77),
(2011, 0.11, 45781.50),
(2011, 0.09, 36625.20),
(2011, 0.03, 26451.53),
(2011, 0.14, 769129.24),
(2011, 0.06, 234401.29),
(2011, 0.75, 161150.89),
(2011, 0.04, 14243.13),
(2011, 0.12, 69587.88),
(2011, 0.03, 91817.35),
(2011, 0.15, 8851.09),
(2011, 0.09, 2421.33),
(2011, 0.15, 30521.00),
(2011, 0.11, 87493.54),
(2011, 0.09, 122490.95),
(2011, 0.13, 8927.39),
(2011, 0.11, 5768.47),
(2011, 0.06, 13836.59),
(2011, 0.29, 23342.87),
(2011, 0.25, 40694.67),
(2011, 0.19, 110689.50),
(2011, 0.84, 44764.14);
-------

The desired output is the following table (the first row contains
column headers):

ROL_1, ROL_2, Year, NetProfit
0.00, 0.25, 2007, 1593228.05
0.00, 0.25, 2008, 956026.25
0.00, 0.25, 2009, 1616130.65
0.00, 0.25, 2010, 321611.55
0.00, 0.25, 2011, 1719731.65
0.26, 0.50, 2007, 0.00
0.26, 0.50, 2008, 13628.92
0.26, 0.50, 2009, 0.00
0.26, 0.50, 2010, 31814.37
0.26, 0.50, 2011, 23342.87
0.51, 1.00, 2007, 0.00
0.51, 1.00, 2008, 41911.1
0.51, 1.00, 2009, 28042.54
0.51, 1.00, 2010, 56310.32
0.51, 1.00, 2011, 205915.03

I can generate the summed number for NetProfit by Year by ROL range
with the following statement:

SELECT SUM([NetProfit])
FROM [dbo].[tblProfit]
Where [YEAR] = 2011 AND [ROL] Between 0.51 AND 1.00

But all it gives me is the summed up NetProfit for 2011 with the low
range of 0.51 and the high range of 1.00 - I need to produce an output
table as shown above. I use SQL Server 2008. I hope this is what you
requested me to post - please let me know if I need anything else -
any and all help is much appreciated!

Thanks!

[Gene Wirchenko]

On Thu, 9 Feb 2012 12:40:48 -0800 (PST), Haas C
<***@gmail.com> wrote:

[snip]

Post by Haas C
table as shown above. I use SQL Server 2008. I hope this is what you
requested me to post - please let me know if I need anything else -
any and all help is much appreciated!

Yes. I was able to plug it into SQL SErver 2008 Express. Without
that, I would have just skipped it.

"year" is a bad choice of column name as it is a reserved word.

I went with your between, but that is a bad idea. If there is
data that has an ROL of 0.255, it will not be picked up. I suggest
you use
case
when ROL<0.00 then 0
when ROL<=0.25 then 1
when ROL<=0.50 then 2
when ROL<=1.00 then 3
else 0
end
or similar.

The following will do almost what you want:

***** Start of Code *****
SELECT
[year],
case
when ROL between 0.00 and 0.25 then 1
when ROL between 0.26 and 0.50 then 2
when ROL between 0.51 and 1.00 then 3
else 0
end,
sum([NetProfit])
FROM [dbo].[tblProfit]
group by
case
when ROL between 0.00 and 0.25 then 1
when ROL between 0.26 and 0.50 then 2
when ROL between 0.51 and 1.00 then 3
else 0
end,
[Year]
***** End of Code *****

It does not output rows when there are no rows for the year and
ROL category. This means that the three zero rows are missing.

The above does look ugly because of an SQL quirk. That column
case can not be given a name and be referred to in the group by, so
the code has to be repeated.

Sincerely,

Gene Wirchenko

[Erland Sommarskog]

Post by Haas C
But all it gives me is the summed up NetProfit for 2011 with the low
range of 0.51 and the high range of 1.00 - I need to produce an output
table as shown above. I use SQL Server 2008. I hope this is what you
requested me to post - please let me know if I need anything else -
any and all help is much appreciated!

It's so much easier with test data. Beside the sloopy errors, I failed to
observe that you wanted zeroes for the combinations misisng. That makes the
query a little more interesting:

; WITH All_ROL AS (
SELECT 0 AS ROL_1, 0.25 AS ROL_2
UNION ALL
SELECT 0.26, 0.50
UNION ALL
SELECT 0.51, 1
), All_years AS (
SELECT DISTINCT Year FROM tblProfit
), Profitdata AS (
SELECT Year, NetProfit,
ROL_1 = CASE WHEN ROL <= 0.25 THEN 0
WHEN ROL <= 0.50 THEN 0.26
ELSE 0.51
END,
ROL_2 = CASE WHEN ROL <= 0.25 THEN 0.25
WHEN ROL <= 0.50 THEN 0.50
ELSE 1
END
FROM tblProfit
)
SELECT r.ROL_1, r.ROL_2, y.Year, coalesce(SUM(p.NetProfit), 0)
FROM All_ROL r
CROSS JOIN All_years 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


The first two CTEs (Common Table Expression) serves to produce all
possible ROL and year values, and then the CROSS JOIN spans up all
combinations. The third CTE is only there to make the GROUP BY a
little easier to write. We left join to that CTE, to retain the
rows where there is no rows in tblProfit.

[Haas C]

Erland,

Thank you very much - that solution worked! Truly appreicate the help!

Post by Erland Sommarskog

Post by Haas C
But all it gives me is the summed up NetProfit for 2011 with the low
range of 0.51 and the high range of 1.00 - I need to produce an output
table as shown above. I use SQL Server 2008. I hope this is what you
requested me to post - please let me know if I need anything else -
any and all help is much appreciated!

It's so much easier with test data. Beside the sloopy errors, I failed to
observe that you wanted zeroes for the combinations misisng. That makes the
   ; WITH All_ROL AS (
      SELECT 0 AS ROL_1, 0.25 AS ROL_2
      UNION ALL
      SELECT 0.26, 0.50
      UNION ALL
      SELECT 0.51, 1
   ), All_years AS (
      SELECT DISTINCT Year FROM tblProfit
   ), Profitdata AS (
     SELECT Year, NetProfit,
            ROL_1 = CASE WHEN ROL <= 0.25 THEN 0
                         WHEN ROL <= 0.50 THEN 0.26
                         ELSE 0.51
                    END,
            ROL_2 = CASE WHEN ROL <= 0.25 THEN 0.25
                         WHEN ROL <= 0.50 THEN 0.50
                         ELSE 1
                    END
     FROM   tblProfit
   )
   SELECT r.ROL_1, r.ROL_2, y.Year, coalesce(SUM(p.NetProfit), 0)
   FROM   All_ROL r
   CROSS  JOIN All_years 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
The first two CTEs (Common Table Expression) serves to produce all
possible ROL and year values, and then the CROSS JOIN spans up all
combinations. The third CTE is only there to make the GROUP BY a
little easier to write. We left join to that CTE, to retain the
rows where there is no rows in tblProfit.
--
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx