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

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

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
>
>