Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: ?? on Finding rec in Date range that DONT exist Post Reply Post New Topic
Author Message
jbalbo
Senior Member
Senior Member
Avatar

Joined: 17 Feb 2011
Online Status: Offline
Posts: 219
Quote jbalbo Replybullet Topic: ?? on Finding rec in Date range that DONT exist
    Posted: 31 Jan 2012 at 7:33am
Any ideas on if I have a start date and end date and for each day there should be a specific value say SD="other" if a value is not there for that day I want to display that date..

example..
Start Date = 1/1/2012
End Date = 1/31/2012

Go get the records....

SD=other on 1/1/2012... display nothing
SD <>other on 1/2/2012 display 1/2/2012..

Thanks In Advance

Joe



Edited by jbalbo - 31 Jan 2012 at 7:36am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 31 Jan 2012 at 7:46am
do you always have a value on each day or are there days with no values at all?
IP IP Logged
jbalbo
Senior Member
Senior Member
Avatar

Joined: 17 Feb 2011
Online Status: Offline
Posts: 219
Quote jbalbo Replybullet Posted: 31 Jan 2012 at 9:49am
Thanks for getting back..
Yes, there are days with no data..


IP IP Logged
rkrowland
Senior Member
Senior Member
Avatar

Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
Quote rkrowland Replybullet Posted: 01 Feb 2012 at 1:30am
create a formula;
 
if {table.date} >= {?startdate}
and {table.date} <= {?enddate}
and {table.sd} <> "Other"
then {table.date}
else date(1901,01,01)
 
Then create record select filter that selects records based on that formula not equal to 01/01/1901.
 
Regards,
Ryan.


Edited by rkrowland - 01 Feb 2012 at 1:33am
IP IP Logged
jbalbo
Senior Member
Senior Member
Avatar

Joined: 17 Feb 2011
Online Status: Offline
Posts: 219
Quote jbalbo Replybullet Posted: 01 Feb 2012 at 2:47am
Thanks for getting back ...

what does the "other" do ?

so what happens if the date is 1/1/2012 and
there is no data for that date , that's the date I want to show ..
if 1/2/2012 has data I don't want to show it...

Thanks



Edited by jbalbo - 01 Feb 2012 at 2:50am
IP IP Logged
rkrowland
Senior Member
Senior Member
Avatar

Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
Quote rkrowland Replybullet Posted: 01 Feb 2012 at 3:05am
I assumed from your description you wanted to show a date for anything where SD <> "Other" (That is what you put in your formula lol)
 
Try the following instead;
 
if {table.date} >= {?startdate}

and {table.date} <= {?enddate}

and isnull({table.sd})
 
then {table.date}

else date(1901,01,01)
 
Regards,
Ryan.
 
EDIT: I think I misunderstood your original post, you're saying if there are no records at all on a date - you want to know that date? I originally thought you wanted to return the dates of records where a fields had no data.
 
If that is what you want I don't think it's possible using Crystal directly, you'd need to write a SQL command.
 
Regards,
Ryan.

 


Edited by rkrowland - 01 Feb 2012 at 3:13am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 01 Feb 2012 at 3:49am

some people use (or create) a source table that is basically just a calendar, one row per calendar day. You can then outer join your data to this table to inlcude in the missing days.

If you can't do that you can write variable formulas to insert text for missing days. Keep in mind these are not new rows of data but just additional text with carriage returns to expand an existing row to make it look like multiple rows.
IP IP Logged
Post Reply Post New Topic
Printable version 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



This page was generated in 0.016 seconds.