Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 10 total |
count rented boats per day |
Thu, Apr 17 2008 11:23 AM | Permanent Link |
"Harry de Boer" | 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 |
Thu, Apr 17 2008 11:38 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Thu, Apr 17 2008 12:08 PM | Permanent Link |
"Harry de Boer" | 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] > |
Thu, Apr 17 2008 1:22 PM | Permanent Link |
Jan Ferguson Data Software Solutions, Inc. 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? |
Thu, Apr 17 2008 2:05 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Thu, Apr 17 2008 3:52 PM | Permanent Link |
"Jeff Cook" | 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 |
Fri, Apr 18 2008 2:49 AM | Permanent Link |
"Harry de Boer" | 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 |
Fri, Apr 18 2008 3:25 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Fri, Apr 18 2008 4:27 PM | Permanent Link |
"Jeff Cook" | 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 |
Mon, Apr 21 2008 7:32 AM | Permanent Link |
"John Hay" | 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 > > |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |