LS

I have a table with boats (id_boat) and a table with orders (id_order,
id_boat, rentedfrom, rentedtill).

boat1 is rented from 1-1 till 3-1
boat2 is rented from 1-1 till 2-1
boat3 is rented from 1-1 till 5-1
boat4 is rented from 1-1 till 3-1

How can I query this so that the result is:

2007-01-01    4
2007-01-02    4
2007-01-03    3
2007-01-04    1
2007-01-05    1

where the second field is the amount of boats rented that day.

dbIsam 3.3

Regards, Harry

Harry


Point 1: wrong ng

Point 2: I don't think you can. Even if DBISAM V3 supported pivot (right word?) I don't think you could achieve it because the raw data has to be interpreted first. Code or code + sql - no problem. Pure sql - nope (unless one of the real guru's know's better). I couldn't even think of a way with memory tables and scripts.

Roy Lambert [Team Elevate]

Roy,

> ...wrong ng
Well... I thought that there wasn't a dbisam.sql NG anymore. Could be wrong
though.

> ...I couldn't even think of a way with memory tables and scripts.
Neither did I. Lost lots of hair during the process

> ...unless one of the real guru's know's better.
That was what I was hoping for (better their hair then mine!).

So any of you bold wizards: can this be done?

Regards, Harry







"Roy Lambert" <roy.lambert@skynet.co.uk> schreef in bericht
news:2CBF1BA4-9870-48A9-9EE8-F83D6A93B606@news.elevatesoft.com...
> Harry
>
>
> Point 1: wrong ng
>
> Point 2: I don't think you can. Even if DBISAM V3 supported pivot (right
word?) I don't think you could achieve it because the raw data has to be
interpreted first. Code or code + sql - no problem. Pure sql - nope (unless
one of the real guru's know's better). I couldn't even think of a way with
memory tables and scripts.
>
> Roy Lambert [Team Elevate]
>

Harry,

What I have done to accomplish this in the past was by writing a report
where I "hid" the detail band and created summary fields in either the
summary band or a header band. The hidden detail band would run but not
show all the individual records. Meanwhile, because the data was now
available in the report, I could display the summarized data by using a
"Count" Calculation type in a DBCalc component. FYI, I have done this
using ReportBuilder.

Not a SQL solution but it gets the job done.

--
Regards,
Jan Ferguson [Team Elevate]


Harry de Boer wrote:

<<<< ...unless one of the real guru's know's better.
<<That was what I was hoping for (better their hair then mine!).
<<
<<So any of you bold wizards: can this be done?

Jan


Not sure how even that could work. If Harry had a record for each day of boat hire rather than a start and finish date it could be done by grouping which is sort of what you're suggesting. Its the initial creation of a table with a record for each day of boat hire that I see as the stumbling block. If I could figure that out I could develop a script.

Roy Lambert [Team Elevate]

Roy Lambert wrote:

> Jan
>
>
> Not sure how even that could work. If Harry had a record for each day
> of boat hire rather than a start and finish date it could be done by
> grouping which is sort of what you're suggesting. Its the initial
> creation of a table with a record for each day of boat hire that I
> see as the stumbling block. If I could figure that out I could
> develop a script.
>
> Roy Lambert [Team Elevate]

Harry

Do you have a fixed date range to report on e.g. a weekly report?

If so perhaps you could do something like this:-
==========================================================
SELECT boatid, {ReportDate} AS BookedDate
TO MEMORY Temp /* note ancient syntax! */
FROM YourTable
WHERE rentedfrom = {ReportDate}
;
INSERT INTO MEMORY Temp
(boatid,BookedDate)
SELECT boatid, {ReportDate} + 1  
FROM YourTable
WHERE {ReportDate} + 1 BETWEEN rentedfrom AND rentedto
;
.... repeat for {ReportDate} + 2 to {ReportDate + 6}
;
SELECT BookedDate, COUNT(*)
FROM MEMORY Temp
GROUP BY BookedDate
==========================================================

Use a string replace function to replace the {ReportDate} with your
week beginning date and voila!



--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz
+
Joan and Jeff Cook
The Cooks Oasis
www.cookislandsoasis.com

Jeff,

The way it should be is that a user views a yearcalender where on every day
the amount of boats rented is shown, so the data that must be collected is a
year's data.

I will look into your suggestion though. I could see this happen in a loop
in an SP script, but as we're on dbIsam 3.3 that's a no go.

Regards, Harry


"Jeff Cook" <jeffc@aspect.co.nz> schreef in bericht
news:5183B611-79CD-4617-8BDD-4D3D8E06D4EA@news.elevatesoft.com...
> Roy Lambert wrote:
>
> > Jan
> >
> >
> > Not sure how even that could work. If Harry had a record for each day
> > of boat hire rather than a start and finish date it could be done by
> > grouping which is sort of what you're suggesting. Its the initial
> > creation of a table with a record for each day of boat hire that I
> > see as the stumbling block. If I could figure that out I could
> > develop a script.
> >
> > Roy Lambert [Team Elevate]
>
> Harry
>
> Do you have a fixed date range to report on e.g. a weekly report?
>
> If so perhaps you could do something like this:-
> ==========================================================
> SELECT boatid, {ReportDate} AS BookedDate
> TO MEMORY Temp /* note ancient syntax! */
> FROM YourTable
> WHERE rentedfrom = {ReportDate}
> ;
> INSERT INTO MEMORY Temp
> (boatid,BookedDate)
> SELECT boatid, {ReportDate} + 1
> FROM YourTable
> WHERE {ReportDate} + 1 BETWEEN rentedfrom AND rentedto
> ;
> ... repeat for {ReportDate} + 2 to {ReportDate + 6}
> ;
> SELECT BookedDate, COUNT(*)
> FROM MEMORY Temp
> GROUP BY BookedDate
> ==========================================================
>
> Use a string replace function to replace the {ReportDate} with your
> week beginning date and voila!
>
>
>
> --
> Jeff Cook
> Aspect Systems Ltd
> www.aspect.co.nz
> +
> Joan and Jeff Cook
> The Cooks Oasis
> www.cookislandsoasis.com

