List of Dates

rbuser

I have a report that must have one row for every day throughout a date
range.

So if they select 9/1/2009 to 10/15/2009, I need a row to print for each
day. The days are essentially my master pipeline.

How is this achieved?

rbuser

I assume you are using a sql based database? If so, it should be achievable
through your query.

rbuser

As far as I know my query will only return rows where there is data for
the date. I need a row for every data even if no data is present.

rbuser

You could use a UNION to acheive the missing rows.

rbuser

And I need only one master row per date.

So if i selected 9/1/2009 to 9/15/2009 my master pipeline needs to have
exactly:

9/1/2009
9/2/2009
9/3/2009
9/4/2009
9/5/2009
9/6/2009
9/7/2009
9/8/2009
9/9/2009
9/10/2009
9/11/2009
9/12/2009
9/13/2009
9/14/2009
9/15/2009

rbuser

Can you give an example of such a union?

rbuser

rbuser

Well its firebird. But any example will help me get the gist of what
you mean.

rbuser

Well, not really, with Oracle you can use the CONNECT BY statement which is
not available in MSSQL, and I'm guessing Firebird.

A generic approach would be to create a "date" table. You could write a
quick program to populate it. One column would not occupy that much space
over 200 years. Not sure what you need for a full range.

then, you have SELECT adate from datetable where adate >= 9/1 and adate <=
10/15 (so to speak).

Then you can union that SELECT statement against your current select
statement. Look up (or google) UNION statement if you need more info.

SELECT ADATE FROM TABLEA (Primary data)
UNION
SELECT ADATE FROM TABLEB (union missing dates)

Another way to do it would be to use a clientdataset, insert missing date
rows, then report.

Maybe someone else has another suggestion.

rbuser

Oh, yes i know how to do that. I was assuming RB had a built in
solution for this type of thing though. Is there none?

rbuser

> Oh, yes i know how to do that. I was assuming RB had a built in solution


the DADE uses the database funcionality and has it's limitations. Does the
DADE support stored procs? (I know it didn't several years ago)--if so, I
would use this as a solution.

Ed Dressel
Team DM

rbuser

Edward Dressel [Team DM] wrote:

well what I've done so far is just make a list variable full of the
dates then tied a JITPipeline to it. Seems to be okay.

nardmoseley

1. For RB 11, DADE includes many new enhancements, I invite you to check
them out.

http://www.digital-metaphors.com/rbWiki/General/What's_New/RB_11/DADE

2. DADE includes a nice syntax highlighted SQL editor. Stored Procs can be
called from SQL.

3. For RB 11 manually edited SQL can be bound to parameters (inlcuding
AutoSearch) and can participate in linking relationships with other queries.


4. Another option is to extend DADE via custom DataView classes. For an
example of definining custom DataView classes that call stored procs, check
out Demos\EndUser Databases\SQL Server\ADO\Stored Proc.




--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com

rbuser

Hello Chris,

Building on KevinZ's suggestion you can also use an outer join to join the
date table to the detail.

select datetable.masterdate, detail.column1, detail.column2, etc
from datetable left outer detail on datetable.masterdate = detail.otherdatefield
where datetable.masterdate >= 9/1/2009 and datetable.masterdate <= 9/15/2009

What this does is select all the records from the datetable table that meet
the where clause and if there is a record in the detail table where the dates
match the datetable date it will also pull the detail values. If there isn't
a match it will still display the datetable.masterdate field.

Just a quick note - the where clause needs to be tweaked based on the database
you are using.

Rick Anderson