Date

[Gyration]

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?

[Mike]

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.

[Gyration]

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:

YEARS	PLAN_TOTAL	ACTUAL_TOTAL
October   2009	160,562.00	181,860.00
August    2009	160,562.00	222,495.00
November  2009	160,562.00	150,990.00
July    2009	160,562.00	129,990.00
December  2009	160,562.00	0.00
September 2009	160,562.00	198,870.00
June    2009	963,374.00	952,539.00

I need the SUM of PLAN_TOTAL form June 2009 to Septermber 2009
 
D

[zx8754]

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');

newbielink:http://www.techonthenet.com/sql/between.php [nonactive]

[Prakash]

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');