[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 774
  • Last Modified:

Crosstab query using the DB2 stored procedure

Hi,

     I have a table called Problems with the following columns & data.


     Table Name: Problems

       Tkt#                       Problem_Code                Resolver_Group         Reporter_Group

     __________________________________________________________________________


    22                              Closed                           ABC                           Mechanical
    33                              Closed FC                      ABC                           IT
    11                              Resolved                        XYB                           Mechanical
    04                              Open                                                             IT
    99                              Closed FC                       XYB                          HR
    14                              Resolved                        PYT                           FI
    09                              Closed                           XYB                          HR

________________________________________________________________________________


I want to produce the Cross tab report with the following format :


Resolver_Group            Closed            ClosedFc      Total
________________________________________________________

ABC                                 1                   1               2
XYB                                 2                   1               3
PYT                                 1                   0               1

________________________________________________________

Criteria for producing the above ourptut:
____________________________________

Exlude Open Ticktes
CLOSED = Closed & Resolved Tickets
CLOSEDFC - Closed-FC ticketes
Total = Total number os Closed & Closed Fc tickets.

Please let me know the solution as soon as possible.

Thank You
Korni

0
Kornipalli
Asked:
Kornipalli
  • 2
  • 2
  • 2
  • +1
3 Solutions
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Kornipalli,

This is clearly a homework assignment and the rules disallow us providing homework.  Make a stab at it and we'll be glad to help you.


Good Luck!
Kent
0
 
sachinwadhwaCommented:
if you think logically ... its not a very hard !!

I can give you hint....

use SUM function
0
 
KornipalliAuthor Commented:
Hi Kent,
    I am not undersatdning what you meant by the homework assignment & the meaning of a stab.
    I have tried to do it by Cube function, but could not able to do it.
    Please let me know.

Thanks
Kornipalli
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Kornipalli,

Sorry.  I sometimes forget that American slang isn't universal.  In this case "stab" means attempt.  And the way the question is phrased leads me to believe that this is schoolwork.  The rules of the board prohibit us from giving complete answers to homework assignments, though we can help you to get to the answer.

There are several ways to get there.  I suggest that the "more basic" method is to simply count the objects and put them into the report.  This is wordier than other solutions, but let's walk before we run.

From the data in the table we only really care about two columns -- the problem_code and the resolver_group.  And regarding the problem_code, we only care about two of the possible status codes.

So we simply count the rows with the correct status and join the result to the resolver group.

Select
  resolver_group,
  (count (*) from Problems where Problem_code = 'Closed') as a,
  (count (*) from Problems where Problem_code = 'Closed FC') as b
from Problems p
where p.resolver_group = a.resolver_group
  and p.resolver_group = b.resolver_group;

The SQL above will get you close, but it isn't a final answer.  Because I do believe this to be homework, I'll let you try and complete it.  If you're struggling, write back and I'll help you along.


Good Luck!
Kent
0
 
sachinwadhwaCommented:
use mine & kent's hint togeather and you will reach to answer quickly...
0
 
KornipalliAuthor Commented:
Hi Kent,
           Thank You for the response. It is not a Home assignment. I am a Fulltime employee & working on a project with the new tchnology IBM DB2.
           I have provided you the details just with general details.
           So, please let me know your soultion & it will be really a great help.
           I am also writing some code to test it out.

Thank You
Kornipalli

0
 
ocgstylesCommented:
the simplest i can think of would be to use a a combination of a SUM and CASE to get the answer:

ex:
sum(case when problem_code in ('Closed', 'Resolved') then 1 else 0 end) as closed

that should be able to help you along...
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now