0

I am trying to use a drop down menu to have a report act like 2 separate reports. this is a parameter called rtype that is value of 0-2.

Data is client appointment and I am trying to report on their ability to keep appointments.

Option 1 and 2 we are looking specifically for their first appointment hence the 'and not exist' section in the where with the subquery looking for a previous appointment.

List 3 is essentially list1+list2(and a little more) but we dont care if it is the initial appointment

select sa.organization, sa.status, count(1)
 from mytable as sa
where  sa.service_date between  ${sdate} and ${edate}
 and (
 (${rtype}::int = 0 and sa.activity_id in (..) /*list 1*/ )
 OR(${rtype}::int = 1 and sa.activity_id in (..)  /*list 2*/)
 OR(${rtype}::int = 2 AND sa.activity_id not in (..) /*list 3*/)
)

and ( 
 (${rtype}::int !=2 AND not exists (select *
                     from rpt_scheduled_activities as sa2
                    where sa2.client_id = sa.client_id
                      and (   
       (${rtype}::int = 0 and sa.activity_id in (..)/*list 1*/ )
       OR
       (${rtype}::int = 1 and sa.activity_id in (..)  /*list 1*/ )
       )
                      and sa2.status in ('Kept')
       and sa2.service_date <  sa.service_date
                      ))
       OR (${rtype}::int = 2))
     
group by 
  sa.organization,
          sa.status
order by 
   sa.status

Essentially I would like to 'skip' the not exist subquery when rtype = 2.

Can anyone point out what I am doing wrong?

mikew0w
  • 1
  • 2
  • How you know that you query is not skiping not exist subquery? – fenix Sep 21 '14 at 04:50
  • When my parameter = 2 I get an empty set returned. I assume that it is finding _any_ previous data point since there is no limitation on the activity ID when rtype=2. – mikew0w Sep 28 '14 at 14:55

0 Answers0