Hi Everyone
Crystal Reports 11
MYSQL 5.2
Created a sql query that joins two areas together.
Have created set variables that work perfectly in MYSQL
but when putting into crystal they are not liking them.
Please you tell me what I am doing wrong.
Thank you for all your time.
The set@i create a incremental date i.e 31 rows for july 1st to 31st, need this to join the data, also it shows how many days they were on a treatment.
The query works perfectly in MYSQL.
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
;
Crystal Reports 11
MYSQL 5.2
Created a sql query that joins two areas together.
Have created set variables that work perfectly in MYSQL
but when putting into crystal they are not liking them.
Please you tell me what I am doing wrong.
Thank you for all your time.
The set@i create a incremental date i.e 31 rows for july 1st to 31st, need this to join the data, also it shows how many days they were on a treatment.
The query works perfectly in MYSQL.
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
;