Good Afternoon
Crystal 11
MYSQL 5.2
In mysql to declare a set I use @ at the beginning, problem I have there is Crystal doen't understand this, what do I use instead of @
my code as follows
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 11
MYSQL 5.2
In mysql to declare a set I use @ at the beginning, problem I have there is Crystal doen't understand this, what do I use instead of @
my code as follows
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
;