Help! CUBE? ROLLUP? or COMPUTE BY?
Giving a table's content as below...
Date Area Product Amount
----
--
2005/07/23 CA Book 150
2005/07/29 NY Pen 70
2005/08/03 CA Pen 500
2005/08/04 CA Book 200
2005/08/05 NY Book 270
May i ask how to get the result as below?
Area Book Book Pen Pen
----
--
CA SUM(Book_CA_thisMonth) SUM(Book_CA_th
isyear) SUM(Pen_CA_thisMonth) SUM(Pe
n_CA_thisyear)
NY SUM(Book_NY_thisMonth) SUM(Book_NY_th
isyear) SUM(Pen_NY_thisMonth) SUM(Pe
n_NY_thisyear)
Should i use CUBE? ROLLUP? or COMPUTE BY? What's the correct SELECT syntax?Hi
COMPUTE/COMPUTE BY are available for backward compatibility and there should
not be used in new code. The information obtained by using ROLLUP would be:
CREATE TABLE myData([Date] datetime,Area char(2),Product char(4),Amount int)
INSERT INTO myData([Date],Area,Product,Amount)
SELECT '20050723','CA','Book', 150
UNION ALL SELECT '20050729','NY','Pen', 70
UNION ALL SELECT '20050803','CA','Pen', 500
UNION ALL SELECT '20050804','CA','Book', 200
UNION ALL SELECT '20050805','NY','Book', 270
CREATE VIEW MyMonthlyValues AS
SELECT YEAR([Date]) AS Year, MONTH([Date]) AS [Month], Area, Product, Amount
FROM MyData
SELECT CASE WHEN (GROUPING([Year]) = 1) THEN 'All' ELSE CAST([Year] AS
CHAR(5)) END AS [Year],
CASE WHEN (GROUPING([Month]) = 1) THEN 'All' ELSE CAST([Month] AS CHAR(4))
END AS [Month],
CASE WHEN (GROUPING([Area]) = 1) THEN 'All' ELSE [Area] END AS [Area],
CASE WHEN (GROUPING([Product]) = 1) THEN 'All' ELSE [Product] END AS
[Product],
SUM(Amount) AS AmountTotal
FROM MyMonthlyValues
GROUP BY [Year], [Month], Area, Product WITH ROLLUP
To restrict to the current month you could do
SELECT CASE WHEN (GROUPING([Year]) = 1) THEN 'All' ELSE CAST([Year] AS
CHAR(5)) END AS [Year],
CASE WHEN (GROUPING([Month]) = 1) THEN 'All' ELSE CAST([Month] AS CHAR(4))
END AS [Month],
CASE WHEN (GROUPING([Area]) = 1) THEN 'All' ELSE [Area] END AS [Area],
CASE WHEN (GROUPING([Product]) = 1) THEN 'All' ELSE [Product] END AS
[Product],
SUM(Amount) AS AmountTotal
FROM MyMonthlyValues
WHERE [Month] = MONTH(GETDATE()) AND [Year] = YEAR(GetDate())
GROUP BY [Year], [Month], Area, Product WITH ROLLUP
You can process the rows on the client if you don't the format you
specified. Alternatively it is possible to do:
CREATE VIEW MyMonthlyTotals AS
SELECT [Year], [Month], [Area], [Product], SUM(Amount) As MonthTotal
FROM MyMonthlyValues
GROUP BY [Year], [Month], [Area], [Product]
SELECT A.[Year], A.[Month], A.[Area], A.[Product], A.[MonthTotal], ( SELECT
SUM(M.[MonthTotal]) FROM MyMonthlyTotals M WHERE A.[Year] = M.[Year] AND
A.[Month] >= M.[Month] AND A.[Area] = M.[Area] AND A.[Product] = M.[Product]
) AS AnnualTotal
FROM MyMonthlyTotals A
WHERE A.[Month] = MONTH(GETDATE())
AND A.[Year] = YEAR(GetDate())
ORDER BY A.[Year], A.[Month], A.[Area], A.[Product]
You may need a Products, Areas and possibly a Calendar Table (see
http://www.aspfaq.com/show.asp?id=2519) to OUTER JOIN to, so that all
products, areas, months are displayed in case data is missing for certain
values.
e.g.
CREATE TABLE Products ( ProductId int NOT NULL IDENTITY(1,1), Product
char(4) )
INSERT INTO Products ( Product )
SELECT 'Book'
UNION ALL SELECT 'Pen'
CREATE TABLE Areas( AreaId int NOT NULL IDENTITY(1,1), Area char(2) )
INSERT INTO Areas ( Area )
SELECT 'CA'
UNION ALL SELECT 'NY'
John
"OKLover" wrote:
> Help! CUBE? ROLLUP? or COMPUTE BY?
>
> Giving a table's content as below...
> Date Area Product Amount
> ----
--
> 2005/07/23 CA Book 150
> 2005/07/29 NY Pen 70
> 2005/08/03 CA Pen 500
> 2005/08/04 CA Book 200
> 2005/08/05 NY Book 270
>
> May i ask how to get the result as below?
> Area Book Book Pen Pen
> ----
--
> CA SUM(Book_CA_thisMonth) SUM(Book_CA_th
isyear) SUM(Pen_CA_thisMonth) SUM(
Pen_CA_thisyear)
> NY SUM(Book_NY_thisMonth) SUM(Book_NY_th
isyear) SUM(Pen_NY_thisMonth) SUM(
Pen_NY_thisyear)
> Should i use CUBE? ROLLUP? or COMPUTE BY? What's the correct SELECT syntax?[/color
]|||What a MVP likes John Bell should to be respected. you do so much. :)
As your suggestion, i got 3 SELECT results as below:
Year Month Area Product AmountTotal
---
2005 7 CA Book 150
2005 7 CA All 150
2005 7 NY Pen 70
2005 7 NY All 70
2005 7 All All 220
2005 8 CA Book 200
2005 8 CA Pen 500
2005 8 CA All 700
2005 8 NY Book 270
2005 8 NY All 270
2005 8 All All 970
2005 All All All 1190
All All All All 1190
Year Month Area Product AmountTotal
---
2005 8 CA Book 200
2005 8 CA Pen 500
2005 8 CA All 700
2005 8 NY Book 270
2005 8 NY All 270
2005 8 All All 970
2005 All All All 970
All All All All 970
Year Month Area Product MonthT AnnualT
----
2005 8 CA Book 200 350
2005 8 CA Pen 500 500
2005 8 NY Book 270 270
Is it possible to get the results like this:
Book Pen
---
CA | 200 350 500 500
NY | 270 270 Null Null
Total with 4 columns and 2 rows excluding the Product and Area label.|||Hi
I would have hoped that you would have taken the suggestions further,
progressing further with the scripts and taken on some of the suggestions,
such as using a product, area and calendar table you can "fill in the gaps"
such as:
CREATE TABLE Calendar ( Month int, Year int, [Date] datetime )
DECLARE @.basedate datetime
SET @.basedate = '20050101'
INSERT Calendar ( Month, Year, [Date] )
SELECT
MONTH(DATEADD(m,i,@.basedate)),Year(DATEA
DD(m,i,@.basedate)),DATEADD(m,i,@.base
date)
FROM ( SELECT 1 AS i
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10
UNION ALL SELECT 11
UNION ALL SELECT 12 ) A
SELECT R.[Area], P.[Product], A.[MonthTotal], ( SELECT
SUM(M.[MonthTotal]) FROM MyMonthlyTotals M WHERE C.[Year] = M.[Year] AND
C.[Month] >= M.[Month] AND R.[Area] = M.[Area] AND P.[Product] = M.[Product]
) AS AnnualTotal
FROM Calendar C
CROSS JOIN [Products] P
CROSS JOIN [Areas] R
LEFT JOIN MyMonthlyTotals A ON A.Product = P.Product AND C.[Month] =
A.[Month] AND C.[Year] = A.[Year] AND R.[Area] = A.[Area]
WHERE C.[Month] = 8
AND C.[Year] = 2005
ORDER BY R.[Area], P.[Product]
This will give
Area Product MonthTotal AnnualTotal
-- -- -- --
CA Book 200 350
CA Pen 500 500
NY Book 270 270
NY Pen NULL 70
In the previous post I said to get into your exact format it is best to do
it on the client, but it is possible using SQL, and there are many posts on
how to CROSSTAB or PIVOT your results such as http://tinyurl.com/7hfet where
if you had read the links such as
http://www.windowsitpro.com/SQLServ...5608/15608.html you
should have come up with:
SELECT [Area], SUM(CASE WHEN [Product] = 'Book' THEN MonthTotal ELSE 0 END)
AS [Book Month],
SUM(CASE WHEN [Product] = 'Book' THEN AnnualTotal ELSE 0 END) AS [Book Year],
SUM(CASE WHEN [Product] = 'Pen' THEN MonthTotal ELSE 0 END) AS [Pen Month],
SUM(CASE WHEN [Product] = 'Pen' THEN AnnualTotal ELSE 0 END) AS [Pen Year]
FROM ( SELECT R.[Area], P.[Product], A.[MonthTotal], ( SELECT
SUM(M.[MonthTotal]) FROM MyMonthlyTotals M WHERE C.[Year] = M.[Year] AND
C.[Month] >= M.[Month] AND R.[Area] = M.[Area] AND P.[Product] = M.[Product]
) AS AnnualTotal
FROM Calendar C
CROSS JOIN [Products] P
CROSS JOIN [Areas] R
LEFT JOIN MyMonthlyTotals A ON A.Product = P.Product AND C.[Month] =
A.[Month] AND C.[Year] = A.[Year] AND R.[Area] = A.[Area]
WHERE C.[Month] = 8
AND C.[Year] = 2005 ) D
GROUP BY [Area]
ORDER BY [Area]
John
"OKLover" wrote:
> What a MVP likes John Bell should to be respected. you do so much. :)
> As your suggestion, i got 3 SELECT results as below:
> Year Month Area Product AmountTotal
> ---
> 2005 7 CA Book 150
> 2005 7 CA All 150
> 2005 7 NY Pen 70
> 2005 7 NY All 70
> 2005 7 All All 220
> 2005 8 CA Book 200
> 2005 8 CA Pen 500
> 2005 8 CA All 700
> 2005 8 NY Book 270
> 2005 8 NY All 270
> 2005 8 All All 970
> 2005 All All All 1190
> All All All All 1190
>
>
> Year Month Area Product AmountTotal
> ---
> 2005 8 CA Book 200
> 2005 8 CA Pen 500
> 2005 8 CA All 700
> 2005 8 NY Book 270
> 2005 8 NY All 270
> 2005 8 All All 970
> 2005 All All All 970
> All All All All 970
>
> Year Month Area Product MonthT AnnualT
> ----
--
> 2005 8 CA Book 200 350
> 2005 8 CA Pen 500 500
> 2005 8 NY Book 270 270
>
> Is it possible to get the results like this:
> Book Pen
> ---
> CA | 200 350 500 500
> NY | 270 270 Null Null
>
> Total with 4 columns and 2 rows excluding the Product and Area label.|||THANK YOU VERY MUCH !!!
