Suppress repeating dates
- Thread starter tinJeff
- Start date
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
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
Puiu thanks for the reply. But I didnt 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....
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
Danner
As far as I can tell, you havent given us enough info to help you. Your sample is:
What logic determines whats in Col2 for the date 1/1/2005? Do you want entry1 or entry2? Or, you dont care?
-ner
Code:
Col1 Col2
1/1/2005 "entry1"
1/1/2005 "entry2"
1/2/2005 "entry"What logic determines whats in Col2 for the date 1/1/2005? Do you want entry1 or entry2? Or, you dont care?
-ner
Arch4ngel
Well-known member
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?
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?
I thought of something, but i dont 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
youll 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:
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
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
youll 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:
Code:
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 #TempTbl1this 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
Last edited by a moderator:
Gud Day...
Sorry Im 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 were 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.
Sorry Im 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 were 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.