Crystal Reports SQL Commands

Message


   HI
Crystal 11
MYSQL 5.2
 
Trying to work out what syntax to use in the following query that has 
set @=-1, crystal doen't seem to like them any ideas of what to use instead
 
set @i = -1;
set @StartDate = '2012-07-01';
set @EndDate = '2012-07-31';
 
##Combined Data
SELECT HDPD.*,
p.idnam as `Surname` ,
p.idfnam as `Forename`,
date(p.iddob) as `DOB`,
p.idnhs as `NHSNo`,
p.idpcd as `Postcode`,
 ((date_format(`PbRDate`,'%Y') - date_format(  p.IDDOB ,'%Y')) - (date_format(`PbRDate`,'00-%m-%d') < date_format(  p.IDDOB ,'00-%m-%d'))) AS  `Age` ,
##GP Data Area Add in Practice PCT and Code only##
p.practicelnk,
concat_ws(', ', e_practice.practice_name) as Practice_Name,
e_practice.Practice_add1 as Prac_Postcode,
e_practice.Practice_code,
## BBV Data Area
(select BVHCV from p_bbv as hepc where hepc.fk_oid=P.oid and BVHCV is not null order by bvdat desc limit 1) as hcv,
(select BVHIV from p_bbv as hiv where hiv.fk_oid=P.oid and BVHIV is not null order by bvdat desc limit 1) as hiv,
(select bvbsag from p_bbv as bvbsag where bvbsag.fk_oid=P.oid and bvbsag is not null order by bvdat desc limit 1) as bvbsag
 
# PD DATA EXTRACT UNION HD DATA EXTRACT
FROM ((
##PD DATA EXTRACT
SELECT
pd.`fk_oid` as `PatientID`,
pd_temp.`PbRDate`,
Date(pd.`TVDATS`)as `TlDate`,
Date(pd.`TVDATF`) as `TlEndDate`,
Date(IF(@StartDate>TVDATS,@StartDate, TVDATS)) as `From Date`,
Date(IF(@EndDate>TVDATF, TVDATF,@EndDate)) as `End Date`,
'PD' as `Mode`,
sp_getcodecode(pd.`TVMOD`) as `ModeOfCare`,
sp_getcodecode(pd.`TVCEN`) as `Centre`,
'Home' as `Location`,
'PD Catheter' as `Access`
FROM p_newtimeline pd
INNER JOIN
 
(SELECT DATE(ADDDATE(@StartDate, INTERVAL @i:=@i+1 DAY)) AS `PbRDate`
FROM `p_chemistry1`
HAVING  @i < DATEDIFF(@EndDate, @StartDate)) as pd_temp
 
where (`TVMOD` IN (183204,183205,183206))
and (`TVDATF` is null or (`TVDATF` between @StartDate and @EndDate))
and `TVDATS`<`PbRDate`
and Date(IF(@EndDate>TVDATF, TVDATF,@EndDate))>=`PbRDate`
)
 
UNION
 
# HD DATA EXTRACT
(Select
ob.fk_oid as `PatientID`,
date(ob.DIDAT) as `PbRDate`,
(SELECT   date(max(p_newtimeline.tvdats)) as maxtl
  FROM p_anthropometry as obsq
  inner join p_newtimeline
  on p_newtimeline.tvdats<=obsq.didat
  and p_newtimeline.fk_oid=obsq.fk_oid
  where obsq.fk_oid=ob.fk_oid and obsq.didat=ob.didat
  group by obsq.didat ) as `TlDate`,
'' as `TlEndDate`,
'' as `From Date`,
'' as `End Date`,
'HD' as `Mode`,
sp_getcodecode((SELECT TVMOD FROM p_newtimeline as Cen
where cen.fk_oid=`PatientID` and TVDATS=(SELECT   date(max(p_newtimeline.tvdats)) as maxtl
  FROM p_anthropometry as obsq
  inner join p_newtimeline
  on p_newtimeline.tvdats<=obsq.didat
  and p_newtimeline.fk_oid=obsq.fk_oid
  where obsq.fk_oid=ob.fk_oid and obsq.didat=ob.didat
  group by obsq.didat ) order by TVDATS desc limit 1)) as `ModeOfCare`,
sp_getcodecode((SELECT TVCEN FROM p_newtimeline as Cen
where cen.fk_oid=`PatientID` and TVDATS=(SELECT   date(max(p_newtimeline.tvdats)) as maxtl
  FROM p_anthropometry as obsq
  inner join p_newtimeline
  on p_newtimeline.tvdats<=obsq.didat
  and p_newtimeline.fk_oid=obsq.fk_oid
  where obsq.fk_oid=ob.fk_oid and obsq.didat=ob.didat
  group by obsq.didat ) order by TVDATS desc limit 1)) as `Centre`,
loc.codetext as `Location`,
acc.codetext as `Access`
FROM p_anthropometry as ob
left join codes as loc on loc.proid=ob.haloc
left join codes acc on acc.proid=ob.access_used
 -- inner join p_newtimeline  on p_newtimeline.fk_oid=ob.fk_oid
 
Where
ob.didat >= @StartDate and
ob.didat <= @EndDate and
ob.prepost=200101
order by ob.fk_oid, ob.didat)) as HDPD
 
## Add Patient and GP Data
inner join p_person p on P.oid=HDPD.`PatientID`
LEFT JOIN e_practice ON p.PracticeLnk = e_practice.oid
 
 
order by `PatientID`, `PbRDate`, `ModeOfCare` DESC
;