displaying data in chart for year problem

I have the following query that is the dataset for a chart in MS reporting services.

select workunit,to_char(trunc(startm,'MM'),'MON-YY ')as Period ,count(case_id)
from(
       select cd.case_id, cd.workunit,m.startm
       from
         (select n, add_months(trunc(sysdate,'mm'),-n) as startm, last_day(add_months(trunc(sysdate,'mm'),-n)) as endm
                        from imt_user.numbers@GHWWEB1
                        where n between 1 and  12)m,
                        case_details CD         
        where  ((CD.CASE_END_DATE Is Null) OR (CD.CASE_END_DATE>=startm))
        AND (trunc(CD.CASE_START_DATE,'mm')= startm ))
GROUP BY WORKUNIT, startm

I need to display count(case_id) for each workunit  for each startm(period)
like

                Jan   Feb   May   Jun ....
wunit1      50     34   25     19
wunit2      12     32   23      45
wunit3      76     34   67      12

TIA

deNZityAsked:
Who is Participating?
 
David ToddSenior DBACommented:
Hi,

Post a question in community support asking to have this question moved to the Oracle forum.

Regards
  David
0
 
David ToddSenior DBACommented:
Hi,

How many months do you need to display?

Regards
  David
0
 
deNZityAuthor Commented:
Thanks for the reply, all 12 months however I have a solution. I
display  the value for the month and have the workunit as a parameter
which is displayed in a textbox. User has to select workunit.

query as it is now.

select workunit,to_char(trunc(startm,'MM'),'MON-YY ')as Period ,count(case_id)
from(
       select cd.case_id, cd.workunit,m.startm
       from
         (select n, add_months(trunc(sysdate,'mm'),-n) as startm
                        from imt_user.numbers@GHWWEB1
                        where n between 1 and  12)m,
                        case_details CD         
        where  ((CD.CASE_END_DATE Is Null) OR (CD.CASE_END_DATE>=startm))
        AND (trunc(CD.CASE_START_DATE,'mm')<= startm )
        AND workunit = :PARAM1)
GROUP BY WORKUNIT, startm
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
deNZityAuthor Commented:
looks like this

workunit

        Jan Feb Mar
10  
         18          13  
20  
              28
30

40
0
 
David ToddSenior DBACommented:
Hi,

What are you developing this in? There are some non-SQL functions in there ...

Anyway, if you know how many months you want, the query is quite easy if a touch long to write

select workunit
  , (
  select count()
  from dbo.Sometable si
  where si.workunit = s.workunit and si.date between '2007-01-01' and '2007-01-31'
  ) JanCount
  , (
  ... as above but the date is for February
  ) FebCount
from dbo.SomeTable s
where ...

HTH
  David
0
 
deNZityAuthor Commented:
The datasource is an Oracle database.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.