OK, forgive my reformatting of your SQL, I just find using TEXT END and
laying out the SQL this way is much clearer and easier to make changes.
TEXT TO m.lcSql NOSHOW TEXTMERGE
SELECT sitename ;
,siteday;
,progstats.season ;
,COUNT(DATE) AS nights_open ;
,IIF(EMPTY(siteinfo.deactivated),.T.,.F.) AS ActiveStatus ;
,SUM(fed) AS sum_fed
,SUM(sleep) AS sum_sleep;
,SUM(men) AS sum_men
,SUM(women) AS sum_women;
,SUM(youth) AS sum_youth ;
,siteinfo.siteid
,siteinfo.deactivated ;
FROM progstats ;
INNER JOIN siteinfo ON progstats.sitekey = siteinfo.siteid ;
GROUP BY ;
sitename;
,siteday;
,season;
,siteid;
,deactivated ;
ORDER BY ;
sitename;
,season ;
INTO CURSOR sums
ENDTEXT
TRY
&lcSql
CATCH
* you could use AERRORS to display the error, I normally
display at least laError[1] and [2]
AERROR(laErrors)
=MESSAGEBOX("Error in SQL " + laError[1],16,"CANNOT PROCEED")
=STRTOFILE(lcSql,'Errors.txt',0)
ENDTRY
* now get the same data in a separate cursor
SELECT *;
FROM sums;
INTO CURSOR sums2
* then join the two, note the season - 1
SELECT sums.*;
,sums2.nights_open as nights_open2
,sums2.sum_fed as sum_fed2
, < any other fields you want >
FROM sums;
LEFT JOIN sums2 ON sums.sitename = sums2.sitename;
AND sums.siteday = sums2.siteday;
AND sums.season = sums2.season - 1;
AND sums.siteid = sums2.siteid
Frank.
Frank Cazabon
On 13/03/2013 09:14 AM, Michael Savage wrote:
The year field is season.
Mike
On 13/03/2013 9:06 AM, Frank Cazabon wrote:
Which field is the year field?
Frank.
Frank Cazabon
On 13/03/2013 08:51 AM, Michael Savage wrote:
lcSql = lcSql + ' SELECT sitename , siteday, progstats.season, '
lcSql = lcSql + ' COUNT(DATE) AS nights_open, '
lcSql = lcSql + ' IIF(EMPTY(siteinfo.deactivated),.T.,.F.) AS
ActiveStatus, '
lcSql = lcSql + ' SUM(fed) AS sum_fed, SUM(sleep) AS sum_sleep,'
lcSql = lcSql + ' SUM(men) AS sum_men,SUM(women) AS sum_women,
SUM(youth) AS sum_youth, '
lcSql = lcSql + ' siteinfo.siteid, siteinfo.deactivated '
lcSql = lcSql + ' FROM progstats INNER JOIN siteinfo '
lcSql = lcSql + ' ON progstats.sitekey=siteinfo.siteid '
lcSql = lcSql + ' GROUP BY sitename, siteday, season, siteid,
deactivated '
lcSql = lcSql + ' ORDER BY sitename, season '
lcSql = lcSql + ' INTO CURSOR sums '
[excessive quoting removed by server]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
