Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 10 of 12 total |
Getting various forecast counts into a table. |
Sat, Aug 25 2007 5:23 AM | Permanent Link |
P.Read | 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. |
Mon, Aug 27 2007 6:12 AM | Permanent Link |
"Adam H." | 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. |
Mon, Aug 27 2007 5:33 PM | Permanent Link |
Phil Read | 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. |
Mon, Aug 27 2007 5:36 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Aug 27 2007 8:12 PM | Permanent Link |
"Adam H." | 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 |
Tue, Aug 28 2007 1:29 AM | Permanent Link |
Phil Read | 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. |
Tue, Aug 28 2007 1:41 AM | Permanent Link |
"Adam H." | 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. |
Tue, Aug 28 2007 7:38 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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. -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Aug 30 2007 7:25 PM | Permanent Link |
"Adam H." | "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> |
Sat, Sep 1 2007 8:32 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |