Date: Tue, 6 Jan 2015 17:05:52 -0700 (MST)
From: sqlnewbie2015 <cara.esu@gmail.com>
To: pgsql-sql@postgresql.org
Message-ID: <1420589152496-5833121.post@n5.nabble.com>
Subject: Count values that match elements in csv string
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

Hello All- 
I am having difficulty limiting my query to only count items which match any
part of a csv string table.  My current query runs but it seems to only
count one of the elements listed in the csv and stops rather then count all
the activities that match with the csv.
 
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

 Details: I need to calculate how many hours our staff spends seeing
clients. Each staff has different appointments that can count toward this.
The specified appointments for each staff are listed as comma separated
values. My existing query calculates the appointment hours for each staff in
a given time period. However, I need help limiting my query to only include
specified activities for each staff. My current where clause uses IN to
compare the appointment (i.e. activity) listed in the staff's schedule with
what is listed an an approved appointment type (i.e. performance target
activity). 

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

Cara


--
View this message in context: http://postgresql.nabble.com/Count-values-that-match-elements-in-csv-string-tp5833121.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql