best way to do stats from a select statement

[Akourou]

hello to all!

I hope you're getting on ok in the current crazy situation.

my question:
i have a select statement that collects data from several tables into a cursor.

SELECT bdate, platforma.descr, house.h_name, guest.descr, CDOW(checkin)+" "+DMY(checkin), nights,;
CDOW(checkin+nights)+" "+DMY(checkin+nights), price, deposit, ALLTRIM(booking.notes), booking.aa, house, checkin, bplatform, client.aa;
FROM booking;
JOIN house ON booking.house = house.aa;
JOIN platforma ON booking.bplatform = platforma.aa;
JOIN guest ON booking.guest = guest.aa;
JOIN client ΟΝ house.client = client.aa;
WHERE house=IIF(EMPTY(ALLTRIM(staform.bshouse.value)),house,val(staform.bshouse.value)) and;
bplatform=IIF(EMPTY(ALLTRIM(staform.bsplatform.value)),bplatform,VAL(staform.bsplatform.value))and;
checkin >= staform.wsdate.value AND checkin <= staform.wedate.value and;
bookopt(booked, canceled, staform.bookoption.value) and;
house.client=IIF(EMPTY(ALLTRIM(staform.bsclient.value)),house.client,val(staform.bsclient.value));
ORDER BY &wsort;
INTO CURSOR eResults

this date represents bookings on several properties that belong to different clients.
these bookings are from different platforms.

i want to get statistics from this data like total rental days per house and then per platform,
total earnings per house and per platform etc.

what is the best way to do that?

do i scan the date several times and collect results in arrays?
do i use calculate or sum commands?
is there a way to do that while collecting the data into the cursor?

thank you
Andreas

 

[TamarGranor]

Sounds like you should be able to do queries against this cursor to get what you want. For example, to get total rental days per house (assuming what you need is the total of the nights field for each house), you can use:

[pre]
SELECT h_name, SUM(nights) ;
FROM eResults ;
GROUP BY h_name ;
INTO CURSOR csrHouseNights
[/pre]

Other, similar queries answer your other questions.

Tamar

 

[Akourou]

thank you Tamar,

this select statement is very clear.

is it possible to include more analysis in the same statement?

for example the above statement calculates total nights per house.
can i have more sum() in the same statement to analyze these total nights according to platform type?
for example: HouseA, total nights:90 Airbnb:30 Booking:20 Homeaway:20 Direct:20

or i have to issue separate select statements for that?

 

[TamarGranor]

The key is the GROUP BY clause. In the example I gave you, we're grouping by h_name, so you can compute any statistics you want by h_name.

If you want numbers for h_name by platform, then use GROUP BY h_name, platform and add platform to the query:

[pre]
SELECT h_name, platform, SUM(nights) ;
FROM eResults ;
GROUP BY h_name, platform ;
INTO CURSOR csrHouseNightsByPlatform[/pre]

You'll get one record per house per platform.

If what you really want is a crosstab where houses are the rows and platforms are the columns, take a look at this article:

http://www.tomorrowssolutionsllc.com/Articles/Generating crosstabs in VFP.pdf

Tamar

 

[Akourou]

thank you Tamar,

your article about crosstab is very informative and solves many of my issues.
The only problem is that the link for Alexander Golovlev fastxTab is no longer available.
Do you know if there is another link to that download?

thank you
Andreas

 

[TamarGranor]

Use the updated version from Vilhelm-Ion Praisach instead.

http://praisachion.blogspot.com/2015/02/fastxtab-version-16.html

has the download link for the latest version.

Tamar