I have a database of >20 million records, containing serial numbers of products, in batches of 50,000 in each batch. Sets of these serial numbers are allocated to users (user is a numeric field) and when they are allocated a datetime field records that allocation date/time.

 

I'm trying to report the start and end serial numbers allocated to a user on each day (there could be multiple allocations to multiple uses on any given day) so I'm grouping the allocation date field by 'second'.

 

I need to show the min and max serial numbers allocated to a user on a given day and using minimum({serial_no},{allocation_date}) and maximum({serial_no},{allocation_date})  for this. So far so good -

 

My problem is, if in a batch of 50,000 serial number records the first 5000 are allocated to user 1 and the next 4000 to user 2, followed by the next 3000 to user 1 again (all on the same day) the report shows that user 2 has 4000 (correctly) but user 1 is shown as having 12,000, i.e. the report is ignoring the allocation to user 2 in the middle.

 

Hopefully I've explained my query clearly  - if anyone can offer me some clues as to how I can show these allocations correctly I'd be very grateful

 

Thanks

 

PeterH

 

The report is grouping by user, then stock type then allocation date, then batch_No

 

It works perfectly when the allocations are all done on the same day but if, in the example above (12000 serial numbers), the first 5000 and last 3000 are allocated to user 1 on day 1 and the 4000 in the middle of the serial number range are allocated to user 2 on day 2 I get the problem where the report shows user 1 having all 12000 incorrectly allocated to him (on day 1) when I try and calculate the start and end serial number of the allocated ranges using minimum and maximum formulae,  but user 2 is shown as having the middle 4000 correctly allocated to him on day 2.

 

What I want to report is:

 

User 1-   Start Serial 0001       End Serial 5000     Qty 5000  Date 12/12/12

User 1-   Start Serial 9001       End serial 12000    Qty 3000  Date 12/12/12

 

User 2-   Start Serial 5001       End Serial 9000     Qty  4000 Date  13/12/12

 

 

What I currently end up with is: