Thanks Thomas, 

Works like a charm.

-Philip


________________________________
From: Thomas Clarke <thomas.p.clarke@...>
To: firebird-support@yahoogroups.com
Sent: Saturday, June 18, 2011 8:02 AM
Subject: Re: [firebird-support] Looping Query to count checkouts

On Fri, Jun 17, 2011 at 10:29 PM, John <javakqj@...> wrote:

> **
>
>
> Try this replacing my query with
> yours.----------------------------------------------------------set term #
> ;execute blockRETURNS ( x varchar(20) )asdeclare i int = 102;beginwhile (i <
> 169) dobeginfor select a.EMP_NO from employee a where a.EMP_NO !=null into
> :xdo suspend;i = i +
> 1;endend#----------------------------------------------------------
> --jd
> --- On Fri, 6/17/11, prwels <prwels@...> wrote:
>
> From: prwels <prwels@...>
> Subject: [firebird-support] Looping Query to count checkouts
> To: firebird-support@yahoogroups.com
> Date: Friday, June 17, 2011, 9:58 AM
>
>
>
>
> Hi all,
>
> I have a database of assets which can be checked out by various people.
> There is a table of history, which records all the checking and checkout
> dates and times as well as personnel ids.
>
> So far I am able to use FlameRobin to count the checkouts for one
> individual at a time, but I would like to loop through all the personnel
> ids, so I only have to run the script once, rather than 60+ times.
>
> I finally got my initial loop code to work, but as soon as I add my SELECT
> line the code starts giving me "Token unknown" for the ';' at the end of the
> SELECT line. If I take out the semi-colon, it gives me the same error for
> the variable on the next line.
>
> Here is my code so far. Any suggestions would be appreciated.
>
> set term # ;
>
> execute block
>
> as
>
> declare i int = 102;
>
> begin
>
> while (i < 169) do
>
> begin
>
> select a."Description"
>
> from "AssetHistory" a
>
> where a."EntryType" = 'CheckOut' and a."Personnel_ID" = 103;
>
> i = i + 1;
>
> end
>
> end
>
> #
>
> [Non-text portions of this message have been removed]
>
> 
>

Hi prwels,

How about:

select a."Personnel_ID", count(*) as check_out_cnt
from "AssetHistory" a
where
  a."EntryType" = 'CheckOut'
group by a."Personnel_ID"

This will count the number of check outs by persons.

--
Thomas Clarke
Cyber Sea Incorporated
Barbados BB11070

web: http://cybersea.biz


[Non-text portions of this message have been removed]



------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links



[Non-text portions of this message have been removed]