Number of days

  • Thread starter Thread starter James
  • Start date Start date
J

James

Got a question that has been bugging me for days and need a bit of
inspriation. The data I have is this:

Material : 123
Date: 1/1/05
Amount: 3

Very large table with multiple dates, materials and usage.

Basically what I need to know is how much of a particular material was used
over X number of days. I know I can start from the beginning date, count X
days and sum. What I really need though is the MAX usage over any period (X
is lead time) not structured periods. Any ideas?

Thanks, James
 
Your requirement is not clearly stated.

Do you want the Max usage for each material within a date range? Do you have
multiple records for a material on the same date?

For 123, could you have three records for 1/1/05 with amount values of 1, 7, and
9? Or do you have only a maximum of one record per material and date combination?

SELECT Material, Max(Amount) as MaxAmount
FROM YourTable
Where [Date] Between #1/1/05# and #1/31/05#
GROUP BY Material

Or, for example, are you trying to find the three-day period within the month
(or some other designated period) where the maximum amount of material 123 was
used? This is of course much more complicated. You would want to return the
material, the begin-date (and end date), and the sum of the material used over
the specified period.

As a starter, I would build a query something like the following - untested.

SELECT Material, [Date],
(SELECT Sum(Amount)
FROM Table as Tmp
WHERE tmp.[Date] between M.Date and M.Date + 3
And Tmp.Material = M.material and
tmp.[Date] = M.[Date]) as MultiDayAmount
FROM Table as M
WHERE M.[Date] Between #1/1/05# and #1/28/05#

IF that worked, then I would use that query as the basis to get the Maximum.
 
John Spencer (MVP) said:
Your requirement is not clearly stated.

Do you want the Max usage for each material within a date range? Do you
have
multiple records for a material on the same date?

For 123, could you have three records for 1/1/05 with amount values of 1,
7, and
9? Or do you have only a maximum of one record per material and date
combination?

SELECT Material, Max(Amount) as MaxAmount
FROM YourTable
Where [Date] Between #1/1/05# and #1/31/05#
GROUP BY Material

Or, for example, are you trying to find the three-day period within the
month
(or some other designated period) where the maximum amount of material 123
was
used? This is of course much more complicated. You would want to return
the
material, the begin-date (and end date), and the sum of the material used
over
the specified period.

As a starter, I would build a query something like the following -
untested.

SELECT Material, [Date],
(SELECT Sum(Amount)
FROM Table as Tmp
WHERE tmp.[Date] between M.Date and M.Date + 3
And Tmp.Material = M.material and
tmp.[Date] = M.[Date]) as MultiDayAmount
FROM Table as M
WHERE M.[Date] Between #1/1/05# and #1/28/05#

IF that worked, then I would use that query as the basis to get the
Maximum.

Got a question that has been bugging me for days and need a bit of
inspriation. The data I have is this:

Material : 123
Date: 1/1/05
Amount: 3

Very large table with multiple dates, materials and usage.

Basically what I need to know is how much of a particular material was
used
over X number of days. I know I can start from the beginning date, count
X
days and sum. What I really need though is the MAX usage over any period
(X
is lead time) not structured periods. Any ideas?

Thanks, James
Click to expand...
Click to expand...

Think I got it now. Thanks. Using the example you provided I can take each
individual issue event and add in the issue events for the next X number of
days to compare to my Lead Time vs. reorder triggers. This is my final
version:

SELECT M.Material, M.Date, (SELECT Sum(Quantity)
FROM MyTable as Tmp
WHERE tmp.[Date] between M.Date and M.Date + [Lead Time]
And Tmp.Material = M.material) AS MultiDayAmount
FROM MyTable AS M;
 
Back
Top