• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 141
  • Last Modified:

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

0
deNZity
Asked:
deNZity
  • 3
  • 3
1 Solution
 
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
 
deNZityAuthor Commented:
looks like this

workunit

        Jan Feb Mar
10  
         18          13  
20  
              28
30

40
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
David ToddSenior DBACommented:
Hi,

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

Regards
  David
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now