Home » SQL & PL/SQL » SQL & PL/SQL » YTD Accumulation of Months
YTD Accumulation of Months [message #189921] Mon, 28 August 2006 09:54 Go to next message
mlshann
Messages: 1
Registered: August 2006
Location: Nebraska
Junior Member
Good Morning,
This is my first post... please bear with me and please excuse any indiscretions. My problem.. I am getting a monthly total for customer count and duration(represents how long a customer is without service.. ie. Electric utility service). I would like to obtain a running YTD total of customer count and duration. That is as the code progresses through each month a counter would contain the totals for the year as of the end of the month.

Any ideas????



Home Scripts
SQL and PL/SQL Formatter

Choose a database Oracle MSSQL Access MySQL Generic Output: SQL or PL/SQL C# C# String Builder Delphi Java Java String Buffer PHP VB/ASP VC Pro*C Pro*Cobol Html
SELECT TO_CHAR(START_TIME,'YY') AS "YEAR_BUCKET",
TO_CHAR(START_TIME,'MM') AS "MONTH_BUCKET",
COUNT(DISTINCT CUSTOMER_ID)AS "CUST_COUNT",
SUM(DURATION)
FROM
EIT_CUST_INTERRUPT_BY_FEEDER
WHERE (CUSTOMER_ID IS NOT NULL)
AND (DURATION >= 5) AND
start_time BETWEEN TO_DATE('01/01/2005 00:00:00','MM/DD/YYYY HH24:MI:SS')
AND TO_DATE('12/31/2005 23:59:59','MM/DD/YYYY HH24:MI:SS')
GROUP BY TO_CHAR(START_TIME,'YY'), ROLLUP (TO_CHAR(START_TIME,'MM'))


Output SQL:
05 01 5606 320572
05 02 1619 132502
05 03 2238 341967
05 04 14890 917045
05 05 11880 1262018
05 06 10058 1100237
05 07 8301 871919
05 08 3491 409899
05 09 6445 567295
05 10 3409 416826
05 11 15694 4737869
05 12 3547 510620
05 56727 11588769 Format Options:

--------------------------------------------------------------------------------
Keywords case: Uppercase Lowercase InitCap Unchanged

Identifier case: Uppercase Lowercase InitCap Unchanged Init cap each word

Function case: Uppercase Lowercase InitCap Unchanged
--------------------------------------------------------------------------------
Linebreaks with comma:
After
Before
Before with space
--------------------------------------------------------------------------------
List and Parameters Style:
Stacked
Not Stacked
--------------------------------------------------------------------------------
Stacked align:
Align left
Align right
--------------------------------------------------------------------------------
And/Or under Where Clause:
And/Or under Where
--------------------------------------------------------------------------------
Remove linebreak before beautify:
Remove Linebreak before beautify
--------------------------------------------------------------------------------
Trim Quoted Char of Each Line:
Trim Quoted Char of Each Line
quoted char of eachline:
--------------------------------------------------------------------------------
Compact mode:
Compact the output of sql output
Max length per line in compact mode:



.:: Site Home | Wiki Home | Forum Home | Privacy | Contact ::.
Order offline version of this SQL formatter
Re: YTD Accumulation of Months [message #189988 is a reply to message #189921] Mon, 28 August 2006 23:20 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You are looking for a cumulative sum. Oracle does these quite neatly with Analytic Functions. Do a search on this board and you will find tons of examples.
The syntax will end up something like:

SUM(DURATION) 
  OVER (
    PARTITION BY TO_CHAR(START_TIME,'YY') 
    ORDER BY TO_CHAR(START_TIME,'MM')
  )


Ross Leishman
Previous Topic: Before Insert trigger - Skip Insert
Next Topic: how to ungrant (get back) the permission. that we have given to one user.
Goto Forum:
  


Current Time: Fri Dec 09 04:15:09 CST 2016

Total time taken to generate the page: 0.21348 seconds