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_thisyear)    SUM(Pen_CA_thisMonth)    SUM(Pen_CA_thisyear) NY    SUM(Book_NY_thisMonth)    SUM(Book_NY_thisyear)    SUM(Pen_NY_thisMonth)    SUM(Pen_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
Show quoteHide quote

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(DATEADD(m,i,@basedate)),DATEADD(m,i,@basedate)
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/SQLServer/Article/ArticleID/15608/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

Show quoteHide quote

THANK YOU VERY MUCH !!!

syntax error in CONTAINS query
need help with execution plan
Understanding best way to optimize...
Calling of Store Procs on SQL Server from MS Access
Squeezing out more performance!!