[thelist] db2 query question

Dan McCullough dan.mccullough at gmail.com
Thu Mar 2 09:33:56 CST 2006


Try this, no guarantees

SELECT B.AREA, COUNT(*) FROM TABLE1 A, TABLE1 B WHERE A.AREA = 3 AND
B.ITEM = A.ITEM AND B.DATE >= A.DATE AND B.AREA > A.AREA GROUP BY
B.AREA

On 3/2/06, Joe S <smokenjoe2 at gmail.com> wrote:
> I have a query where I am trying to get the count of items in a certain
> area.  So I have a table that contains the items id, the time/date, the area
> number.  Now the table is setup so that each area is marked with a time/date
> on when it entered and left, so you will have multiple entries for one item.
>
> 655391 0 2005-12-22 15:59:00 32 2005-12-22 16:21:00 76 0 1.00 C 1940-01-01
> 655391 1 2006-01-19 16:44:00 5 2006-01-19 16:44:00 5 0 1.00 C 1940-01-01
> 655391 2 2005-12-22 16:21:00 76 2006-01-31 15:45:00 72 0 29.00 C 1940-01-01
> 655391 3 2006-02-02 13:32:00 101 2006-02-03 11:44:00 32 0 1.00 C 1940-01-01
> 655391 4 2006-02-03 15:28:00 25 2006-02-03 20:56:00 26 0 0 C 1940-01-01
> 655391 5 2006-02-08 15:36:00 12 2006-02-08 15:36:00 12 0 1.00 C 1940-01-01
> 655391 6 2006-02-08 16:46:00 67 2006-02-08 16:46:00 67 0 1.00 C 1940-01-01
> 655391 7 2006-02-09 07:08:00 5 2006-02-14 13:51:00 5 0 4.00 C 1940-01-01
> 655391 8 2006-02-14 15:11:00 18 2006-02-14 15:11:00 18 0 1.00 C 1940-01-01
>
> Now here is a little bit of a tricky part, I dont need counts for 0,1,2 I
> just need to find how many items are in 3 that left 2 on x date and so on
> and so forth until your past 8.  I do not need to know where the items have
> been I need to know where there are now.
> --
>
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
>
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>



More information about the thelist mailing list