How to display all the date in the report

[dm21]

Hi,

I have a report where user wants to see all the dates between the Start Date and End Date prompt in the report even if there is no data in the table.

i.e. If user enters start date as '20030401' and end date as '20030403'

user wants to see as

04/01/2003 abc 100.00
04/02/2003 abc 0.0
04/03/2003 bcd 50.0

on 04/02/2003 there are no recodrs in table but user wants to see as zero revenue.

Now, I am already using a union between two tables to get the requirements in the sql window of the impromptu.
i.e. select date,
product,
revenue
from data
union
select date,
product,
revenue
from lost_deals

How can I get the above result with all the date.

Your help is appriciated.

Thanks.

DM

 

[CanadianTechie]

DM,

I would suggest that you either:

1) Populate your source table with 0 sales records for the date in question (which may not be possible if your source is a transactional system compared to a managed data warehouse)

2) Create a second table with two fields, the first a date field the second a numeric column mathing your source table. Pre-populate the table with records that span the date range desired (put in a few years of records) -- then change your query to perform a full join or union between the tables...

If you need further assistance, I can post actual SQL snippits for you to illustrate my approach. Let me know...

Pedro Cardoso
A.K.A CanadianTechie

 

[astra1]

HI,

Just to add few more lines on what techie said, You may use a table with date field only and have an outer join with the one on the base table.

Vish

 

[dm21]

Hi
Yes I can do union but that means I will have to do four unions which will affect the performance.

And outer join does not give zero value, it repeats the value from the base table.

Write now I have done the union the performance is not that great. I was wondering if there is another way it can be possible.

Thanks.

 

[astra1]

I created 2 tables as tmp1 and tmp2.
tmp1 has data while tmp2 has one date field

=> for tmp1

select * from tmp1 gives

1234 2003-01-01 00:00:00.000
112 2003-01-03 00:00:00.000
9078 2003-01-04 00:00:00.000


=> for tmp2

select * from tmp2 gives

2003-01-01 00:00:00.000
2003-01-02 00:00:00.000
2003-01-03 00:00:00.000
2003-01-04 00:00:00.000
2003-01-05 00:00:00.000

and the query

select b.dt_fld, a.fld2
from tmp1 a
right outer join tmp2 b
on a.dt_stamp= b.dt_fld


gives

2003-01-01 00:00:00.000 1234
2003-01-02 00:00:00.000 NULL
2003-01-03 00:00:00.000 112
2003-01-04 00:00:00.000 9078
2003-01-05 00:00:00.000 NULL


You may replace the NULL with zeroes in imrs.


Hope this solves it

Vish