0

I am having difficulty limiting my query to only count items which match all parts of a csv string. My current query runs but it seems to only count one of the elements listed in the csv, rather then count all the activities that match with the csv. I noticed that when I separated these values some had spaces and others not but I have no idea how to resolve.

 select (sum (kept)/60) 
   from (select distinct sa.staff_id, sa.service_date, sa.client_id, sa.actual_duration as kept
           from rpt_scheduled_activities as sa
                inner join rpt_staff_performance_target as spt
                   on sa.staff_id = spt.staff_id 
          where (sa.status = 'Kept' 
                 and sa.service_date between '01-nov-2014' and '30-nov-2014' 
                 and sa.activity_name in 
                                      (select regexp_split_to_table(spt.activity,',') 
                                         from rpt_staff_performance_target as spt))
                                      ) as p

Thank you for your consideration and for any tips you can provide!!

ErstwhileIII
  • 4,829
  • 2
  • 23
  • 37
sqlnewbie2015
  • 1
  • Please see http://stackoverflow.com/q/3653462/398670, http://dba.stackexchange.com/q/55871/7788, http://dba.stackexchange.com/q/17808/7788 then fix your schema. – Craig Ringer Jan 07 '15 at 02:00
  • 4 rules define CSV, unfortiunamtely regex can only handle one or two rules at a time. – Jasen Jan 07 '15 at 04:20

0 Answers0