Crystal Reports Crystal Reports

Report Design

Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design

Topic: Crystal Reports - COALESCE

Author	Message
bryanrevelant Newbie Joined: 03 Apr 2015 Online Status: Offline Posts: 8	Topic: Crystal Reports - COALESCE Posted: 06 May 2015 at 3:13am
	Does any one know the equivilent to the below statement in record selection for Crystal reports. and '2015-05-05' BETWEEN vs.start_date AND COALESCE(v.discharge_date,CURRENT_TIMESTAMP) I tried the below but this doesnt produce {?Date} IN {AvailableBedsSTJ.start_date} TO (if isnull({AvailableBedsSTJ.discharge_date}) THEN CurrentDateTime)
	IP Logged


DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 06 May 2015 at 4:16am
	what about it 'does not produce'?
	IP Logged

bryanrevelant Newbie Joined: 03 Apr 2015 Online Status: Offline Posts: 8	Posted: 06 May 2015 at 4:33am
	Good Question. There are records that are coming back with a start_date of 12/28/2014 and a discharge date of 12/27/2014
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 06 May 2015 at 4:44am
	?Date is a Date datatype parameter, correct? StartDate and Discharge date are both datetime fields, correct? you are comparing at a detail row level, not a grouping on a bed# (or its equivalent), correct? What is your entire select statement? Are you doing any outer joins? you can also insert "debugging" formulas to test out how it is reading your data example: isnull({AvailableBedsSTJ.discharge_date}) should return a T/F on each row
	IP Logged

bryanrevelant Newbie Joined: 03 Apr 2015 Online Status: Offline Posts: 8	Posted: 06 May 2015 at 4:54am
	Date is my parm. StartDate and discharge are both date time. I would like to group by each coloumn. I am trying to produce a report that will bring people in the current room based on the Date parm they choose. I an really looking for the count. I tried the below which works to an extent but I think something is still off. {?Date} IN {AvailableBedsSTJ.start_date} TO (if isnull({AvailableBedsSTJ.discharge_date}) THEN CurrentDateTime else {AvailableBedsSTJ.discharge_date} )
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 06 May 2015 at 5:02am
	ug, I need more coffee. Yes you would need to add in the ELSE to use the discharge date value when it is not null. What do you think is still off?
	IP Logged

bryanrevelant Newbie Joined: 03 Apr 2015 Online Status: Offline Posts: 8	Posted: 06 May 2015 at 5:59am
	i Guess I am not to sure yet. The reason is because my SQL is producing less records than my crystal report. Not many more but something is not right. I may repost another question.
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 06 May 2015 at 9:06am
	what is your sql?
	IP Logged

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum