Author Topic: Date  (Read 8364 times)

Gyration

  • Newbie
  • *
  • Posts: 2
    • View Profile
Date
« 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?



Mike

  • Administrator
  • Hero Member
  • *****
  • Posts: 2040
    • View Profile
Re: Date
« Reply #1 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.

Gyration

  • Newbie
  • *
  • Posts: 2
    • View Profile
Re: Date
« Reply #2 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

zx8754

  • Newbie
  • *
  • Posts: 2
    • View Profile
Re: Date
« Reply #3 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');

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

Prakash

  • Newbie
  • *
  • Posts: 17
    • View Profile
Re: Date
« Reply #4 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');
« Last Edit: January 05, 2014, 09:44:17 PM by Mike »