Totals across a rows columns and potentialy multiple rows

Question

Hi, I thought I posted this question last week but cannot find trace so apologies if I duplicate it:

I have a requirement to total across a row but only columns in a user specified range. My results may require a grand total of multiple rows depending on the user parameters.
The actual spec is to calculate total sales over a given period range for a specified customer range from a table that hold sales by year/period. The unique key on the table is customer/year and for each row there are 13 period columns: period01....period13.

The user wishes to run the report for example with the following criteria: Customer A ~ Customer A, Year/Period: 0810 ~ 0906

Therefore not only do I need only total across the row for year 08 for period 10 to 12 only but then add to it the total of period columns 1-6 from the row year 09 Eg dataset:

Customer Year  Period01 Period02 ..03 ..04 ..05 ..06 ..07 ..08 ..09 ..10 ..11 ..12 ..13
CUSTA    2008     5     4         1    2    3    2    1     1    1   1     1   1    2 
CUSTA    2009     2     3         2    1    2    5    6     7    2   0     4   1    5

Result output = 20

I’m not much kop at in depth sql; a few static columns totalled across the row is my level; so I’m hoping some nice kind expert out there is able & willing to point me in the right direction. I will probably need apply any sql to a crystal report which I think is the end users main aim and from where the user parameters will be parsed from.

Just seen the text formatting of the resultant question, sorry bout that but I'm sure you get the jist of my example data.

Many thanks in advance 

Answer 1

On SQL Server 2005 and above you can use UNPIVOT operator to rotate the columns into the rows and then you can simply select and sumarize the data.

Here you have a complete solution.

DECLARE @tbl TABLE(
    Customer varchar(20),
    [Year] int,
    Period1 int,
    Period2 int,
    Period3 int,
    Period4 int,
    Period5 int,
    Period6 int,
    Period7 int,
    Period8 int,
    Period9 int,
    Period10 int,
    Period11 int,
    Period12 int,
    Period13 int
)

INSERT INTO @tbl(
    Customer,
    [Year],
    Period1,
    Period2,
    Period3,
    Period4,
    Period5,
    Period6,
    Period7,
    Period8,
    Period9,
    Period10,
    Period11,
    Period12,
    Period13
)
SELECT 'CUSTA', 2008, 5, 4, 1, 2, 3, 2, 1, 1, 1, 1, 1, 1, 2 UNION ALL
SELECT 'CUSTA', 2009, 2, 3, 2, 1, 2, 5, 6, 7, 2, 0, 4, 1, 5


;WITH DataTable AS (
    SELECT 
        Customer, 
        [Year], 
        Period, 
        Data
    FROM 
    (SELECT
        Customer,
        [Year],
        Period1 AS "1",
        Period2 AS "2",
        Period3 AS "3",
        Period4 AS "4",
        Period5 AS "5",
        Period6 AS "6",
        Period7 AS "7",
        Period8 AS "8",
        Period9 AS "9",
        Period10 AS "10",
        Period11 AS "11",
        Period12 AS "12",
        Period13 AS "13"
    FROM @tbl
    ) p
    UNPIVOT
        (Data FOR Period in ("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13")
    ) AS unpvt
)
SELECT 
    SUM(Data) 
FROM DataTable
WHERE Customer = 'CUSTA' AND ([Year] * 100 + Period) >= 200810 AND ([Year] * 100 + Period) <= 200906

For SQL 2000 you can achieve result using several UNIONS. Here you have the SQL 2000 solution

DECLARE @tbl TABLE(
    Customer varchar(20),
    [Year] int,
    Period1 int,
    Period2 int,
    Period3 int,
    Period4 int,
    Period5 int,
    Period6 int,
    Period7 int,
    Period8 int,
    Period9 int,
    Period10 int,
    Period11 int,
    Period12 int,
    Period13 int
)

INSERT INTO @tbl(
    Customer,
    [Year],
    Period1,
    Period2,
    Period3,
    Period4,
    Period5,
    Period6,
    Period7,
    Period8,
    Period9,
    Period10,
    Period11,
    Period12,
    Period13
)
SELECT 'CUSTA', 2008, 5, 4, 1, 2, 3, 2, 1, 1, 1, 1, 1, 1, 2 UNION ALL
SELECT 'CUSTA', 2009, 2, 3, 2, 1, 2, 5, 6, 7, 2, 0, 4, 1, 5


SELECT
    Sum(Data)
FROM    
(SELECT
    Customer,
    [Year],
    1 AS Period,
    Period1 Data
FROM @tbl
UNION ALL
SELECT
    Customer,
    [Year],
    2 AS Period,
    Period2 Data
FROM @tbl
UNION ALL
SELECT
    Customer,
    [Year],
    3 AS Period,
    Period3 Data
FROM @tbl
UNION ALL
SELECT
    Customer,
    [Year],
    4 AS Period,
    Period4 Data
FROM @tbl
UNION ALL
SELECT
    Customer,
    [Year],
    5 AS Period,
    Period5 Data
FROM @tbl
UNION ALL
SELECT
    Customer,
    [Year],
    6 AS Period,
    Period6 Data
FROM @tbl
UNION ALL
SELECT
    Customer,
    [Year],
    7 AS Period,
    Period7 Data
FROM @tbl
UNION ALL
SELECT
    Customer,
    [Year],
    8 AS Period,
    Period8 Data
FROM @tbl
UNION ALL
SELECT
    Customer,
    [Year],
    9 AS Period,
    Period9 Data
FROM @tbl
UNION ALL
SELECT
    Customer,
    [Year],
    10 AS Period,
    Period10 Data
FROM @tbl
UNION ALL
SELECT
    Customer,
    [Year],
    11 AS Period,
    Period11 Data
FROM @tbl
UNION ALL
SELECT
    Customer,
    [Year],
    12 AS Period,
    Period12 Data
FROM @tbl
UNION ALL
SELECT
    Customer,
    [Year],
    13 AS Period,
    Period13 Data
FROM @tbl
) DataTable 
WHERE Customer = 'CUSTA' AND ([Year] * 100 + Period) >= 200810 AND ([Year] * 100 + Period) <= 200906

Totals across a rows columns and potentialy multiple rows

Follow this question

Related Questions