Grouping on Dates

[WeeDram]

I am attempting to count the number of times we produce a product, and the average quantity in each production run.

I have the detail by production date, but the issue I can't resolve is how to count production on consecutive days as 1 entry. This is to cover where we begin production late one day, and finish the following day (or even the day after!).

An added complication, is where we start on a Friday, and finish on a Monday.

Can anyone suggest how I could query this data, grouping as described? (the table contains hundreds of products - folowing is an example of one product, but if there is a solution, I'm assuming it will work for all products.)

Product Production Date Quantity
A123456 10/2/06 80
A123456 13/2/06 80
A123456 11/5/06 450
A123456 02/8/06 550
A123456 03/8/06 25

Lines 1 & 2 should be counted as 1 and the quantity = 160
Line 3 should be counted as 1 and the quantity = 450
Lines 4 & 5 should be counted as 1 and the quantity = 575

Hope this makes sense...!

Thanks

Peter (need a wee Dram!)

 

[rjoubert]

An added complication, is where we start on a Friday, and finish on a Monday.

What about the rest of the week (Tuesday thru Thursday)? Do you mean your week starts on Friday (and ends Thursday)?

Assuming that is what you mean, try this query...

SELECT DatePart("ww",[ProductionDate],6) AS WeekNumber, Sum(Quantity) AS SumOfQuantity
FROM ProductTable
GROUP BY DatePart("ww",[ProductionDate],6);

 

[WeeDram]

No, I meant started production on a Friday and finished on a Monday - we work a normal Mon -Fri week and produce every day....

 

[rjoubert]

So do you want to group from Sunday to Saturday in the same week, or Friday to Thursday, or Monday to Sunday?

 

[randy700]

Product Production Date Quantity
A123456 10/2/06 80
A123456 13/2/06 80
A123456 11/5/06 450
A123456 02/8/06 550
A123456 03/8/06 25

Lines 1 & 2 should be counted as 1 and the quantity = 160
Line 3 should be counted as 1 and the quantity = 450
Lines 4 & 5 should be counted as 1 and the quantity = 575

How can I tell, from looking at this data, which lines should be added together?


Randy

 

[WeeDram]

Randy,

It's based on the production date - consecutive days should be counted as 1 including weekends (ie start on a Friday, finish on a Monday....

 

[WeeDram]

Randy,

I've now managed to get a solution for my problem.

Thanks very much for taking the time to look at my issue, and I've no doubt you will probably have to help me again in the future.

Thanks

Peter

 

[randy700]

How about posting the solution... it might help others.

Randy