Selecting the first appointment date 1

[gromboy]

Hi,
I have a db where a hopsital patient has appointment dates of treatment. It is a date/time field.
I need to pull the first date for the patient from a list of appointment dates and calculate the difference between this and the last appointment date...
Does anyone know how this could be done ?
Any help greatly appreciated as ever...
Steve

 

[IanWaterman]

Group data by patient in group footer use formula

@daterange

datediff("d", minimum(datefield,patientgrpfield), maximum(datefield,patientgrpfield))


Ian

 

[gromboy]

Hi Ian,
I can't seem to get the formula to work...
The fields are called
{PATREGIM.CYCLEDATE} - Date/time field
{PATREGIM.DISTRICTNO} - the patients number varchar field

I have grouped the {PATREGIM.DISTRICTNO} field first and from this formula

datediff("d", minimum({PATREGIM.CYCLEDATE},{PATREGIM.DISTRICTNO})),
maximum({PATREGIM.CYCLEDATE},{PATREGIM.DISTRICTNO})))

get the following error..The remaining Text does not appear to be part of the formula..

I thought it was a ( or ) somewhere and despite working on it I cannot get it right..
Any ideas ?

 

[IanWaterman]

YOur brackets are wrong try

datediff("d", minimum({PATREGIM.CYCLEDATE},{PATREGIM.DISTRICTNO}),
maximum({PATREGIM.CYCLEDATE},{PATREGIM.DISTRICTNO}))

Ian

 

[gromboy]

Ian....thanks a bunch..works a treat

 

[gromboy]

Ian,
Following on from this...I have created a formula which groups together the output of the above formula.

It looks like this
IF {@weeks between last cycle and date of deat} IN (0 to 4) then "Up to 1 Month"
ELSE IF {@weeks between last cycle and date of deat} IN (5 to 8) then "1-2 Months"
ELSE IF {@weeks between last cycle and date of deat} IN (9 to 12) then "2-3 Months"
ELSE IF {@weeks between last cycle and date of deat} IN (13 to 24) then "3-6 Months"
ELSE IF {@weeks between last cycle and date of deat} IN (25 to 52) then "6 Months - 1 Year"
ELSE IF {@weeks between last cycle and date of deat} IN (53 to 104) then "1 - 2 Years"
ELSE IF {@weeks between last cycle and date of deat} IN (105 to 157) then "2 - 3 years"
ELSE IF {@weeks between last cycle and date of deat} IN (158 to 208) then "3 - 4 Years"
ELSE IF {@weeks between last cycle and date of deat} IN (209 to 261) then "4 - 5 Years"
ELSE IF {@weeks between last cycle and date of deat} IN (262 to 312) then "5 - 6 Years"
ELSE IF {@weeks between last cycle and date of deat}> 313 then "Over 6 Years"

I added the formula to a chart but I cannot group on this formula....

Any ideas why ?
Steve

 

[IanWaterman]

Its because the formula I gave you uses Summary data (min and Max). You can not use this for grouping.

To do what you want you will need to pre group the data using either a view or command and then report off that.

Ian

 

[gromboy]

OK...I thought it might be something to do with the min and max...

However a view or command ?....
I'm not sure how to do that....sorry....
Are you able to help ?
Steve

 

[IanWaterman]

A view is something created on your database, you need some one competant in SQL with access to your database. A command is a Crystal based SQL instruction again needs competance in SQL but as its created in Crystal it will not require any additional access rights to the database.

What is your database?

DateDiff is a standard function on MS SQL, for oracle you would need to write your own.

Ian

 

[gromboy]

My db is MS SQL 2005

 

[IanWaterman]

If you do not need the other records from Patient table ie those not equal to min and max replace your patient table with this command

select patientNo, districtno, minimum(cycledate) as MinDate,
minimum(cycledate) as MaxDate
from PATREGIM
group by patientNo, districtno

Change PatientNo to correct field name, add any other fields you require to both select and group by.

You can then use the Min and maxdates dates in your formula instead of the summaries. You shold then be able to group by this formula.

Ian

 

[gromboy]

I see...This is the query I use at the moment...
Could I add the min and max into this query and run it as a command ?

 SELECT "PATREGIM"."DISTRICTNO", "PATREGIM"."REGIME", "PATREGIM"."CYCLE", 
"PATREGIM"."CYCLEDATE", "PATREGIM"."ID", "PATREGIM"."STATUS", 
"PATREGIM"."MAXDAYS", "PATREGIM"."COST", "PATREGIM"."BAND", 
"PATREGIM"."AUTH", "PATREGIM"."SADATE", "PATREGIM"."SAHEIGHT", 
"PATREGIM"."SAWEIGHT", "PATREGIM"."SA", "PATREGIM"."CONS", 
"PATREGIM"."GFRDATE", "PATREGIM"."GFR", "PATREGIM"."GFR_TYPE", 
"PATREGIM"."CARENO", "PATREGIM"."TRIAL", "PATREGIM"."INTENTION", 
"PATREGIM"."GIVEPROTID", "PATREGIM"."ALLOUNAME", "PATREGIM"."ALLODATE", 
"PATREGIM"."ALLOTIME", "PATREGIM"."TRCODE", "PATREGIM"."EDTA", 
"DIAGNOSI"."DIAGNOSIS", "PAEDCODE2"."TEXT", "PMI"."DEATH_DATE", 
"PATDRUG"."CAUTH", "PATDRUG"."CONS", "PMI"."DOB"

 FROM   "WOSCAN_50G"."dbo"."PMI" "PMI" INNER JOIN 
((("WOSCAN_50G"."dbo"."PATREGIM" "PATREGIM" INNER JOIN 
"WOSCAN_50G"."dbo"."DIAGNOSI" "DIAGNOSI" ON 
("PATREGIM"."DISTRICTNO"="DIAGNOSI"."DISTRICTNO") AND 
("PATREGIM"."CARENO"="DIAGNOSI"."CARENO")) INNER JOIN 
"WOSCAN_50G"."dbo"."PATDRUG" "PATDRUG" ON 
(("PATREGIM"."CYCLE"="PATDRUG"."CYCLE") AND 
("PATREGIM"."DISTRICTNO"="PATDRUG"."DISTRICTNO")) AND 
("PATREGIM"."REGIME"="PATDRUG"."REGIME")) INNER JOIN 
"WOSCAN_50G"."dbo"."PAEDCODE2" "PAEDCODE2" ON 
"DIAGNOSI"."DIAG_CODE"="PAEDCODE2"."CODE") ON 
"PMI"."DISTRICTNO"="PATDRUG"."DISTRICTNO"

 WHERE  "PMI"."DEATH_DATE">={ts '1899-12-31 00:00:00'} AND 
"PATDRUG"."CAUTH"='Y'

 ORDER BY "PATREGIM"."DISTRICTNO", "PATREGIM"."CARENO"

Steve