Hi,

I'm not sure if this is possible with DBISam and SQL, but thought I'd
throw it out there anyway.

The end goal is to return a result where it excludes the first two
records that match for a particular field.

I thought I might be able to implement the Runsum function to achieve
this, but now I'm not so sure because I can't seem to get Runsum to
reset - which is what I really need.

As an example, let's say I have a list of staff and their workhours. I
want a result to show me all those who have worked overtime, but exclude
the first two overtime days. Overtime is when time exceeds 38hrs

Raw Data:

Name   Day   Hours
Bob   1   40
Bob   2   38
Bob   3   40
Bob   4   38
Bob   5   38
Bob   6   40
Ron   1   38
Ron   2   40
Ron   3   40
Ron   4   38
Ron   5   38
Ron   6   38
Tim   1   38
Tim   2   40
Tim   3   40
Tim   4   42
Tim   5   38
Tim   6   40


The result I would be chasing would be:
Name   Day   Hours   Val
Bob   1   40   1
Bob   3   40   2   
Bob   6   40   3
Ron   2   40   40
Ron   3   40   40
Tim   4   42   3
Tim   6   40   4

(Where Val is the number of times it has occurred at that time).
I would just then filter out the records where Val < 3.



If I do:

Select Name, Day, Hours, Runsum(1) as Val
From Table
where Hours > 38
Group by Name, Day
Order by Name, Day

.... it won't reset for each person, because the grouping is per name and
day. The result I will get is:

Name   Day   Hours   Val
Bob   

Can anyone see if this is possible via SQL, or am I going to have to
code this manually by creating a result with a blank field, and then
manually doing a while not eof populate script...

Cheers

Adam

Adam

I'm confused by what you're actually asking for (others are probably brighter than me and won't be) Your requirement

>The end goal is to return a result where it excludes the first two
>records that match for a particular field.

And the examples you quote don't seem to match. Reading the above, and just for Bob I would have expected you to want

Name    Day    Hours    Val
Bob        6        40        2

ie exclude Bob 1 & Bob 3 as the first two that are overtime and exclude Bob's 2,4 & 5 because they aren't overtime.

Can you clarify please.

I do have an idea that may work using a script but I need to be sure

Roy Lambert

Hi Roy,

> I'm confused by what you're actually asking for (others are probably
brighter than me and won't be) Your requirement

Sorry for the confusion. You are right with your understanding. (For Bob
only) - except that I'm chasing it for everyone who has more than 2
overtimes.

Such as:

Name    Day    Hours   Val
Bob       6       40   3
Tim       4       42   3
Tim       6       40    4


(I'm looking to have Val show the number of overtimes to that record. 1
and 2 are hidden by a filter in the end).

I figure that I can ignore the whole eliminate 1 and 2 to start with. If
I can just get a result set that returns all overtimes with their count
such as the below I can figure it out from there:

Name    Day    Hours    Val
Bob       1       40       1   
Bob       3       40       2      
Bob       6       40       3
Ron       2       40       40
Ron       3       40       40
Tim       4       42       3
Tim       6       40       4


Where I'm strugling is figuring out how to populate the Val field like
that...

Cheers

Adam.

Adam


My first idea didn't work out - I'll see if I can come up with something else.

Where is John Hay when you need him?

Roy Lambert

Adam


Brain is not up to this task. I think I'd create a memory table with the overtime records and then delete from that using a table.

If you want to explore a bit further then there's this post

Message-ID: <0B509261-C504-46EE-9953-60A2A912816A@news.elevatesoft.com>
From: "Al Vas" <noreply@noreply.com>
Newsgroups: elevatesoft.public.dbisam.sql
Subject: Numbering records by group
Date: Wed, 10 Dec 2008 12:49:38 +1100

which might allow you to add row numbers into a temporary table and then its easy to delete anything where the number is less than 3


Roy Lambert

Hi Roy,

> Brain is not up to this task.

You and I both.

> I think I'd create a memory table with the overtime records and then delete from that using a table.

I always thought that a memory table was the go, but I was hoping to
populate the number of instances / count per name within SQL.

I'm taking a look at that post by John Hay - trying to wrap my head
around it. Maybe it might be something for the morning when my head is
clear.

Thanks for the help.

Cheers

Adam.

Adam

>I always thought that a memory table was the go, but I was hoping to
>populate the number of instances / count per name within SQL.

You could sort of do it all in SQL by using a user defined function, but I'm pretty sure it would be slower than creating the memory table and walking it.

>I'm taking a look at that post by John Hay - trying to wrap my head
>around it. Maybe it might be something for the morning when my head is
>clear.

Hmmm. I often feel that those sort of chunks of SQL are best addressed after the second bottle of wine.

Roy

> Hmmm. I often feel that those sort of chunks of SQL are best addressed after the second bottle of wine.

That's where I've been going wrong all these years!!!!