Dates in a Month Qry

[venur]

Hi,

I am trying to get the sum of a column for all distinct dates in a month.

Ex: Table A
Fee (number)
lupddt(date)

Output for mon (jan,2003) should look like the below

Sum (Fee) , lupddt
--------------------
10 01-jan-2003
20 02-jan-2003
30 03-jan-2003
10 04-jan-2003
20 05-jan-2003
30 06-jan-2003
10 07-jan-2003
10 08-jan-2003
0 09-jan-2003
---
---
---
10 31-jan-2003

If there was no record inserted on 09-jan-2003 it should still show up in date column and the sum of fees should apper as 0.

I tried doing like this but its taking time and did not get the result as I expected.

SELECT
SUM(FEE), to_char(lupddt,'dd-mon-yyyy')
FROM
A
WHERE
to_char(lupddt,'dd-mon-yyyy') IN (
SELECT TRUNC(to_date('01-JUL-2003','dd-MON-yy'),'MON') + rownum - 1 day
FROM all_objects
WHERE
rownum <= to_char(add_months(TRUNC(to_date('01-JUL-2003','dd-MON-yy'),'MON'),1)-1,'dd')
)GROUP BY to_char(lupddt,'dd-mon-yyyy')

Hope Some one will help me ...

Thank you,
venu

 

[LKBrwnDBA]

Try this:

SELECT
SUM(FEE), to_char(d.lupddt,'dd-mon-yyyy')
FROM
A
, (SELECT to_date('01-JUL-2003','dd-MON-yy') + rownum - 1 lupddt
FROM all_objects
WHERE rownum <= last_day(TRUNC(to_date('01-JUL-2003','dd-MON-yy'))) D
WHERE A.lupddt(+) = D.luppdt
GROUP BY to_char(d.lupddt,'dd-mon-yyyy')


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Venu,

While I was composing my solution, it looks like LKBrwnDBA was even quicker on the draw. Our solutions look similar:

accept month_year prompt &quot;Enter Month and Year to evaluate (ex. JAN2003): &quot;
col tot heading &quot;Total|fee|for|Date&quot; format 999,999.99
col dt heading &quot;Survey|Date&quot;
select nvl(sum(fee),0) tot,drive_dt dt
from a, (select to_date(rownum||'&month_year','ddmonyyyy') drive_dt from all_objects
where rownum <= to_char(last_day(to_date('01&month_year','ddmonyyyy')),'dd')) b
where b.drive_dt=a.LUPDDT(+)
group by drive_dt
/

Cheers,

Dave

 

[DeepDiverMom]

Actually, if it was a race for the quickest working solution, then I guess SantaMufasa gets the prize. For LK's script to work here are the fixes:

1) Add a closing paren at the end of the line with the inner WHERE clause
2) &quot;luppdt&quot; needs to read &quot;lupddt&quot; in the inner SELECT
3) Also the inner WHERE clause throws an error when trying to compare ROWNUM to a DATE. The WHERE needs to isolate the day number with a to_char and read:
&quot;WHERE rownum <= to_char(last_day(TRUNC(to_date('01-JUL-2003','dd-MON-yy'))),'DD')) D&quot;
4) To fit venur's request, the original SELECT needs to print zeros for nulls:
&quot;SELECT nvl(SUM(FEE),0),...&quot;

Also, I like the idea of prompting for the month and year versus a literal, but that's just needless nit picking on my part.

 

[venur]

Hi,

The query is running fine and thank you for response, but the query is not giving the results as we expected.
A.FEE D.LUPDDT
----------------- -----------
0 01-aug-2003
0 02-aug-2003
0 03-aug-2003
0 04-aug-2003
0 05-aug-2003
0 06-aug-2003
0 07-aug-2003
0 08-aug-2003
0 09-aug-2003
0 10-aug-2003
0 11-aug-2003
----
---
0 23-aug-2003
0 24-aug-2003
0 25-aug-2003
0 26-aug-2003
0 27-aug-2003
0 28-aug-2003
0 29-aug-2003
0 30-aug-2003
0 31-aug-2003

When I run the query with just group by its giving the below result
LPDDT FEE
-------- ---------
04-AUG-03 427.8
05-AUG-03 944.45
06-AUG-03 823.35
07-AUG-03 821.55
08-AUG-03 830.05
09-AUG-03 1088.45
10-AUG-03 882.6
11-AUG-03 723.15
12-AUG-03 1109.2

------
----
26-AUG-03 1260.4
27-AUG-03 1568.85
28-AUG-03 520.55


And even the query is taking lot of time to execute so I have decided to create a temp table and a JOB that will run at 12:15 AM every day, which insert the record into temp table with the fees collected on that date if not 0.

