Backlog Query

  • Thread starter Thread starter John Scanlon
  • Start date Start date
J

John Scanlon

I need to create a query which provides a count of the number of orders
which are currently, or were the end of every week backlogged. The database
I am querying has open date, and closed date, but the goal is to produce a
chart (weekly over the last 52 weeks) which shows how many were open at the
end of each week, but had not been closed. So, if Sunday morning at 12:00
am signifies the begining of the weekly measurement period, I need each
backlogged order to be counted each week that it is open before that sunday
morning, but not closed before it.

Examples???? Help????


Thanks!
 
Hi,


You want to count the number of records having their closed date not in
the same week than their open date?


SELECT DatePart("ww", openDate), COUNT(*)
FROM myTable
WHERE (DatePart("ww", openDate) <> DatePart("ww", closedDate))
OR (closedDate Is Null)
GROUP BY DatePart("ww", openDate)


You may have to add -0.5 to the dates, as argument of the DatePart, if
the starting point of the week is at midday, rather than at midnight.



Hoping it may help,
Vanderghast, Access MVP
 
Hi Michel,

Apologies in advance if I am wrong here..

An order gets opened in week 1
but does not get closed until week 3.

In week 2 that order is not part of
week 2 group so it appears to me it would not
be counted as open/not closed in your query
for week 2?

I could be wrong as always...

I always look forward to your answers to
interesting problems like this one, and often ask
myself...
"what would Michel (John(s)/Duane/Tom) do..."

I thought you might create a parametized query
for the "week ranges" working off an Iota-like
table going from 1 to 52 to produce something like:

qryRange
WeekNum PeriodStart WeekEnd
1 [Enter Start] [Enter Start] + WeekNum*7
2 [Enter Start] [Enter Start] + WeekNum*7
3 [Enter Start] [Enter Start] + WeekNum*7
.....
52 [Enter Start] [Enter Start] + WeekNum*7

then "join" (haven't finished thinking this through)
to a group by query:

SELECT OrderID, Max(OpenDate) MO,
Max(ClosedDate) MC
FROM myTable
GROUP BY OrderID;

then getting count by
"negative summing" the condition that
(MO >= PeriodStart
AND MC > WeekEnd)
Group By WeekNum

I have to get into work so apologise again
that have not thought this through completely.

Gary Walter
 
Hi,


It is possible, indeed, that your solution is the one the OP is looking
for. I initially though that the question was about "how may unfinished
stuff ... of jobs started this week... were left unfinished at the end of
the week", without ANY consideration, ever, about any stuff left over from
previous week(s). If it was so, then, the date at which the job is finished
does not matter except to answer to the simple question, "is it of the same
week it started". If so, removing the records where the ending date is in
the same week than the starting date, leave us with the jobs with a
"problem", and thus, simply counting those problems, week by week, was the
simplicity on earth. Generally, indeed, if we think in term of maintenance
contract, the problem would be more like "48 hours after we open the case",
or things like that, but the original question seemed to me to be a simpler
one of really fixed calendar scheduled, so, quite happy with that simplified
approach, I supplied a simple group by, with a simple criteria... :-)



Vanderghast, Access MVP




Gary Walter said:
Hi Michel,

Apologies in advance if I am wrong here..

An order gets opened in week 1
but does not get closed until week 3.

In week 2 that order is not part of
week 2 group so it appears to me it would not
be counted as open/not closed in your query
for week 2?

I could be wrong as always...

I always look forward to your answers to
interesting problems like this one, and often ask
myself...
"what would Michel (John(s)/Duane/Tom) do..."

I thought you might create a parametized query
for the "week ranges" working off an Iota-like
table going from 1 to 52 to produce something like:

qryRange
WeekNum PeriodStart WeekEnd
1 [Enter Start] [Enter Start] + WeekNum*7
2 [Enter Start] [Enter Start] + WeekNum*7
3 [Enter Start] [Enter Start] + WeekNum*7
....
52 [Enter Start] [Enter Start] + WeekNum*7

then "join" (haven't finished thinking this through)
to a group by query:

SELECT OrderID, Max(OpenDate) MO,
Max(ClosedDate) MC
FROM myTable
GROUP BY OrderID;

then getting count by
"negative summing" the condition that
(MO >= PeriodStart
AND MC > WeekEnd)
Group By WeekNum

I have to get into work so apologise again
that have not thought this through completely.

Gary Walter

Michel Walsh said:
Hi,


You want to count the number of records having their closed date not in
the same week than their open date?


SELECT DatePart("ww", openDate), COUNT(*)
FROM myTable
WHERE (DatePart("ww", openDate) <> DatePart("ww", closedDate))
OR (closedDate Is Null)
GROUP BY DatePart("ww", openDate)


You may have to add -0.5 to the dates, as argument of the DatePart, if
the starting point of the week is at midday, rather than at midnight.



Hoping it may help,
Vanderghast, Access MVP


at
the
Click to expand...
Click to expand...
 
just in case this was close...
condition should have been
(MO >= PeriodStart
AND ((MC > WeekEnd) OR (MC IS NULL))
Group By WeekNum
Click to expand...


Gary Walter said:
Hi Michel,

Apologies in advance if I am wrong here..

An order gets opened in week 1
but does not get closed until week 3.

In week 2 that order is not part of
week 2 group so it appears to me it would not
be counted as open/not closed in your query
for week 2?

I could be wrong as always...

I always look forward to your answers to
interesting problems like this one, and often ask
myself...
"what would Michel (John(s)/Duane/Tom) do..."

I thought you might create a parametized query
for the "week ranges" working off an Iota-like
table going from 1 to 52 to produce something like:

qryRange
WeekNum PeriodStart WeekEnd
1 [Enter Start] [Enter Start] + WeekNum*7
2 [Enter Start] [Enter Start] + WeekNum*7
3 [Enter Start] [Enter Start] + WeekNum*7
....
52 [Enter Start] [Enter Start] + WeekNum*7

then "join" (haven't finished thinking this through)
to a group by query:

SELECT OrderID, Max(OpenDate) MO,
Max(ClosedDate) MC
FROM myTable
GROUP BY OrderID;

then getting count by
"negative summing" the condition that
(MO >= PeriodStart
AND MC > WeekEnd)
Group By WeekNum

I have to get into work so apologise again
that have not thought this through completely.

Gary Walter

Michel Walsh said:
Hi,


You want to count the number of records having their closed date not in
the same week than their open date?


SELECT DatePart("ww", openDate), COUNT(*)
FROM myTable
WHERE (DatePart("ww", openDate) <> DatePart("ww", closedDate))
OR (closedDate Is Null)
GROUP BY DatePart("ww", openDate)


You may have to add -0.5 to the dates, as argument of the DatePart, if
the starting point of the week is at midday, rather than at midnight.



Hoping it may help,
Vanderghast, Access MVP
Click to expand...
Click to expand...
 
Hey guys,

Thanks for the answers, but let me clarify.

If an order is opened on week 1, and not closed till we 5, it should show as
a backlogged item on weeks 1, 2, 3, and 4. It should show as a backlogged
item in week 1 as we did not close the order before the end of week 1, but
it should not show as backlogged at the end of week 5 as we have closed the
order during week 5.

I am fairly knowledgeable of most of the date functions, and already have
queries which fees graphs which will show opened or closed orders by week
ending date (Saturday) for the past 52 weeks. I would consider using the
output date range from the opened or closed queries mentioned here as the
reference dates for a second query which calculates the backlogged events in
each week, but I feel like I am standing to close to the forest right now
and can not see the trees. Sorry for being stupid, but I would not have
asked if I thought it was simple.

Thanks!

John
Michel Walsh said:
Hi,


It is possible, indeed, that your solution is the one the OP is looking
for. I initially though that the question was about "how may unfinished
stuff ... of jobs started this week... were left unfinished at the end of
the week", without ANY consideration, ever, about any stuff left over from
previous week(s). If it was so, then, the date at which the job is finished
does not matter except to answer to the simple question, "is it of the same
week it started". If so, removing the records where the ending date is in
the same week than the starting date, leave us with the jobs with a
"problem", and thus, simply counting those problems, week by week, was the
simplicity on earth. Generally, indeed, if we think in term of maintenance
contract, the problem would be more like "48 hours after we open the case",
or things like that, but the original question seemed to me to be a simpler
one of really fixed calendar scheduled, so, quite happy with that simplified
approach, I supplied a simple group by, with a simple criteria... :-)



Vanderghast, Access MVP




Gary Walter said:
Hi Michel,

Apologies in advance if I am wrong here..

An order gets opened in week 1
but does not get closed until week 3.

In week 2 that order is not part of
week 2 group so it appears to me it would not
be counted as open/not closed in your query
for week 2?

I could be wrong as always...

I always look forward to your answers to
interesting problems like this one, and often ask
myself...
"what would Michel (John(s)/Duane/Tom) do..."

I thought you might create a parametized query
for the "week ranges" working off an Iota-like
table going from 1 to 52 to produce something like:

qryRange
WeekNum PeriodStart WeekEnd
1 [Enter Start] [Enter Start] + WeekNum*7
2 [Enter Start] [Enter Start] + WeekNum*7
3 [Enter Start] [Enter Start] + WeekNum*7
....
52 [Enter Start] [Enter Start] + WeekNum*7

then "join" (haven't finished thinking this through)
to a group by query:

SELECT OrderID, Max(OpenDate) MO,
Max(ClosedDate) MC
FROM myTable
GROUP BY OrderID;

then getting count by
"negative summing" the condition that
(MO >= PeriodStart
AND MC > WeekEnd)
Group By WeekNum

I have to get into work so apologise again
that have not thought this through completely.

Gary Walter

Michel Walsh said:
Hi,


You want to count the number of records having their closed date
Click to expand...
Click to expand...
not
in
Click to expand...
DatePart,
if
Click to expand...
Click to expand...
 
Hi,


Sorry for my simplistic solution... It is hard to gauge the exact
question, and the poster skills, sometimes, with just a few lines of text. I
hope you don't feel offended... I just can't know personally each and every
one asking a question... :-)

In the case of carry over, one of the easiest solution is probably to
make a +1 /-1 cumulative sum. I start it with:


SELECT JobID, StartingDate As TheDay, +1 As TheOp FROM myTable
UNION ALL
SELECT JobID, EndingDate, -1 FROM myTable


would be my data, from now on, in a saved query, QU1, I would then use to
make the running sum over TheDay:



SELECT a.TheDay, SUM(b.TheOp) as ActiveJob
FROM qu1 As a, qu1 As b
WHERE a.TheDay >= b.TheDay
GROUP BY a.TheDay



would supply, day by day, the number of active (or pending, in fact, not
completed, but started) jobs. You can use HAVING to hand pick only those
a.TheDay being a Sunday, or whatever you feel attractive from that point.




Hoping it may help,
Vanderghast, Access MVP



John Scanlon said:
Hey guys,

Thanks for the answers, but let me clarify.

If an order is opened on week 1, and not closed till we 5, it should show as
a backlogged item on weeks 1, 2, 3, and 4. It should show as a backlogged
item in week 1 as we did not close the order before the end of week 1, but
it should not show as backlogged at the end of week 5 as we have closed the
order during week 5.

I am fairly knowledgeable of most of the date functions, and already have
queries which fees graphs which will show opened or closed orders by week
ending date (Saturday) for the past 52 weeks. I would consider using the
output date range from the opened or closed queries mentioned here as the
reference dates for a second query which calculates the backlogged events in
each week, but I feel like I am standing to close to the forest right now
and can not see the trees. Sorry for being stupid, but I would not have
asked if I thought it was simple.

Thanks!

John
Michel Walsh said:
Hi,


It is possible, indeed, that your solution is the one the OP is looking
for. I initially though that the question was about "how may unfinished
stuff ... of jobs started this week... were left unfinished at the end of
the week", without ANY consideration, ever, about any stuff left over from
previous week(s). If it was so, then, the date at which the job is finished
does not matter except to answer to the simple question, "is it of the same
week it started". If so, removing the records where the ending date is in
the same week than the starting date, leave us with the jobs with a
"problem", and thus, simply counting those problems, week by week, was the
simplicity on earth. Generally, indeed, if we think in term of maintenance
contract, the problem would be more like "48 hours after we open the case",
or things like that, but the original question seemed to me to be a simpler
one of really fixed calendar scheduled, so, quite happy with that simplified
approach, I supplied a simple group by, with a simple criteria... :-)



Vanderghast, Access MVP




Gary Walter said:
Hi Michel,

Apologies in advance if I am wrong here..

An order gets opened in week 1
but does not get closed until week 3.

In week 2 that order is not part of
week 2 group so it appears to me it would not
be counted as open/not closed in your query
for week 2?

I could be wrong as always...

I always look forward to your answers to
interesting problems like this one, and often ask
myself...
"what would Michel (John(s)/Duane/Tom) do..."

I thought you might create a parametized query
for the "week ranges" working off an Iota-like
table going from 1 to 52 to produce something like:

qryRange
WeekNum PeriodStart WeekEnd
1 [Enter Start] [Enter Start] + WeekNum*7
2 [Enter Start] [Enter Start] + WeekNum*7
3 [Enter Start] [Enter Start] + WeekNum*7
....
52 [Enter Start] [Enter Start] + WeekNum*7

then "join" (haven't finished thinking this through)
to a group by query:

SELECT OrderID, Max(OpenDate) MO,
Max(ClosedDate) MC
FROM myTable
GROUP BY OrderID;

then getting count by
"negative summing" the condition that
(MO >= PeriodStart
AND MC > WeekEnd)
Group By WeekNum

I have to get into work so apologise again
that have not thought this through completely.

Gary Walter

Hi,


You want to count the number of records having their closed date
Click to expand...
not
in
the same week than their open date?


SELECT DatePart("ww", openDate), COUNT(*)
FROM myTable
WHERE (DatePart("ww", openDate) <> DatePart("ww", closedDate))
OR (closedDate Is Null)
GROUP BY DatePart("ww", openDate)


You may have to add -0.5 to the dates, as argument of the
Click to expand...
DatePart,
if
the starting point of the week is at midday, rather than at midnight.



Hoping it may help,
Vanderghast, Access MVP


I need to create a query which provides a count of the number of orders
which are currently, or were the end of every week backlogged. The
database
I am querying has open date, and closed date, but the goal is to produce a
chart (weekly over the last 52 weeks) which shows how many were
Click to expand...
open
at
the
end of each week, but had not been closed. So, if Sunday morning
Click to expand...
at
12:00
am signifies the begining of the weekly measurement period, I need each
backlogged order to be counted each week that it is open before that
sunday
morning, but not closed before it.

Examples???? Help????


Thanks!
Click to expand...
Click to expand...
Click to expand...
 
One other way would be to create a
"tblNum" , one indexed field "Num"
from 1 to 52.

qryRange

PARAMETERS [Enter Starting Sunday Date] DateTime;
SELECT
tblNum.Num AS WeekNum,
CDate([Enter Starting Sunday Date])+([tblNum].[Num]*7) AS WeekEnd
FROM tblNum
ORDER BY tblNum.Num;

if enter 1/5/03, get

WeekNum WeekEnd
1 1/12/2003
2 1/19/2003
3 1/26/2003
4 2/2/2003
5 2/9/2003
........
51 12/28/2003
52 1/4/2004

You don't give structure of your table,
but assuming something like:

tblOrders

OrderId OpenDate ClosedDate
1 12/5/2002 2/4/2003
2 12/15/2002 12/18/2002
3 1/4/2003 1/8/2003
4 1/5/2003
5 1/19/2003 1/19/2003
6 2/3/2003 2/12/2003
7 2/5/2003 3/1/2003
8 12/31/2003

qryOrdersPreSum

SELECT
qryRange.WeekNum,
[WeekEnd]-7 AS WeekStart,
qryRange.WeekEnd,
tblOrders.OrderId,
tblOrders.OpenDate,
tblOrders.ClosedDate
FROM qryRange, tblOrders
WHERE (((tblOrders.OpenDate)<[WeekEnd])
AND
((tblOrders.ClosedDate)>=[WeekEnd]
Or (tblOrders.ClosedDate) Is Null))
ORDER BY qryRange.WeekNum, tblOrders.OrderId;

WeekNum WeekStart WeekEnd OrderId OpenDate ClosedDate
1 1/5/2003 1/12/2003 1 12/5/2002 2/4/2003
1 1/5/2003 1/12/2003 4 1/5/2003
2 1/12/2003 1/19/2003 1 12/5/2002 2/4/2003
2 1/12/2003 1/19/2003 4 1/5/2003
3 1/19/2003 1/26/2003 1 12/5/2002 2/4/2003
3 1/19/2003 1/26/2003 4 1/5/2003
4 1/26/2003 2/2/2003 1 12/5/2002 2/4/2003
4 1/26/2003 2/2/2003 4 1/5/2003
5 2/2/2003 2/9/2003 4 1/5/2003
5 2/2/2003 2/9/2003 6 2/3/2003 2/12/2003
5 2/2/2003 2/9/2003 7 2/5/2003 3/1/2003
6 2/9/2003 2/16/2003 4 1/5/2003
6 2/9/2003 2/16/2003 7 2/5/2003 3/1/2003
7 2/16/2003 2/23/2003 4 1/5/2003
7 2/16/2003 2/23/2003 7 2/5/2003 3/1/2003
8 2/23/2003 3/2/2003 4 1/5/2003
9 3/2/2003 3/9/2003 4 1/5/2003
......
51 12/21/2003 12/28/2003 4 1/5/2003
52 12/28/2003 1/4/2004 4 1/5/2003
52 12/28/2003 1/4/2004 8 12/31/2003

From this you probably can see that
all you have to do is group this by WeekNum
and count the OrderId's.

John Scanlon said:
Hey guys,

Thanks for the answers, but let me clarify.

If an order is opened on week 1, and not closed till we 5, it should show as
a backlogged item on weeks 1, 2, 3, and 4. It should show as a backlogged
item in week 1 as we did not close the order before the end of week 1, but
it should not show as backlogged at the end of week 5 as we have closed the
order during week 5.

I am fairly knowledgeable of most of the date functions, and already have
queries which fees graphs which will show opened or closed orders by week
ending date (Saturday) for the past 52 weeks. I would consider using the
output date range from the opened or closed queries mentioned here as the
reference dates for a second query which calculates the backlogged events in
each week, but I feel like I am standing to close to the forest right now
and can not see the trees. Sorry for being stupid, but I would not have
asked if I thought it was simple.

Thanks!

John
Michel Walsh said:
Hi,


It is possible, indeed, that your solution is the one the OP is looking
for. I initially though that the question was about "how may unfinished
stuff ... of jobs started this week... were left unfinished at the end of
the week", without ANY consideration, ever, about any stuff left over from
previous week(s). If it was so, then, the date at which the job is finished
does not matter except to answer to the simple question, "is it of the same
week it started". If so, removing the records where the ending date is in
the same week than the starting date, leave us with the jobs with a
"problem", and thus, simply counting those problems, week by week, was the
simplicity on earth. Generally, indeed, if we think in term of maintenance
contract, the problem would be more like "48 hours after we open the case",
or things like that, but the original question seemed to me to be a simpler
one of really fixed calendar scheduled, so, quite happy with that simplified
approach, I supplied a simple group by, with a simple criteria... :-)



Vanderghast, Access MVP




Gary Walter said:
Hi Michel,

Apologies in advance if I am wrong here..

An order gets opened in week 1
but does not get closed until week 3.

In week 2 that order is not part of
week 2 group so it appears to me it would not
be counted as open/not closed in your query
for week 2?

I could be wrong as always...

I always look forward to your answers to
interesting problems like this one, and often ask
myself...
"what would Michel (John(s)/Duane/Tom) do..."

I thought you might create a parametized query
for the "week ranges" working off an Iota-like
table going from 1 to 52 to produce something like:

qryRange
WeekNum PeriodStart WeekEnd
1 [Enter Start] [Enter Start] + WeekNum*7
2 [Enter Start] [Enter Start] + WeekNum*7
3 [Enter Start] [Enter Start] + WeekNum*7
....
52 [Enter Start] [Enter Start] + WeekNum*7

then "join" (haven't finished thinking this through)
to a group by query:

SELECT OrderID, Max(OpenDate) MO,
Max(ClosedDate) MC
FROM myTable
GROUP BY OrderID;

then getting count by
"negative summing" the condition that
(MO >= PeriodStart
AND MC > WeekEnd)
Group By WeekNum

I have to get into work so apologise again
that have not thought this through completely.

Gary Walter

Hi,


You want to count the number of records having their closed date
Click to expand...
not
in
the same week than their open date?


SELECT DatePart("ww", openDate), COUNT(*)
FROM myTable
WHERE (DatePart("ww", openDate) <> DatePart("ww", closedDate))
OR (closedDate Is Null)
GROUP BY DatePart("ww", openDate)


You may have to add -0.5 to the dates, as argument of the
Click to expand...
DatePart,
if
the starting point of the week is at midday, rather than at midnight.



Hoping it may help,
Vanderghast, Access MVP


I need to create a query which provides a count of the number of orders
which are currently, or were the end of every week backlogged. The
database
I am querying has open date, and closed date, but the goal is to produce a
chart (weekly over the last 52 weeks) which shows how many were open at
the
end of each week, but had not been closed. So, if Sunday morning at 12:00
am signifies the begining of the weekly measurement period, I need each
backlogged order to be counted each week that it is open before that
sunday
morning, but not closed before it.

Examples???? Help????


Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Thanks to both of you for all your assistance. Excellent suggestions which
I believe I will be able to utilize in my work. I manage a support center
and have produced many reports & charts for measurig the teams perfomance.
Tracking backlog in a way which is both accurate the day measured as well as
accurate if measured for the same period 1.5 years later is important. With
your input I think I may be able to get there!

Thanks again!


Gary Walter said:
One other way would be to create a
"tblNum" , one indexed field "Num"
from 1 to 52.

qryRange

PARAMETERS [Enter Starting Sunday Date] DateTime;
SELECT
tblNum.Num AS WeekNum,
CDate([Enter Starting Sunday Date])+([tblNum].[Num]*7) AS WeekEnd
FROM tblNum
ORDER BY tblNum.Num;

if enter 1/5/03, get

WeekNum WeekEnd
1 1/12/2003
2 1/19/2003
3 1/26/2003
4 2/2/2003
5 2/9/2003
.......
51 12/28/2003
52 1/4/2004

You don't give structure of your table,
but assuming something like:

tblOrders

OrderId OpenDate ClosedDate
1 12/5/2002 2/4/2003
2 12/15/2002 12/18/2002
3 1/4/2003 1/8/2003
4 1/5/2003
5 1/19/2003 1/19/2003
6 2/3/2003 2/12/2003
7 2/5/2003 3/1/2003
8 12/31/2003

qryOrdersPreSum

SELECT
qryRange.WeekNum,
[WeekEnd]-7 AS WeekStart,
qryRange.WeekEnd,
tblOrders.OrderId,
tblOrders.OpenDate,
tblOrders.ClosedDate
FROM qryRange, tblOrders
WHERE (((tblOrders.OpenDate)<[WeekEnd])
AND
((tblOrders.ClosedDate)>=[WeekEnd]
Or (tblOrders.ClosedDate) Is Null))
ORDER BY qryRange.WeekNum, tblOrders.OrderId;

WeekNum WeekStart WeekEnd OrderId OpenDate ClosedDate
1 1/5/2003 1/12/2003 1 12/5/2002 2/4/2003
1 1/5/2003 1/12/2003 4 1/5/2003
2 1/12/2003 1/19/2003 1 12/5/2002 2/4/2003
2 1/12/2003 1/19/2003 4 1/5/2003
3 1/19/2003 1/26/2003 1 12/5/2002 2/4/2003
3 1/19/2003 1/26/2003 4 1/5/2003
4 1/26/2003 2/2/2003 1 12/5/2002 2/4/2003
4 1/26/2003 2/2/2003 4 1/5/2003
5 2/2/2003 2/9/2003 4 1/5/2003
5 2/2/2003 2/9/2003 6 2/3/2003 2/12/2003
5 2/2/2003 2/9/2003 7 2/5/2003 3/1/2003
6 2/9/2003 2/16/2003 4 1/5/2003
6 2/9/2003 2/16/2003 7 2/5/2003 3/1/2003
7 2/16/2003 2/23/2003 4 1/5/2003
7 2/16/2003 2/23/2003 7 2/5/2003 3/1/2003
8 2/23/2003 3/2/2003 4 1/5/2003
9 3/2/2003 3/9/2003 4 1/5/2003
.....
51 12/21/2003 12/28/2003 4 1/5/2003
52 12/28/2003 1/4/2004 4 1/5/2003
52 12/28/2003 1/4/2004 8 12/31/2003

From this you probably can see that
all you have to do is group this by WeekNum
and count the OrderId's.

John Scanlon said:
Hey guys,

Thanks for the answers, but let me clarify.

If an order is opened on week 1, and not closed till we 5, it should show as
a backlogged item on weeks 1, 2, 3, and 4. It should show as a backlogged
item in week 1 as we did not close the order before the end of week 1, but
it should not show as backlogged at the end of week 5 as we have closed the
order during week 5.

I am fairly knowledgeable of most of the date functions, and already have
queries which fees graphs which will show opened or closed orders by week
ending date (Saturday) for the past 52 weeks. I would consider using the
output date range from the opened or closed queries mentioned here as the
reference dates for a second query which calculates the backlogged events in
each week, but I feel like I am standing to close to the forest right now
and can not see the trees. Sorry for being stupid, but I would not have
asked if I thought it was simple.

Thanks!

John
Michel Walsh said:
Hi,


It is possible, indeed, that your solution is the one the OP is looking
for. I initially though that the question was about "how may unfinished
stuff ... of jobs started this week... were left unfinished at the end of
the week", without ANY consideration, ever, about any stuff left over from
previous week(s). If it was so, then, the date at which the job is finished
does not matter except to answer to the simple question, "is it of the same
week it started". If so, removing the records where the ending date is in
the same week than the starting date, leave us with the jobs with a
"problem", and thus, simply counting those problems, week by week, was the
simplicity on earth. Generally, indeed, if we think in term of maintenance
contract, the problem would be more like "48 hours after we open the case",
or things like that, but the original question seemed to me to be a simpler
one of really fixed calendar scheduled, so, quite happy with that simplified
approach, I supplied a simple group by, with a simple criteria... :-)



Vanderghast, Access MVP




Hi Michel,

Apologies in advance if I am wrong here..

An order gets opened in week 1
but does not get closed until week 3.

In week 2 that order is not part of
week 2 group so it appears to me it would not
be counted as open/not closed in your query
for week 2?

I could be wrong as always...

I always look forward to your answers to
interesting problems like this one, and often ask
myself...
"what would Michel (John(s)/Duane/Tom) do..."

I thought you might create a parametized query
for the "week ranges" working off an Iota-like
table going from 1 to 52 to produce something like:

qryRange
WeekNum PeriodStart WeekEnd
1 [Enter Start] [Enter Start] + WeekNum*7
2 [Enter Start] [Enter Start] + WeekNum*7
3 [Enter Start] [Enter Start] + WeekNum*7
....
52 [Enter Start] [Enter Start] + WeekNum*7

then "join" (haven't finished thinking this through)
to a group by query:

SELECT OrderID, Max(OpenDate) MO,
Max(ClosedDate) MC
FROM myTable
GROUP BY OrderID;

then getting count by
"negative summing" the condition that
(MO >= PeriodStart
AND MC > WeekEnd)
Group By WeekNum

I have to get into work so apologise again
that have not thought this through completely.

Gary Walter

Hi,


You want to count the number of records having their closed
Click to expand...
date
not
in
the same week than their open date?


SELECT DatePart("ww", openDate), COUNT(*)
FROM myTable
WHERE (DatePart("ww", openDate) <> DatePart("ww", closedDate))
OR (closedDate Is Null)
GROUP BY DatePart("ww", openDate)


You may have to add -0.5 to the dates, as argument of the DatePart,
if
the starting point of the week is at midday, rather than at midnight.



Hoping it may help,
Vanderghast, Access MVP


I need to create a query which provides a count of the number of
orders
which are currently, or were the end of every week backlogged. The
database
I am querying has open date, and closed date, but the goal is to
produce a
chart (weekly over the last 52 weeks) which shows how many were open
at
the
end of each week, but had not been closed. So, if Sunday morning at
12:00
am signifies the begining of the weekly measurement period, I need
each
backlogged order to be counted each week that it is open before that
sunday
morning, but not closed before it.

Examples???? Help????


Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top