locked
How to implement a stored procedure to get this summary result? RRS feed

  • Question

  • User-609535877 posted

    I will implement a SSRS summary data report with following data records from one table.

    ID         Part                  Type                 Value

    1          Payroll              State Tax           2010

    1          Payroll              City Tax            500

    1          Payroll              Medical             300

    2          Payroll              State Tax           2000

    2          Payroll              City Tax             400

    3          Payroll              FICA                  200

    1          Refund              State Tax           -500

    1          Refund              Medical             -100

    3          Payroll              FICA                  200

    1          Refund              State Tax           -500

    1          Refund              Medical             -100

    How Do I implement a sql stored procedure to produce following result by summarizing Type for each Part so it is easy to build the ssrs report? Thanks a lot!

    Type                      Payroll                  Refund                 Total

    State Tax              4010                      -500                       3600

    City Tax                 900                        0                            900

    FICA                       400                         0                          400

    Medical                300                         -100                       200

    Monday, October 29, 2018 6:03 PM

Answers

  • User1724605321 posted

    Hi zhao790 ,

    You can refer to below code sample for GROUP BY clause with an aggregator 'SUM()' :

    1> create table employee(
    2>     ID          int,
    3>     name        nvarchar (10),
    4>     salary      int,
    5>     start_date  datetime,
    6>     city        nvarchar (10),
    7>     region      char (1))
    8> GO
    1>
    2> insert into employee (ID, name,    salary, start_date, city,       region)
    3>               values (1,  'Jason', 40420,  '02/01/94', 'New York', 'W')
    4> GO
    
    (1 rows affected)
    1> insert into employee (ID, name,    salary, start_date, city,       region)
    2>               values (2,  'Robert',14420,  '01/02/95', 'Vancouver','N')
    3> GO
    
    (1 rows affected)
    1> insert into employee (ID, name,    salary, start_date, city,       region)
    2>               values (3,  'Celia', 24020,  '12/03/96', 'Toronto',  'W')
    3> GO
    
    (1 rows affected)
    1> insert into employee (ID, name,    salary, start_date, city,       region)
    2>               values (4,  'Linda', 40620,  '11/04/97', 'New York', 'N')
    3> GO
    
    (1 rows affected)
    1> insert into employee (ID, name,    salary, start_date, city,       region)
    2>               values (5,  'David', 80026,  '10/05/98', 'Vancouver','W')
    3> GO
    
    (1 rows affected)
    1> insert into employee (ID, name,    salary, start_date, city,       region)
    2>               values (6,  'James', 70060,  '09/06/99', 'Toronto',  'N')
    3> GO
    
    (1 rows affected)
    1> insert into employee (ID, name,    salary, start_date, city,       region)
    2>               values (7,  'Alison',90620,  '08/07/00', 'New York', 'W')
    3> GO
    
    (1 rows affected)
    1> insert into employee (ID, name,    salary, start_date, city,       region)
    2>               values (8,  'Chris', 26020,  '07/08/01', 'Vancouver','N')
    3> GO
    
    (1 rows affected)
    1> insert into employee (ID, name,    salary, start_date, city,       region)
    2>               values (9,  'Mary',  60020,  '06/09/02', 'Toronto',  'W')
    3> GO
    
    (1 rows affected)
    1>
    2> select * from employee
    3> GO
    ID          name       salary      start_date              city       region
    ----------- ---------- ----------- ----------------------- ---------- ------
              1 Jason            40420 1994-02-01 00:00:00.000 New York   W
              2 Robert           14420 1995-01-02 00:00:00.000 Vancouver  N
              3 Celia            24020 1996-12-03 00:00:00.000 Toronto    W
              4 Linda            40620 1997-11-04 00:00:00.000 New York   N
              5 David            80026 1998-10-05 00:00:00.000 Vancouver  W
              6 James            70060 1999-09-06 00:00:00.000 Toronto    N
              7 Alison           90620 2000-08-07 00:00:00.000 New York   W
              8 Chris            26020 2001-07-08 00:00:00.000 Vancouver  N
              9 Mary             60020 2002-06-09 00:00:00.000 Toronto    W
    
    (9 rows affected)
    1>
    2> --GROUP BY clause with an aggregator 'SUM()'.
    3>
    4> SELECT region, SUM(Salary)
    5> FROM Employee
    6> WHERE ID BETWEEN 1 AND 10
    7> GROUP BY Region
    8> GO
    region
    ------ -----------
    N           151120
    W           295106
    
    (2 rows affected)
    1>
    2>
    3> drop table employee
    4> GO
    1>

    Best Regards,

    Nan Yu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 30, 2018 6:13 AM