Hi Guys,

I want to know the best / efficient way to calculate 3 daily forecast columns over a date
range.

Let me give an example:

Here's a basic reservation table storing reservation periods.

resID  IN                  OUT
--------------------------------------
1           2007-05-01     2007-05-05
2           2007-05-02     2007-05-06
3           2007-05-03     2007-05-07

From this table I want to produce a daily forecast of 3 things,  'guests in hotel', 'no.
checkouts' and 'no.checkins' for each day of the report date range.

So based on the above table, if I set the report range from 2007-04-30 to 2007-05-08 the
results would be...

date               inhotel             no. checkouts       no.checkins
------------------------------------------------------------------------
2007-04-30      0                           0                        0
2007-05-01      1                           0                        1
2007-05-02      2                           0                        1
2007-05-03      3                           0                        1
2007-05-04      3                           0                        0
2007-05-05      2                           1                        0
2007-05-06      1                           1                        0
2007-05-07      0                           1                        0
2007-05-08      0                           0                        0

I hope that makes sense,

Thanks again,

Phil.

Hi Phil,

Firstly, I believe you're going to need to create another table
(memory/temporary table) and populate it with each date within the date
range you specify. Then using SQL such as below, you can start to calculate
the information you're after:

ie, Create a temporary table called Dates (for the date range), and populate
1 record per date available within that range. Then run:

Select D.Date, Coalesce(Count(B.RecordID),0)
From Dates D
left outer join Bookings B on (B.In <= D.Date) and (B.Out >= D.Date)
Group by Date


This will give you a summary of rooms booked for the date range.

It was my thought that the following script should give you the date per
date range, PLUS the bookIns and BookOuts, but for some reason, when tested,
it didn't give the results I expected (all 3 columns populated with the same
figures):

Select D.Date, Coalesce(Count(B.RecordID),0), Coalesce(Count(B2.RecordID),0)
BookIns, Coalesce(Count(B3.RecordID),0) BookOuts
From Dates D
left outer join Bookings B on (B.In <= D.Date) and (B.Out >= D.Date)
left outer join Bookings B2 on (B2.In = D.Date)
left outer join Bookings B3 on (B3.Out = D.Date)
Group by Date


There may be a better way to do this, but you'll have to wait for one of the
others (they're smarter than me . In the mean time, this might help.

BTW - if anyone can see what I've done wrong with the 2nd SQL - I'd be
interested.

Cheers

Adam.

Hi Adam,

Thanks alot for taking a look. It's a trickey one and your right about
first building a temp table filled with the complete date range.

I still have not cracked it yet and I can't get passed the problem you
had where using multiple "Coalesce" on the same table but named B, B1,
B2 etc is actually picking the same data for each result column.

Thanks again for your effort, appreciate it,

Phil.

Adam,

<< Firstly, I believe you're going to need to create another table
(memory/temporary table) and populate it with each date within the date
range you specify. >>

Agreed.  He'll then have to loop through the table navigationally and run a
query for the stats for each date.


<< It was my thought that the following script should give you the date per
date range, PLUS the bookIns and BookOuts, but for some reason, when tested,
it didn't give the results I expected (all 3 columns populated with the same
figures): >>

Yes, you can't separate output values based upon joins.  You need grouped
sub-queries to do that, and unfortunately DBISAM cannot execute sub-queries
like that (ElevateDB can).

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Phil,

> Thanks alot for taking a look. It's a trickey one and your right about
> first building a temp table filled with the complete date range.
>
> I still have not cracked it yet and I can't get passed the problem you had
> where using multiple "Coalesce" on the same table but named B, B1, B2 etc
> is actually picking the same data for each result column.
>
> Thanks again for your effort, appreciate it,

No problems - I actually enjoyed giving it a shot.  

As per Tim's comment - it looks like we can't do it the way I was trying,
however try the SQL below. it seems to work for me. (Using multiple queries
and a memory table):

Hope this helps.

Cheers

Adam.

Select D.Date, Coalesce(Count(B.RecordID),0) Booked, 0 BookIns, 0 BookOuts
Into Memory\MyTable1
From Dates D
left outer join Bookings B on (B.In <= D.Date) and (B.Out >= D.Date)
Group by Date
union all
Select D.Date, 0 Booked, Coalesce(Count(B.RecordID),0) BookIng, 0 BookOuts
From Dates D
inner join Bookings B on (B.In = D.Date)
Group by Date
union all
Select D.Date, 0 Booked, 0 BookIng, Coalesce(Count(B.RecordID),0) BookOuts
From Dates D
inner join Bookings B on (B.Out = D.Date)
Group by Date
;
Select Date, Sum(Booked) Booked, Sum(Bookins) BookIns, Sum(BookOuts)
BookOuts
From Memory\MyTable1
group by Date
order by Date

Awesome Adam,

You're a legend, it works perfectly and it's so much faster than
scripting / looping.

I can't thank you enough and also a thank you to Tim for keeping us on
the straight and narrow. I will be upgrading to ElevateDB over te next
12 months as our software upgrades take place, but for now that does the
trick,

THANKS GUYS,

Phil.

Hi Phil,

> You're a legend, it works perfectly and it's so much faster than scripting
> / looping.

Glad to hear it was helpful. I must admit, comming from a paradox/BDE
background, one of the features I still smile at almost every time I use it,
is the ability to do queries on queries with memory tables in DBISam like
that. I just love that freedom!  

Have a great week!

Adam.

Adam,

<< As per Tim's comment - it looks like we can't do it the way I was trying,
however try the SQL below. it seems to work for me. (Using multiple queries
and a memory table): >>

Nice job.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:471ED2B2-ED88-41CC-ACE7-BB5B8E6CF015@news.elevatesoft.com...
> Adam,
>
> << As per Tim's comment - it looks like we can't do it the way I was
> trying, however try the SQL below. it seems to work for me. (Using
> multiple queries and a memory table): >>
>
> Nice job.

Thanks Tim. (Although don't ask me how to do the same thing in EDB at the
moment ) <vbg>

Adam,

<< Thanks Tim. (Although don't ask me how to do the same thing in EDB at the
moment ) <vbg>  >>

Just use CREATE TEMPORARY TABLE..AS instead of SELECT..INTO.   Then just
refer to the temporary table in the subsequent queries, and be sure to DROP
the temporary table when you're done.

--
Tim Young
Elevate Software
www.elevatesoft.com