agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedCount values that match elements in csv string
2+ messages / 2 participants
[nested] [flat]
* Count values that match elements in csv string
@ 2015-01-07 00:05 sqlnewbie2015 <cara.esu@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: sqlnewbie2015 @ 2015-01-07 00:05 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Count values that match elements in csv string
@ 2015-01-07 02:21 David G Johnston <david.g.johnston@gmail.com>
parent: sqlnewbie2015 <cara.esu@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: David G Johnston @ 2015-01-07 02:21 UTC (permalink / raw)
To: pgsql-sql
sqlnewbie2015 wrote
> 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
How about something considerably easier to read...
FROM ... sa
JOIN ... spt USING (staff_id)
WHERE sa.activity_name = ANY( regexp_split_to_array( spt.activity, ',' ) )
AND [status, service-name]
You haven't explained or shown why you believe DISTINCT is necessary so I
question its presence.
It would be a lot easier if you either show schema or, even better, provide
a query of the form
WITH sa AS ( ... ), spt AS ( ... )
SELECT ...
FROM sa JOIN spt USING (staff_id)
and provide some sample data inside the WITH/CTE constructs so that someone
can simply copy/paste your example and run it without having to create
tables.
The lack of any example data makes suggestions difficult to give here. The
ANY(array) expression is much cleaner and from what I can tell the presence
of the IN (subquery) with its own FROM clause is pointless if not outright
problematic.
David J.
--
View this message in context: http://postgresql.nabble.com/Count-values-that-match-elements-in-csv-string-tp5833121p5833136.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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2015-01-07 02:21 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-01-07 00:05 Count values that match elements in csv string sqlnewbie2015 <cara.esu@gmail.com>
2015-01-07 02:21 ` David G Johnston <david.g.johnston@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox