Open records vs closed records by weeks

[chrisl1471]

Alright gurus: Have a question for ya, I have a database that shows orders, I have 2 date fields for REQUESTED ON then another for STATUS Date as the status changes to eventually "CLOSED".

What I am trying to get the DB to show me, is on week x I had XXX open records, and keep that total running for all the following weeks. Where it would be Open Records = (Previous + new records - closed records) for each week? Some weeks NEW records and/or closed may be null so I assume I can use the Nz here?

I think I am on the right path I have 2 sub queries built now that 1 shows me the # of records (new) broken down in weeks....

Then a 2nd one that shows me # of records closed by weeks...

Now I am stumped how to get them to do the math for me?

 

[June7]

Each query has the same weeks? Perhaps JOIN these two queries.

 

[chrisl1471]

Don't believe that is the answer....a join will show me only those where the records date (weeks) are EQUAL.....So I could have a record opened in week 11 but closed in week 12 or 51...........If you left or right join it then it would be ALL from one side and ONLY those where the join is EQUAL.....Keep in mind that I have some weeks were I have null values also, which a join would filter that out cause they would not have an equal on the other side.

Which is not what I am after....

I am trying to get something like this


Week # NEW CLOSED TOTAL
1 382 82 300
2 100 50 350
3 0 50 300

This is being asked for as an afterthought......so I might need to redesign the DB to make this work.?

 

[June7]

That's why I asked if both datasets have same weeks. If they don't then you need to JOIN both queries to a dataset that does have all weeks.

 

[The_Doc_Man]

Redesign? Probably not a total rework. However, you might need to do some out-of-the box thinking.

If you have the information to support this, make a query that shows the date on which each of those orders was opened and when it was closed as separate events. Make the query show a status digit 1 for each open order and a digit 0 for each closed order. Be sure that the event date is available to the query. You want to do a DatePart (which you can look up easily with the great Google brain) to pick out the YEAR and also the WEEK NUMBER. If you convert those to text strings, you could generate a field that contained, for example, 2018-06, 2018-07, etc. (weeks in February of this year).

Now treat this as a pseudo-inventory with transactions. You can write an aggregate query that SUMs the "open" digits grouped by that concatenated year-week number. You can do the same type of thing if you have other statuses besides REQUESTED and CLOSED for which you would do this kind of analysis.

And you don't necessarily need to have a separate table for this if there is a way for you to make a query to simulate what appears to be a transaction. Because that aggregate query would work just as well based on a query as it would work on a table.

 

[Pat Hartman]

Queries don't have a facility to turn 1 record into many but that is essentially what you need to do to accurately count things by time period. One way to do do this is to create a table with one entry for each week with a start date and an end date. You would use a cross join (Cartesian Product) with a where clause that selects rows from the weeks table where the start/end dates overlap the request/close date range. That expression is:

requested date is <= the end date and the closed date is >= the start date.

This will select 1 to n rows. Each row represents a week and that allows you to count the weeks that the request was open.