Minimum Date within a Group

[ternas1]

Hi, I'm using CR 2008. I have a report grouped by patient account number and transfer in date.
Each account can have multiple transfer in and transfer out dates. I want to display all the dates and times, and add a field that counts patients who are new for that day.

For example, on 12/1/2010 Patient B below transferred in on that date. I would like to add a field that shows a "1" or a "0" next to each line and summarize that field in a group footer.

Desired output:

Acct Transfer In Transfer Out New Patient?
Patient A 11/29/2010 4:23PM 11/29/2010 11:59PM 0

Patient B 12/1/2010 7:32AM 12/1/2010 9:01AM 1
Patient B 12/1/2010 9:01AM 12/1/2010 4:13PM 0
Total 1

The formula I started with is: if {TransferInDate} = date(2010,12,01) then 1. However, it returns a 1 on every line rather than just the first line.

I tried adding Minimum({TransferInDate}) to the above formula, but I can't summarize that field.

Running Totals can show the first and last date for each patient, but I can’t summarize those fields either.

Any way to do this?

 

[lbass]

Create a SQL expression {%mindate} like this:

(
select min(`transferindate`)
from table A
where A.`account` = table.`account`
)

Replace table in both places with actual table name (leave "A" as is). Correct the punctuation to make it appropriate to your datasource.

Then you can change your formula to:

if {table.transferindate} = {%mindate} then 1

You can insert a sum on this.

-LB

 

[ternas1]

I added the text, but I got 1's on all minimum dates. I need to limit the count to just a single date. In this case it's 12/1/2010 so I added the second line in the where clause.

(
select min(TransferInTS)
from edw.lw.vPAT_LOCATION A
where
A.LWAcctNum = vPAT_LOCATION.LWAcctNum
and TransferInTS > '2010-12-1' --new line
)

Without that last line 11/30/2010 would have a 1, but they aren't a new patient on 12/1/2010.

Output now looks like:
TransferIn TransferOut New Patient?
Patient C 11/30/2010 12/1/2010 0
Patient C 12/1/2010 12/2/2010 1 (error)

 

[lbass]

Don't change the SQL expression from my original formula. Change your formula to:

if {table.transferindate} = {%mindate} and
{table.transferindate} = {?DateToEvaluate} then 1

Or you could add a parameter to the record selection formula that limits the dates shown to the desired date. The SQL expression would independently check the database values.

-LB