Good point, Roger. It looked to me that he was looking for a report that was for the last 52 weeks, and so would be, say, November, 2014 back thru December, 2013. My own prefernece in such a situation is the prior quarter by week, and then quarterly accumulation for as many previous quarters that might make sense.

I had trouble grasping how he could accumulate by week and not know the week starting date. The power of SQL still impresses me.


On 12/1/2014 10:27 AM, Roger Harman wrote:
Don't limit yourself to 52 weeks, Booth. Fiscal weeks could be 52, 53, or
even 54 (once every 28 years - 2000 was one of them). I remember having to
tweak things to deal with that 54 week year.



-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Booth
Martin
Sent: Friday, November 28, 2014 8:51 PM
To: Midrange Systems Technical Discussion
Subject: Re: Get Start Date of a Week Number

I love these kinds of problems. :) Questions for you: You speak of
already grouping the sales by week.
What method are you using for that, because that would probably point to a
similar way to get the date for the week.
Are you are needing 52 columns for each report?
Is column 1 always the first week of the year?

On 11/26/2014 2:31 PM, Brian wrote:
Thank you for the feedback. In the forecast I am getting arbitrary
dates and quantities. The person getting the report simply wanted the
quantities accumulated by week. So, if I got data for Monday and
Tuesday of the same week, I would add those together.

In my report, I had a row for each item and then a column for each week.
It might look like this:

Item, Week 1, Week 2
ABC,344,200

And so on.

After generating that output, the user then asked to have the start
date for the week show instead of the week number so now it night look
like:
Item, 2014-12-29, 2015-01-05
ABC,344,200

The 12/29 and 01/15 dates were just from a calendar I was looking at.
I do not think it matters at all if the definition of the week is the
ISO one or not. The main thing is that I am consistent and put dates
as the column headings and not week numbers anymore.

I think Birgitta's solution will work, but I am not in a place to try
it out at the moment.

If you've got other thoughts though, just let me know.

Thanks to all who replied so far!



--
Booth Martin
www.martinvt.com
(802)461-5349
Skype: booth.martin

Nostalgia isn't what it used to be.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.



This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].