Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 3 of 3 total |
Numbering records by group |
Tue, Dec 9 2008 8:49 PM | Permanent Link |
"Al Vas" | Hi,
I have an issue Im trying to resolve and its getting to the limits of my SQL capacity (which isnt very far). Any help appreciated. DBISAM V3.30. I have the following table result: Company Booking No Shift Date book1 book2 book3 book4 book5 book6 book7 seqnbr A 1345 17/07/08 1345 1 A 1345 17/07/08 1350 2 A 1345 17/07/08 1355 3 A 1345 18/07/08 1359 1359 1 A 1345 18/07/08 1361 2 A 1345 19/07/08 1372 1 A 1345 22/07/08 1376 1 B 1345 18/07/08 1380 1 B 1345 18/07/08 1381 2 B 1345 20/07/08 1382 1 B 1345 21/07/08 1384 1 B 1345 21/07/08 1391 2 B 1345 21/07/08 1392 3 B 1345 22/07/08 1395 1 Essentially the part I havent worked out is the last column seqnbr. What I want to achieve is within each company, for each shift_date I want a running count, so there are 3 records for the 17th, want seqnbr to show '1' for the first record, '2' for the second, '3' for the third etc, then restart for the 18th and so on. Hope I have explained it clearly enough, is there a way to do this? I have toyed with groupings, runsums etc to no avail. Thanks Alex |
Wed, Dec 10 2008 9:37 AM | Permanent Link |
"John Hay" | Al
I think you need a script. Something like the following should work. SELECT Company, ShiftDate,BookingNo, book1,book2,book3,book4,book5,book6,book7, RUNSUM(1) as Cnt INTO Memory Temp FROM Table GROUP BY Company, ShiftDate,BookingNo, book1,book2,book3,book4,book5,book6,book7 ORDER BY Company, ShiftDate,BookingNo, book1,book2,book3,book4,book5,book6,book7; SELECT Company,ShiftDate,MIN(Cnt) AS MinCnt INTO Memory Temp1FROM Memory Temp GROUP BY Company,ShiftDate; SELECT Company, ShiftDate,BookingNo, book1,book2,book3,book4,book5,book6,book7, Cnt-MinCnt+1 As Seqnbr FROM Memory Temp JOIN Memory Temp1 ON Temp.Company=Temp1.Company AND Temp.ShiftDate=Temp1.ShiftDate ORDER BY Company,ShiftDate,Seqnbr John |
Wed, Dec 10 2008 6:54 PM | Permanent Link |
"Al Vas" | Worked a treat. A thousand thankyous
Alex "John Hay" <j.haywithoutdot@crbsolutionsremoveallthis.co.uk> wrote in message news:3DE0E2B3-4239-4A3D-AA68-71A5E1BD85AB@news.elevatesoft.com... > Al > > I think you need a script. Something like the following should work. > > SELECT Company, ShiftDate,BookingNo, > book1,book2,book3,book4,book5,book6,book7, RUNSUM(1) as Cnt INTO Memory > Temp > FROM Table > GROUP BY Company, ShiftDate,BookingNo, > book1,book2,book3,book4,book5,book6,book7 > ORDER BY Company, ShiftDate,BookingNo, > book1,book2,book3,book4,book5,book6,book7; > SELECT Company,ShiftDate,MIN(Cnt) AS MinCnt INTO Memory Temp1FROM Memory > Temp > GROUP BY Company,ShiftDate; > SELECT Company, ShiftDate,BookingNo, > book1,book2,book3,book4,book5,book6,book7, Cnt-MinCnt+1 As Seqnbr > FROM Memory Temp > JOIN Memory Temp1 ON Temp.Company=Temp1.Company AND > Temp.ShiftDate=Temp1.ShiftDate > ORDER BY Company,ShiftDate,Seqnbr > > > John > > |
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 |