PSOUG.org

General Category => PSOUG => Topic started by: Gyration on November 25, 2009, 06:02:21 AM

Title: Date
Post by: Gyration on November 25, 2009, 06:02:21 AM
Hi guys, I'm new in this forum. I need help.

I want the sum of, lets say electricity bill for a specific person according to a period of time.

say the sum of a person's electricity bill from March 2009 to July 2009.

Anyone for assistance?

(http://psoug.org/forum/Smileys/default/huh.gif)
Title: Re: Date
Post by: Mike on November 25, 2009, 07:19:43 AM
Post the schema for the table and some sample data; otherwise it's hard for people to guess what code or queries might be needed.
Title: Re: Date
Post by: Gyration on November 26, 2009, 12:47:16 AM
Here is my sample query: 
 
SELECT
TO_CHAR(c.END_DATE,'Month YYYY') years,
SUM (b.plan_total_lcl) plan_total,
SUM (b.actual_total_LCL) actual_total


FROM KCST_BUDGET_PERIOD_SUM a,

KCST_CURRENCY_LINES b,

KNTA_PERIODS c

WHERE a.BUDGET_ID = (SELECT z.PRJ_BUDGET_ID FROM KCRT_FG_PFM_PROJECT z WHERE z.REQUEST_ID = 43985)

AND a.currency_line_id = b.currency_line_id
AND c.period_id = a.period_id

GROUP BY TO_CHAR(c.END_DATE,'Month YYYY')

ORDER BY 1


Results:

YEARSPLAN_TOTALACTUAL_TOTAL
October   2009160,562.00181,860.00
August    2009160,562.00222,495.00
November  2009160,562.00150,990.00
July    2009160,562.00129,990.00
December  2009160,562.000.00
September 2009160,562.00198,870.00
June    2009963,374.00952,539.00


I need the SUM of PLAN_TOTAL form June 2009 to Septermber 2009
 
D
Title: Re: Date
Post by: zx8754 on January 27, 2010, 08:11:34 AM
I am new to this place as well, but recenly was working with dates. So here is my solution.

You coud add extra condition for WHERE. Like this?

AND c.END_DATE is between March 2009 and July 2009


Example use of between:
SELECT *
FROM orders
WHERE order_date between to_date ('2003/01/01', 'yyyy/mm/dd')
   AND to_date ('2003/12/31', 'yyyy/mm/dd');

http://www.techonthenet.com/sql/between.php (http://www.techonthenet.com/sql/between.php)
Title: Re: Date
Post by: Prakash on October 07, 2013, 09:57:27 AM
This is just an idea - it need to be tried on your schema and system. Thinking that you want a grand total (sum) for certain months.

Code: [Select]
SELECT "Grand Total - June 2009 - September 2009", SUM(plan_total)
FROM (SELECT TO_CHAR(c.END_DATE,'MM-YYYY') years,
   SUM (b.plan_total_lcl) plan_total,
   SUM (b.actual_total_LCL) actual_total
   FROM KCST_BUDGET_PERIOD_SUM a,
   KCST_CURRENCY_LINES b,
   KNTA_PERIODS c
   WHERE a.BUDGET_ID = (SELECT z.PRJ_BUDGET_ID FROM KCRT_FG_PFM_PROJECT z WHERE z.REQUEST_ID = 43985)
   AND a.currency_line_id = b.currency_line_id
   AND c.period_id = a.period_id
   GROUP BY TO_CHAR(c.END_DATE,'MM YYYY')
   ORDER BY 1) X
WHERE to_char(Years,'MM-YYYY') between to_char('01-06-2009', 'MM-YYYY') to to_char('01-09-2009','MM-YYYY');