On Mon, Dec 1, 2014 at 2:10 PM, Booth Martin <booth@xxxxxxxxxxxx> wrote:
I had trouble grasping how he could accumulate by week and not
know the week starting date. The power of SQL still impresses me.
SQL is powerful, but in this case most of the "power" is being
provided by a standard week-numbering system. That is, it's the
calendar that is powerful, not the language. It so happens that SQL
effectively has some convenient BIFs that work with the calendar.
What I was trying to establish (unsuccessfully) with my line of
questioning toward the beginning of this thread was the possibility
that the task at hand might NOT be amenable to any standard
week-numbering system. For example, let's say each separate item
starts counting weeks at *exactly* the day on which the first order
for that item was received. (This is NOT the OP's problem; I'm just
laying out a hypothetical scenario.)
So if Item A's first order is from 2014-11-03, then Week 1 for Item A
would be 2014-11-03 through 2014-11-09, and Week 2 would start on
2014-11-10. But if Item B's first order was 2014-11-05, then its Week
1 would be 2014-11-05 through 2014-11-11, and its Week 2 would start
on 2014-11-12.
Maybe it's clearer if I call it an aging problem, where each age
"tier" is 7 days long. In this scenario, SQL's BIFs are no more
useful than RPG's. You'd have to write a bit of your own logic in
either case (roughly speaking, subtract to find the number of days,
and divide by 7).
John Y.
midrange.com
