Suppress repeating dates

[tinJeff]

helo...

How will i suppress values that have the same date?

 

Col1 Col2

1/1/2005 "entry1"

1/1/2005 "entry2"

1/2/2005 "entry"

 

I want to display only 1 entry per date.. I'm using Crystal Report XI.

 

please help....

[Puiu]

The last time i had the same problem i used a temporary table.

 

I created a temporary table with 2 columns(just like in your case)

Create #TempTbl (

MyDate datetime,

MyValue varchar(20)

)

Than i made an insert like insert into #TempTbl (MyDate) select distinct MyDate from MyTable

 

After that i did an update of the #TempTbl:

 

update t set t.MyValue = m.MyValue

from #TempTbl t, MyTable m

where t.MyDate = m.MyDate

 

and then a simple Select * from #TempTbl

 

Hope it helps

[tinJeff]

Puiu thanks for the reply. But I didn't understand how to do it. Where will I make a table, in the Crystal report?

By the way I am creating a DTR report. The report I am creating shows something like this:

Date ArrivalAM DepartureAm ArrivalPM DeparturePm

1/2/06 7:00 12:00 12:45 5:06

12:46 5:07

1/3/06 7:50 12:15 1:00 5:06

7:51 12:16

12:18

 

Now, I want that my report will only show the earliest time in all the four columns (ArrivalAm,DepartureAm,etc..). Please help....

[Nerseus]

As far as I can tell, you haven't given us enough info to help you. Your sample is:

Col1 Col2
1/1/2005 "entry1" 
1/1/2005 "entry2"
1/2/2005 "entry"

 

What logic determines what's in Col2 for the date 1/1/2005? Do you want entry1 or entry2? Or, you don't care?

 

-ner

[Arch4ngel]

What exactly do you want to do?

 

Of course CR is giving you this if "entry1" and "entry2" are not equal.

 

Like Nerseus is saying... what about those "entry" do you care for the value or not?

[penfold69]

Insert a CR "Group" that groups by Col1

 

Move everything from your "details" section into the header or footer of that group.

 

P.

[Puiu]

I thought of something, but i don't know whether it will help you or not!

 

So I assume you have a table named FlightPlan or something with the following columns: Date, ArrivalAm, DepartureAm, ArrivalPM, DeparturePM (btw, why do you have arrivalAm and ArrivalPM? )

 

Now you cannot

show the earliest time in all the four columns (ArrivalAm,DepartureAm,etc..).

 

you'll have to take them on at a time! (you will need like 4 stored procedures)

 

an the code for the stored proc that would return the earliest time for arrivalAM would be something like:

 

 

Create Proc EarlyArrivalAM
as
create table #TempTbl1 (
Date smalldatetime,
ArrivalAM smalldatetime,
DepartureAm smalldatetime,
ArrivalPm smalldatetime,
DeparturePm smalldatetime
)

create table #TempTbl2 (
Date smalldatetime,
ArrivalAM smalldatetime,
DepartureAm smalldatetime,
ArrivalPm smalldatetime,
DeparturePm smalldatetime
)
insert into #tempTbl1 (Date) select distinct Date from FlightPlan
insert into #tempTbl2  select * from FlightPlan order by ArrivalAm

update t1 set t1.ArrivalAM = t2.ArrivalAm,
		t1.DepartureAm = t2.DepartureAM,
		t1.ArrivalPm = t2.ArrivalPM,
		t1.DeparturePM = t2.DeparturePm
from #TempTbl1 t1, #TempTbl2 t2
where t1.Date = t2.Date

select * from #TempTbl1

 

this should return the earliest ArrivalAm time for each distinct date

 

try it and see if it returns what you need

 

if there is a simpler solution for this pls post it..sometimes i complicate things witout wanting it :)

Edited June 16, 2006 by Puiu

[tinJeff]

Gud Day...

Sorry I'm confusing everybody. The report I am making is a DTR. Anyway, I have a table in a database named "CheckIO". This table holds 2 columns, "TimeIO" and "CheckType". "TimeIO" has all the records for my columns in Crystal Reports, which are ArrivalAm, DepartureAm, ArrivalPm, DeparturePm. Now, I will be the one to segregate these records into 4 columns in CR XI. I made 4 formula fields to retrieve specific datetime from the "TimeIO" table. Since we're using a system to time-in and time-out everyday, some of the employees timed-in/time-out twice or more in a day. That is, there may be 2 time-in or 3 time-out. All I want is to display only 1 time-in and 1 time-out, and to suppress those other values that is under the same date.