Harry


Unless you're looking for cross platform compatibility don't get to hung up on sql. With the bit extra data you've given what I'd suggest is:

1. Select the years data into a memory table (SELECT boat, if(startdate>=yearstart,startdate - yearstart,1) AS starting, if(enddate<=yearend,enddate - yearstart,365 /*ignore leapyears*/) AS ending INTO MEMORY\Temp WHERE (startdate BETWEEN yearstart AND yearend) OR (enddate BETWEEN yearstart and yearend)
2. create and initialise an array of 365 integers to zero
3. wizz through the memory table and do a for..next loop for i:= starting to ending do array[i]:=array[i]+1
4. display the results

Should be fairly nippy.

Roy Lambert [Team Elevate]

Harry de Boer wrote:

> Jeff,
>
> The way it should be is that a user views a yearcalender where on
> every day the amount of boats rented is shown, so the data that must
> be collected is a year's data.
>
> I will look into your suggestion though. I could see this happen in a
> loop in an SP script, but as we're on dbIsam 3.3 that's a no go.
>
> Regards, Harry
>

As Roy says a memory table and code might be the way to go -
alternatively, sticking with my method - the INSERT statements could be
generated in a loop, ReportDate + 1 ... ReportDate + 365.  Probably
slower though.

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz
+
Joan and Jeff Cook
The Cooks Oasis
www.cookislandsoasis.com

Harry

If you want to do this as purely SQL you could achieve this using a table of
integers.

Create a table Integers with one field i.  Populate it with integers between
0 to 365 (or however may days you want returned).

SELECT CAST('2008-01-01' AS DATE) + i AS BookingDate, COUNT(id_boat) AS
BOOKED FROM Integers
LEFT OUTER JOIN Orders ON
CAST('2008-01-01' AS DATE) + Integers.i >= Orders.RentedFrom AND
CAST('2008-01-01' AS DATE) + Integers.i <= Orders.RentedTo
WHERE i BETWEEN 0 AND CAST('2008-12-31' AS DATE)-CAST('2008-01-01' AS DATE)
GROUP BY BookingDate
ORDER BY BookingDate

Replace start and end dates with strings or parameters as required.

Cheers

John

"Harry de Boer" <harry@staaf.nl> wrote in message
news:9A4AA200-DD3C-4292-9F3D-507454D7A063@news.elevatesoft.com...
> Jeff,
>
> The way it should be is that a user views a yearcalender where on every
day
> the amount of boats rented is shown, so the data that must be collected is
a
> year's data.
>
> I will look into your suggestion though. I could see this happen in a loop
> in an SP script, but as we're on dbIsam 3.3 that's a no go.
>
> Regards, Harry
>
>
> "Jeff Cook" <jeffc@aspect.co.nz> schreef in bericht
> news:5183B611-79CD-4617-8BDD-4D3D8E06D4EA@news.elevatesoft.com...
> > Roy Lambert wrote:
> >
> > > Jan
> > >
> > >
> > > Not sure how even that could work. If Harry had a record for each day
> > > of boat hire rather than a start and finish date it could be done by
> > > grouping which is sort of what you're suggesting. Its the initial
> > > creation of a table with a record for each day of boat hire that I
> > > see as the stumbling block. If I could figure that out I could
> > > develop a script.
> > >
> > > Roy Lambert [Team Elevate]
> >
> > Harry
> >
> > Do you have a fixed date range to report on e.g. a weekly report?
> >
> > If so perhaps you could do something like this:-
> > ==========================================================
> > SELECT boatid, {ReportDate} AS BookedDate
> > TO MEMORY Temp /* note ancient syntax! */
> > FROM YourTable
> > WHERE rentedfrom = {ReportDate}
> > ;
> > INSERT INTO MEMORY Temp
> > (boatid,BookedDate)
> > SELECT boatid, {ReportDate} + 1
> > FROM YourTable
> > WHERE {ReportDate} + 1 BETWEEN rentedfrom AND rentedto
> > ;
> > ... repeat for {ReportDate} + 2 to {ReportDate + 6}
> > ;
> > SELECT BookedDate, COUNT(*)
> > FROM MEMORY Temp
> > GROUP BY BookedDate
> > ==========================================================
> >
> > Use a string replace function to replace the {ReportDate} with your
> > week beginning date and voila!
> >
> >
> >
> > --
> > Jeff Cook
> > Aspect Systems Ltd
> > www.aspect.co.nz
> > +
> > Joan and Jeff Cook
> > The Cooks Oasis
> > www.cookislandsoasis.com
>
>