asked on

Oracle Dooozy! Cumulative Sum by Month?!?

Hey there,

I have a Movement Table for Receipts, Orders and Adjustments. A customer wants to know how many units and pallets they had in inventory at each month end for a fiscal year.

I have no problem giving them a month end balance for a single month, but have no idea how to accumulate that monthly for the year aside from running 12 separate queries and pasting the results into a spreadsheet.

Ideally it would be nice to put it all in one query, I've put together a UNION query to show what I'm trying to accomplish - however the UNION query is not preferred do to the fact that there are over ten million records in the table and I'd like the query to be as efficient as possible - which by UNION it is not as I'm querying the whole table 12 times (I realize it's probably actually much more than that) rather than once and then between specific dates on wards which is a considerably less number of records.

I'm open to pretty much anything to have a solution.

To explain the fields...

Each pallet is given a PK (PK_Unit). There could be hundreds of records based on that PK in the movement table. Hence the nested select to get the pallet count. They are an ISO customer, this is why I have to minus 10 from the date to ensure I get the proper month.

Ideally I'd like to be able to put in a start date which would sum the entire table up to that date to give me a starting balance. Then have an end date (a year later) whereby the query would then sum and group by month to the end date and pull of a cumulative sum on it by month. I've worked with OVER and PARTITION BY before but can't fathom how to pull it off in this scenario - if it can even be done.

Your expertise and advise is always appreciated.

SELECT TO_CHAR(TO_DATE('30.08.09', 'DD.MM.YY')-10, 'Month') AS M, 
		SUM(A.Units) AS Cs, COUNT(A.PK_Unit) AS Plt
FROM (
SELECT tblMovement.PK_Unit, SUM(tblMovement.Units) AS Units 
FROM tblMovement 
WHERE tblMovement.Company = 'ABC' AND tblMovement.Customer = 'CUSTOMER123' AND 
		tblMovement.Movement_Date <= TO_DATE('30.08.092359', 'DD.MM.YYHH24MI') AND 
		tblMovement.Movement_Type IN('Receipt','Order','Adjustment') 
GROUP BY tblMovement.PK_Unit 
HAVING SUM(tblMovement.Units) > 0
	) A 
UNION 
SELECT TO_CHAR(TO_DATE('27.08.09', 'DD.MM.YY')-10, 'Month') AS M, 
		SUM(A.Units) AS Cs, COUNT(A.PK_Unit) AS Plt
FROM (
SELECT tblMovement.PK_Unit, SUM(tblMovement.Units) AS Units 
FROM tblMovement 
WHERE tblMovement.Company = 'ABC' AND tblMovement.Customer = 'CUSTOMER123' AND 
		tblMovement.Movement_Date <= TO_DATE('27.09.092359', 'DD.MM.YYHH24MI') AND 
		tblMovement.Movement_Type IN('Receipt','Order','Adjustment') 
GROUP BY tblMovement.PK_Unit 
HAVING SUM(tblMovement.Units) > 0
	) A 
UNION
SELECT TO_CHAR(TO_DATE('01.11.09', 'DD.MM.YY')-10, 'Month') AS M, 
		SUM(A.Units) AS Cs, COUNT(A.PK_Unit) AS Plt
FROM (
SELECT tblMovement.PK_Unit, SUM(tblMovement.Units) AS Units 
FROM tblMovement 
WHERE tblMovement.Company = 'ABC' AND tblMovement.Customer = 'CUSTOMER123' AND 
		tblMovement.Movement_Date <= TO_DATE('01.11.092359', 'DD.MM.YYHH24MI') AND 
		tblMovement.Movement_Type IN('Receipt','Order','Adjustment') 
GROUP BY tblMovement.PK_Unit 
HAVING SUM(tblMovement.Units) > 0
	) A 
UNION
SELECT TO_CHAR(TO_DATE('29.11.09', 'DD.MM.YY')-10, 'Month') AS M, 
		SUM(A.Units) AS Cs, COUNT(A.PK_Unit) AS Plt
FROM (
SELECT tblMovement.PK_Unit, SUM(tblMovement.Units) AS Units 
FROM tblMovement 
WHERE tblMovement.Company = 'ABC' AND tblMovement.Customer = 'CUSTOMER123' AND 
		tblMovement.Movement_Date <= TO_DATE('29.11.092359', 'DD.MM.YYHH24MI') AND 
		tblMovement.Movement_Type IN('Receipt','Order','Adjustment') 
GROUP BY tblMovement.PK_Unit 
HAVING SUM(tblMovement.Units) > 0
	) A 
UNION
SELECT TO_CHAR(TO_DATE('27.12.09', 'DD.MM.YY')-10, 'Month') AS M, 
		SUM(A.Units) AS Cs, COUNT(A.PK_Unit) AS Plt
FROM (
SELECT tblMovement.PK_Unit, SUM(tblMovement.Units) AS Units 
FROM tblMovement 
WHERE tblMovement.Company = 'ABC' AND tblMovement.Customer = 'CUSTOMER123' AND 
		tblMovement.Movement_Date <= TO_DATE('27.12.092359', 'DD.MM.YYHH24MI') AND 
		tblMovement.Movement_Type IN('Receipt','Order','Adjustment') 
GROUP BY tblMovement.PK_Unit 
HAVING SUM(tblMovement.Units) > 0
	) A 
UNION
SELECT TO_CHAR(TO_DATE('31.01.10', 'DD.MM.YY')-10, 'Month') AS M, 
		SUM(A.Units) AS Cs, COUNT(A.PK_Unit) AS Plt
FROM (
SELECT tblMovement.PK_Unit, SUM(tblMovement.Units) AS Units 
FROM tblMovement 
WHERE tblMovement.Company = 'ABC' AND tblMovement.Customer = 'CUSTOMER123' AND 
		tblMovement.Movement_Date <= TO_DATE('31.01.102359', 'DD.MM.YYHH24MI') AND 
		tblMovement.Movement_Type IN('Receipt','Order','Adjustment') 
GROUP BY tblMovement.PK_Unit 
HAVING SUM(tblMovement.Units) > 0
	) A 
UNION
SELECT TO_CHAR(TO_DATE('28.02.10', 'DD.MM.YY')-10, 'Month') AS M, 
		SUM(A.Units) AS Cs, COUNT(A.PK_Unit) AS Plt
FROM (
SELECT tblMovement.PK_Unit, SUM(tblMovement.Units) AS Units 
FROM tblMovement 
WHERE tblMovement.Company = 'ABC' AND tblMovement.Customer = 'CUSTOMER123' AND 
		tblMovement.Movement_Date <= TO_DATE('28.02.102359', 'DD.MM.YYHH24MI') AND 
		tblMovement.Movement_Type IN('Receipt','Order','Adjustment') 
GROUP BY tblMovement.PK_Unit 
HAVING SUM(tblMovement.Units) > 0
	) A
UNION
SELECT TO_CHAR(TO_DATE('28.03.10', 'DD.MM.YY')-10, 'Month') AS M, 
		SUM(A.Units) AS Cs, COUNT(A.PK_Unit) AS Plt
FROM (
SELECT tblMovement.PK_Unit, SUM(tblMovement.Units) AS Units 
FROM tblMovement 
WHERE tblMovement.Company = 'ABC' AND tblMovement.Customer = 'CUSTOMER123' AND 
		tblMovement.Movement_Date <= TO_DATE('28.03.102359', 'DD.MM.YYHH24MI') AND 
		tblMovement.Movement_Type IN('Receipt','Order','Adjustment') 
GROUP BY tblMovement.PK_Unit 
HAVING SUM(tblMovement.Units) > 0
	) A
UNION
SELECT TO_CHAR(TO_DATE('02.05.10', 'DD.MM.YY')-10, 'Month') AS M, 
		SUM(A.Units) AS Cs, COUNT(A.PK_Unit) AS Plt
FROM (
SELECT tblMovement.PK_Unit, SUM(tblMovement.Units) AS Units 
FROM tblMovement 
WHERE tblMovement.Company = 'ABC' AND tblMovement.Customer = 'CUSTOMER123' AND 
		tblMovement.Movement_Date <= TO_DATE('02.05.102359', 'DD.MM.YYHH24MI') AND 
		tblMovement.Movement_Type IN('Receipt','Order','Adjustment') 
GROUP BY tblMovement.PK_Unit 
HAVING SUM(tblMovement.Units) > 0
	) A
UNION
SELECT TO_CHAR(TO_DATE('30.05.10', 'DD.MM.YY')-10, 'Month') AS M, 
		SUM(A.Units) AS Cs, COUNT(A.PK_Unit) AS Plt
FROM (
SELECT tblMovement.PK_Unit, SUM(tblMovement.Units) AS Units 
FROM tblMovement 
WHERE tblMovement.Company = 'ABC' AND tblMovement.Customer = 'CUSTOMER123' AND 
		tblMovement.Movement_Date <= TO_DATE('30.05.102359', 'DD.MM.YYHH24MI') AND 
		tblMovement.Movement_Type IN('Receipt','Order','Adjustment') 
GROUP BY tblMovement.PK_Unit 
HAVING SUM(tblMovement.Units) > 0
	) A
UNION
SELECT TO_CHAR(TO_DATE('27.06.10', 'DD.MM.YY')-10, 'Month') AS M, 
		SUM(A.Units) AS Cs, COUNT(A.PK_Unit) AS Plt
FROM (
SELECT tblMovement.PK_Unit, SUM(tblMovement.Units) AS Units 
FROM tblMovement 
WHERE tblMovement.Company = 'ABC' AND tblMovement.Customer = 'CUSTOMER123' AND 
		tblMovement.Movement_Date <= TO_DATE('27.06.102359', 'DD.MM.YYHH24MI') AND 
		tblMovement.Movement_Type IN('Receipt','Order','Adjustment') 
GROUP BY tblMovement.PK_Unit 
HAVING SUM(tblMovement.Units) > 0
	) A
UNION
SELECT TO_CHAR(TO_DATE('01.08.10', 'DD.MM.YY')-10, 'Month') AS M, 
		SUM(A.Units) AS Cs, COUNT(A.PK_Unit) AS Plt
FROM (
SELECT tblMovement.PK_Unit, SUM(tblMovement.Units) AS Units 
FROM tblMovement 
WHERE tblMovement.Company = 'ABC' AND tblMovement.Customer = 'CUSTOMER123' AND 
		tblMovement.Movement_Date <= TO_DATE('01.08.102359', 'DD.MM.YYHH24MI') AND 
		tblMovement.Movement_Type IN('Receipt','Order','Adjustment') 
GROUP BY tblMovement.PK_Unit 
HAVING SUM(tblMovement.Units) > 0
	) A

Open in new window

Sean Stuber

can you provide sample data and expected output?

abitoun

I have a quick question, i am analizing your query and some of the dates do not make sense for example:
not all dates are at the end of each month, perhaps the last working day?

You start with August but on the second union the main query is august and sub query is september, then you jump to the begininig of November, where is october?, then the next union is the end of november, everything is normal till the march query and then you do not have april but have the begining of may, then again you have the end of may, then you have no july but have august of the following year.

what is the consistency of the dates? what makes each date so different and some months to jump and some months to include the begining and the end of the month?

NEVAEHSIN

ASKER

Sample result attached (from UNION query previously posted).

The month end dates are to conform to ISO 8601 standard dates. ISO dating does not factor months - which make this query a little more complex but doable. Your correct - I had a typo on the second query - should be 27.09.09. October gets by passed due to in the ISO world "October" starts in September and ends in November, at least in 2009.

As to the rest of the dates - your absolutely right there is no consistency because; again ISO dating does not have months. The whole idea behind ISO dating is that every week (week number) in the year starts on Monday.

http://en.wikipedia.org/wiki/ISO_8601

NEVAEHSIN

ASKER

Sample attached now ^.^
Month-End-Case-Pallet-For-Year-R.xls

Sean Stuber

is that sample input or expected output?
whichever it is, can you provide the other?

NEVAEHSIN

ASKER

That's expected output. Working on input, it'll be a minute due to so many records. .CSV ok? :p

Sean Stuber

sure, you can create a smaller subset if you want, doesn't have to be all the data, just enough to demonstrate what you're looking for.

NEVAEHSIN

ASKER

Still working on it. Tried first 100000 records and didn't even get a whole year... Should have 1000000 shortly.

NEVAEHSIN

ASKER

And here's a years worth and then some.
EE-SAMPLE.csv

Sean Stuber

I'm confused about your use of ISO dates. From the link your provided, ISO 8601 DOES provide support for months.
And there is no Monday-offset for them. That only applies to weeks. and years

Do the expected results you have in 33746303 apply to the data you have in 33747195 ?

I "think" what you are trying to do is define a month as starting when the ISO week containing the 1st of each month starts. The standard doesn't say anything about that.

In fact, that would sort of be counter to the standard since ISO 2010 starts on January 4.
Jan 1,2,3 are part of ISO 2009.

I can do that anyway, I just want to be sure I understand what you're trying to do, and confirm it's really what you want.

NEVAEHSIN

ASKER

Sorry I've not been active, been extremely busy.

Anyways, let me answer some of your questions, and then try to simplify this whole mess. I apologize, the results did not correspond with the data. I couldn't figure out how to calculate ISO months based on the fact that some ISO months contain 5 weeks and other contain 4 and that some ISO months roll into georgian months, similar to the years as you pointed out.

Basically,

I need to find ISO month ends without a whole wack of CASE statements (that need to be continuously updated). And use those month ends to do an analytical query to replace the twelve union queries showing month end balances for each month in a single query.

Thanks for your time and patience.

Sean Stuber

Can you explain what you consider an ISO-month?

The link you provided does not have any special consideration for when a month begins or ends, which, to me, would mean they start and end on calendar date.

It seems like you want something other than that, but nothing above describes what your rule is

NEVAEHSIN

ASKER

Please see the attached file. This lists the ISO months I need to capture. The tricky part is that the year starts with the week of our civic holiday (August 1st). The attachment includes the next 3 years and also has the month end dates.
Copy-of-ISOwk--2-.xls

Sean Stuber

do you need the results based on the August1 year start? or the iso year?

NEVAEHSIN

ASKER

Well, I would need a month end balance starting August (month end) and each month end there after for the year.

Sean Stuber

so, a year is NOT an iso year, it's August-July

but each month is defined by some set of ISO weeks.
based on the pattern, your weeks are selected where the month begins in the iso week of the first Saturday of each month and runs through the week of the last saturday of the month
Weeks begin on Monday and end on Sunday.

So, October 2010 is ISO weeks 39-43
which means your pseudo-iso-month October is calendar dates September 27 - October 31

is that right?

NEVAEHSIN

ASKER

You got it!!!

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

small adjustment to previous query

SELECT CASE
           WHEN movement_date < mbegin
           THEN
               TO_CHAR(ADD_MONTHS(TRUNC(movement_date, 'mm'), -1), 'yyyy-mm')
           WHEN movement_date >= mbegin AND movement_date < mend + 1
           THEN
               TO_CHAR(TRUNC(movement_date, 'mm'), 'yyyy-mm')
           ELSE
               TO_CHAR(ADD_MONTHS(TRUNC(movement_date, 'mm'), 1), 'yyyy-mm')
       END
           iso_month,
       SUM(units) cs,
       COUNT(pk_unit) plt
  FROM (SELECT m.*,
               NEXT_DAY(
                   NEXT_DAY(TRUNC(m.movement_date, 'mm') - 1, 'Saturday') - 7,
                   'Monday'
               )
                   mbegin,
               NEXT_DAY(
                   NEXT_DAY(
                       LAST_DAY(TRUNC(m.movement_date, 'mm')) - 7,
                       'Saturday'
                   )
                   - 7,
                   'Monday'
               )
               + 6
                   mend
          FROM tblmovement m
         WHERE company = 'ABC'
           AND customer = 'CUSTOMER123'
           AND movement_type IN ('RECEIPT', 'ORDER', 'ADJUSTMENT'))
GROUP BY CASE
             WHEN movement_date < mbegin
             THEN
                 TO_CHAR(
                     ADD_MONTHS(TRUNC(movement_date, 'mm'), -1),
                     'yyyy-mm'
                 )
             WHEN movement_date >= mbegin AND movement_date < mend + 1
             THEN
                 TO_CHAR(TRUNC(movement_date, 'mm'), 'yyyy-mm')
             ELSE
                 TO_CHAR(
                     ADD_MONTHS(TRUNC(movement_date, 'mm'), 1),
                     'yyyy-mm'
                 )
         END

Open in new window