I dont know its a good Idea of doing it.. but any comments will be greatly appreciated.

Thanks,
venu

 

[SantaMufasa]

Venu,

Which query were you running, LKBrwnDBA's or SantaMufasa's? I know I tested mine and it gave non-zero sums for several of the days. Also, to address the performance issue, how many rows total in your table and how many rows typically for any given month that you are surveying? Do you have an on LUPDDT? Please advise.

Dave

 

[SantaMufasa]

Previous post should have finished with &quot;Do you have an INDEX on LUPDDT?&quot; Sorry.

 

[LKBrwnDBA]

Ok,

Here is the corrected version, try it:

SELECT
SUM(FEE), to_char(d.lupddt,'dd-mon-yyyy')
FROM
A
, (SELECT to_date('01-JUL-2003','dd-MON-yy')
+ rownum - 1 lupddt
FROM all_objects
WHERE rownum <= to_char(last_day(
to_date('01-JUL-2003','dd-MON-yy')))
) D
WHERE A.lupddt(+) = D.lupddt
GROUP BY to_char(d.lupddt,'dd-mon-yyyy');

This will give you the missing days.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[venur]

Thank you for the quick responses. The reason why I have went for a temp table bcoz. The table has around 10,00,000 records so for 6 months as it might increase I mean the ratio too.

There is no Index on LUPDDT. I have created the index and there is little improvement. But sill waiting for your suggetions.

 

[SantaMufasa]

LK,

The most recent code still doesn't work: &quot;WHERE rownum <= to_char(last_day(to_date('01-JUL-2003','dd-MON-yy')))&quot; still throws an error since you are comparing a number (rownum) to a non-numeric character string [to_char(last_day...].

Venur,

If your data are strictly historical and are not updated after the fact (example: in August you do not go back and add rows for March), then you should be able to isolate the rows you are querying by creating either an extraction table of the detail rows for a given month or a summary table of your daily summations. These would work for the historical case where additions following month-end closing do not occur. If the data must remain dynamic, then you can use Oracle's partitioning to improve your access performance.

Cheers.

 

[LKBrwnDBA]

Well, try it with:

WHERE rownum <= to_number(to_char(last_day(
to_date('01-JUL-2003','dd-MON-yy'))




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

My apologies for just pointing out the error without providing a solution (since the revision, above fails whilst trying to convert '01-JUL-2003' to a number):

...WHERE rownum <= to_char(last_day(to_date('01-JUL-2003')),'dd')...

 

[venur]

Hi,

Thank you for your valuable answers. I am having difficulty with the below query can one of you tell what wrong and why its not giving the results the same way.Is there any thing to do with the DB settings? or am I doning some thing wrong?

Qry 1: This is running fine on test DB But not in Production DB.

SELECT D.lupddt, A.FEEMOUNT FROM
MONTHLYFEE A ,
(SELECT to_date('01-AUG-03','DD-MON-YY') + rownum - 1 lupddt FROM all_objects
WHERE
rownum <= to_char(last_day(TRUNC(to_date('01-AUG-03','DD-MON-YY'))),'DD') ) D
WHERE
A.LUPDDT (+)= D.LUPDDT

Qry 2: This is running fine on both Test DB and Prod DB but
I cannot do the outer join as It giving me an error
ORA-00920: invalid relational operator (+)
If I remove the outer join then I am not getting all the dates. ie., 30th and 31st of this month as it has no records yet in the table.

SELECT D.lupddt, A.FEEMOUNT FROM
MONTHLYFEE A ,
(SELECT to_date('01-AUG-03','DD-MON-YY') + rownum - 1 lupddt FROM all_objects
WHERE
rownum <= to_char(last_day(TRUNC(to_date('01-AUG-03','DD-MON-YY'))),'DD') ) D
WHERE
TO_DATE(A.LUPDDT,'DD-MON-YY')= D.LUPDDT

Hope to see why or what is worng ??

Thank you,
venu

 

[LKBrwnDBA]

Qry.1

Does it give any error in production?
What are the symptoms?

Qry.2

Well, all I see is that if A.LUPDDT is a date, then why:

TO_DATE(A.LUPDDT,'DD-MON-YY')= D.LUPDDT ???

Is the table the same in Prod DB as in Test DB?

If not, for the outer join did you try:

TO_DATE(A.LUPDDT(+),'DD-MON-YY')= D.LUPDDT

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[venur]

Hi,

Thank you LKBrwnDBA.

The outer join TO_DATE(A.LUPDDT(+),'DD-MON-YY')= D.LUPDDT
is working fine in both TestDB and ProdDB and now I am getting my much waited result.

Thank you once again.

Cheers
venu