Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 892
  • Last Modified:

Crystal Report - How to pull current date and most recent time?

I have a crystal report that pulls the correct records, but it has duplicates coming from this field:
{mwlAppraiserNote.UpdatedonDate}     which is in my select expert. The problem is the field could be updated on 4/24/2009 at 8:00am, 9:30am, and 2:00pm which would be three times for one record. How can I pull this field on CurrentDate and most recent time which would be the 2:00pm?

Thanks. My select expert code is below.


{mwlAppraiserNote.UpdatedonDate} = CurrentDate and 
{mwlLoanData.FinancingType} <>"F" and
{mwlLoanApp.MWBranchCode} <> "TEST"

Open in new window

0
asmyatt
Asked:
asmyatt
  • 2
1 Solution
 
GSQLCommented:
I would do this in the query.  If sql server and/or if you are using a sql command to get the data, you can try something like:

select ...
from mwlAppraiserNote t
where ..
and t.UpdatedonDate = (
  select max(tInner.UpdatedonDate)
  from mwlAppraiserNote tInnner
  where t.{max datetime per whatever} = tInner.{the same per whatever column}
  )

If you're not using SQL Server you may need to take a different approach...
0
 
asmyattAuthor Commented:
I tried  your code and I'm getting a syntax error on the last line:

where t.{max 2009-04-27} = tInner.{2009-04-27}
)

What am I missing?
0
 
GSQLCommented:
First off, I did not give a very good example, I was just trying to give you an idea.  As for the error; for one thing you need the column name, not a value.  

I still think you want to eliminate the duplicates in SQL.  The last possibility I give here is the one I like the best.

Try the code I included here instead.  In your cod you will have to include the other columns you are grouping by.
-- just some sample data
declare @dtbl table (UpdatedonDate datetime)
insert @dtbl (UpdatedonDate) values (dateadd(hh, 1, dateadd(dd, -1, DATEADD(DAY, 0, DATEDIFF(DAY, '', getdate())))))
insert @dtbl (UpdatedonDate) values (dateadd(hh, 7, dateadd(dd, -1, DATEADD(DAY, 0, DATEDIFF(DAY, '', getdate())))))
insert @dtbl (UpdatedonDate) values (dateadd(hh, 14, dateadd(dd, -1, DATEADD(DAY, 0, DATEDIFF(DAY, '', getdate())))))
 
insert @dtbl (UpdatedonDate) values (dateadd(hh, 1, dateadd(dd, -3, DATEADD(DAY, 0, DATEDIFF(DAY, '', getdate())))))
insert @dtbl (UpdatedonDate) values (dateadd(hh, 7, dateadd(dd, -3, DATEADD(DAY, 0, DATEDIFF(DAY, '', getdate())))))
insert @dtbl (UpdatedonDate) values (dateadd(hh, 14, dateadd(dd, -3, DATEADD(DAY, 0, DATEDIFF(DAY, '', getdate())))))
 
insert @dtbl (UpdatedonDate) values (dateadd(hh, 7, dateadd(dd, -4, DATEADD(DAY, 0, DATEDIFF(DAY, '', getdate())))))
insert @dtbl (UpdatedonDate) values (dateadd(hh, 14, dateadd(dd, -4, DATEADD(DAY, 0, DATEDIFF(DAY, '', getdate())))))
 
insert @dtbl (UpdatedonDate) values (dateadd(hh, 7, dateadd(dd, -6, DATEADD(DAY, 0, DATEDIFF(DAY, '', getdate())))))
 
insert @dtbl (UpdatedonDate) values (dateadd(hh, 2, dateadd(dd, -7, DATEADD(DAY, 0, DATEDIFF(DAY, '', getdate())))))
insert @dtbl (UpdatedonDate) values (dateadd(hh, 8, dateadd(dd, -7, DATEADD(DAY, 0, DATEDIFF(DAY, '', getdate())))))
insert @dtbl (UpdatedonDate) values (dateadd(hh, 16, dateadd(dd, -7, DATEADD(DAY, 0, DATEDIFF(DAY, '', getdate())))))
 
-- see what the data looks like first
select * from @dtbl order by UpdatedonDate
 
-- this is the good part 
select DATEADD(DAY, 0, DATEDIFF(DAY, '', UpdatedonDate)) as DateOnly 
	, max(UpdatedonDate) as MaxDateTime
from @dtbl
group by DATEADD(DAY, 0, DATEDIFF(DAY, '', UpdatedonDate))

Open in new window

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now