Home » SQL & PL/SQL » SQL & PL/SQL » Getting all dates in a month (Oracle XE)
Getting all dates in a month [message #319525] Mon, 12 May 2008 01:54 Go to next message
rahul15june
Messages: 35
Registered: May 2008
Member
Hi ,

I have a table like this

SR number
exp_date date
exp_type varchar2(200)
exp_amt number

the data is
sr exp_date exp_type exp_amt
1 01-may-2008 food 100
2 01-may-2008 travel 200
3 04-may-2008 xyz 500
4 07-may-2008 food 20

Now,

I want the,
- Sum of exp_amt group on date
- all missing dates with exp_amt 0
- it should list out for the entire month 31 or 30 days depending on the month selected.

so output should be,

exp_date exp_amt sum
01-May-2008 300
02-may-2008 0
03-May-2008 0
04-May-2008 500
05-May-2008 0
06-May-2008 0
07-May-2008 20
.
.
.
.
.
.
31-May-2008 0

Thanks in advance
Rahul

[Updated on: Mon, 12 May 2008 01:55]

Report message to a moderator

Re: Getting all dates in a month [message #319526 is a reply to message #319525] Mon, 12 May 2008 01:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for "calendar" and/or "row generator".
This is asked every week.
Please search BEFORE posting.

Regards
Michel
Re: Getting all dates in a month [message #319625 is a reply to message #319525] Mon, 12 May 2008 06:53 Go to previous messageGo to next message
rahul15june
Messages: 35
Registered: May 2008
Member
Hi Guys,

okay, so I managed to form a query,

select b.e, nvl(a.exp_amt,0) from
  (SELECT TRUNC(EXP_DATE) EXP_DATE, sum(EXP_AMT) exp_amt from EXP_MAIN
     WHERE to_char(EXP_DATE,'MON-YYYY') = :MON
     and exp_type not in (select exp_type from exp_type where type = 'I')
     GROUP BY TRUNC(EXP_DATE)) A,(SELECT (FIRST_DY + rownum) e
  FROM (SELECT 1 from DUAL CONNECT BY level <= ( :LAST_DY - :FIRST_DY)
  )) b
  where a.exp_date(+) = b.e;


Now my problem is . This lists out all the dates in any given month and the sum as was my initial requirement.

Now, is it possible to get weekly sum also. Like,
- Say in a month I want all EXP_AMT (summation for only weekends,
e.g what was the total expesnse for SAt and sun in everymonth. So there will be generally four rows since each week generally has 4 weeks. And the summation of total exP_amt

-. Can I also get the weekly total. So the output would again be
4 rows generally with the weeks total summed up.

Thanks

[Updated on: Mon, 12 May 2008 06:56]

Report message to a moderator

Re: Getting all dates in a month [message #319636 is a reply to message #319525] Mon, 12 May 2008 07:38 Go to previous message
rahul15june
Messages: 35
Registered: May 2008
Member
This issue is resolved.

Thanks
Previous Topic: Not able to compile a procedure
Next Topic: Delete operation and unable to use trigger
Goto Forum:
  


Current Time: Sun Dec 04 04:27:10 CST 2016

Total time taken to generate the page: 0.08825 seconds