Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 8 of 8 total |
Result including count for that record. |
Sun, Sep 28 2014 9:32 PM | Permanent Link |
Adam H. | 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 |
Mon, Sep 29 2014 4:26 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, Sep 29 2014 6:42 PM | Permanent Link |
Adam H. | 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. |
Tue, Sep 30 2014 3:38 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, Sep 30 2014 4:09 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Oct 1 2014 3:11 AM | Permanent Link |
Adam H. | 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. |
Wed, Oct 1 2014 3:51 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Oct 1 2014 10:01 PM | Permanent Link |
Adam H. | > 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!!!! |
This web page was last updated on Monday, April 29, 2024 at 05:23 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |