×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SQL Command in Crystal

SQL Command in Crystal

SQL Command in Crystal

(OP)
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

RE: SQL Command in Crystal

Please do not crosspost.

-LB

RE: SQL Command in Crystal

(OP)
Hi Ibass
Sorry for Cross posting, wasn't sure which area to put it into.

Have you come across the above issue before, not sure what to do, have you any ideas

Thank you
ralph

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close