Hi Guys,

I thought using EDB there's probably a very fast / nifty way to grab
daily stats for say month.

Let me explain, I am producing daily stats for occupancy based on the
arrival / departure times of a table.

For example rather than writing a loop around this query, I want to LOOP
using the SQL so I get a result of daily occpuancy for 30 days of occupancy

2012-06-01, 2012-06-01, 2012-06-01, 2012-06-01
----------------------------------------------
12          5           9           12   etc...


basic example in theory...

var LOOPDATE
var startdate = 2012-06-01
var enddate = 2012-06-03

FOR (LOOPDATE = startdate; LOOPDATE <= enddate; LOOPDATE ++)
{
SELECT COUNT(*) AS dailyoccupancy FROM bookings
WHERE ar_time < DATE 'LOOPDATE'
AND dep_time >= DATE 'LOOPDATE'
}

Dear Phil

SELECT
 CAST( ar_time as CHAR(7)) as YearMonth,
 COUNT(*) AS dailyoccupancy
FROM bookings
WHERE ar_time < DATE 'LOOPDATE'
AND dep_time >= DATE 'LOOPDATE'

GROUP BY YearMonth

Will do it.

Remember with GROUPING you can group by anything you can CAST into a field in the SQL ...

Hi Adam,

Thanks for the reply... I'm not sure I explained it very well.

I'm trying to loop through the count of dates (one day at a time say 30
in total i.e from startdate to enddate)

And in the loop run an INSERT INTO SELECT query to to get the occupancy
for each day as I loop through one day at a time from startdate until it
reaches the enddate.

Was that any clearer? It might be possible to have a query do the whole
thing without a loop being involved, but I'm not 100% and so thought a
loop would be a start.

Thanks,

Phil.

Phil


I think you already pretty much have your answer in the response that John Hay gave you about invoice aging. "All" you need to do is generate the query based on the dates you want. Rather than actual date think in terms of days ago eg 2012-06-01 becomes 12. You have to sort out column headers but that's about it.

If that approach doesn't work you can define an SQL function either using SQL/PSM or Delphi (which I get the impression is not what you're using) but I have difficulty envisaging anything that would work in-line in a query and not be extremely inefficient. Others may have a better envisaging facility.

I also seem to recall someone else having the same sort of question (I think it was boat bookings) and John Hay came up with a really neat solution. Its worth searching both the ElevateDB & DBISAM ngs for his name and seeing if you can spot it.

Roy Lambert [Team Elevate]

Phi
l" <phil@vizualweb.com> wrote in message news:4074D1B1-46C6-4587-89E3-CDB15C5A1008@news.elevatesoft.com...
> Hi Guys,
>
> I thought using EDB there's probably a very fast / nifty way to grab
> daily stats for say month.
>
> Let me explain, I am producing daily stats for occupancy based on the
> arrival / departure times of a table.
>
> For example rather than writing a loop around this query, I want to LOOP
> using the SQL so I get a result of daily occpuancy for 30 days of occupancy
>
> 2012-06-01, 2012-06-01, 2012-06-01, 2012-06-01
> ----------------------------------------------
> 12          5           9           12   etc...
>
>

Presuming you want a single row (ie a crosstab) it requires lots of typing or for a dynamic number of columns a
stored proc (excuse the casing) - something like

CREATE PROCEDURE "Occupancy" (IN "startdate" DATE, IN "numdays" INTEGER)
BEGIN
declare stmt varchar;
declare cnt integer;
declare mycursor cursor with return for stm;
set stmt='select ';
set cnt = 0;
while cnt < numdays-1 do
 set stmt=stmt+'sum(if(arr_time <= date'''+cast(startdate+(cast(cnt as interval day)) as char(10))+
 ''' and dep_time > date'''+cast(startdate+(cast(cnt as interval day)) as char(10))
 +''' then 1 else 0)) as "D'+cast(startdate+(cast(cnt as interval day)) as char(10))+'",';
 set cnt=cnt+1;
end while;
set stmt=stmt+'sum(if(arr_time < date'''+cast(startdate+(cast(numdays-1 as interval day)) as char(10))+
''' and dep_time > date'''+cast(startdate+(cast(numdays-1 as interval day)) as char(10))+
''' then 1 else 0)) as "D'+cast(startdate+(cast(numdays-1 as interval day)) as char(10))+'"';
set stmt = stmt+' from bookings';
prepare stm from stmt;
open mycursor;
END

John

Thanks very much John, that was perfect and even more useful being it's
in a procedure.

Starting to get to grips with more of EDB features and as the penny
drops am getting answers to lots of 'why' questions I initially had.

Cheers,

Phil.

Phi

> Thanks very much John, that was perfect and even more useful being it's
> in a procedure.
>

No problem.  As an excercise spot the bug in the procedure

John

What was the bug?

Phil

In the statement just after the end while I used arr_time < date etc instead of arr_time <= date

John

Ah I see, I must admit when I used your answer I just looked at how you
had done it and placed my own logic in the code along with the field
names, ar_time as oppose to arr_time etc.

Thanks again,

Phil