Filter out max value, but <= today

[ayla]

I have some problems to extract some data from a access database. The problem is that the table hold the entire history of all employees, and I need to extract only the valid value on all employees.

Ex:
Employee1, value1, FromDate: 16.12.2008
Employee1, value2, FromDate: 01.01.2009
Employee1, value3, FromDate: 20.05.2009
Employee1, value4, FromDate: 01.07.2009
Employee2...

If today date is 11.06.2009, then I need to filter out value3 on employee1, because value4 is not valid before july.

Can somebody help me?

 

[namliam]

Do this in 2 stages...
1) find the maximum date... using a group by query.
2) Use the query from 1, to fetch the data you need from your table.

Good luck !

P.S. Welcome to AWF

 

[ayla]

Can you give me a more detail description. Extracting these value is just a part of the report.
I have tryed to group, then I get the valid value, but when I then continue with the report and try to group on something else, I mess the result.

I tryed to run this sql statment:
UPDATE EmployeePersData AS EPD, EmpGrpHistory AS EGH, EmpCostHistory AS CC SET EGH.CostCenter = CC.Costcenter
WHERE CC.MANR = EPD.MANR AND
EPD.EmployeeNumber = EGH.EmployeeNumber AND
CC.Date = (SELECT MAX(CC2.DATE) FROM EmpCostHistory AS CC2 WHERE CC.MANR = CC2.MANR AND CC2.Date <= DateDiff('d',
#1/1/1990#,EGH.FromDate));

The result is correct on employees with no future value, but if an employee change value in future, the result is nothing.

Can I make a formula in 'select expert' in CR, that solve this problem without the sql statment?

 

[namliam]

Why update? This is a report right? Shouldnt it "show only" ??

Why compare the date to Datediff?? That doesnt make sense??

Is this SQL Server? Oracle? Other database??
SELECT MANR, MAX(CC2.DATE) MaxDate
FROM EmpCostHistory AS CC2
WHERE CC2.Date <= XYZ
Group by MANR

For SQL Server replace XYZ by GetUTCDate(), for Oracle replace by Sysdate.

Now use that query in a join back to your EmpCostHistory joining on both the MANR and MaxDate into your table. This should return your most recent value for you.

 

[ayla]

Main database run Oracle, but when you run a report it export 'most used' data to a personal access database. The access database is deleted everytime I run a new report.

If I need data from the main database I have to link up the table to access or run SQL statments.

On the main database the date is stored as days from 1/1/1990, but the exported data on access database are converted to a date format. Therefore I used Datediff

In the SQL statment i used; EmpCostHistory are linked in from main database.

The database support guy, wants me to not run any SQL statment, because it makes stress on server. So the best would be that I come up with a formaula in CR that solve this problem. But I need help for this

 

[namliam]

The database support guy, wants me to not run any SQL statment, because it makes stress on server.

This is just PLAIN NONSENSE! A proper SQL statement is no / near no stress on any semi serious Oracle box! Either way, I dont think you want to be resolving this in CR. The only (perhaps the best way) is to resolve this in a database. Either Oracle or Access, dont matter.

The difference is you either fetch 10.000 records or if all employees have 5 records on average, 2.000 records. Now a days CPU power is not /should not be your bottleneck. The I/O of harddrives is ussually the problem. Now ask your database support guy what he prefers? 10.000 records fetched or 2.000? Queries are MUCH prefered IMHO!

I can imagine that if each report fetches all of the Oracle database to the Access database that that will drain the Server much more on i/o and network traffic than is saved on CPU power not running the query in Oracle

Now back to the problem at hand...
No way to do this in CR only, you need to resolve this in some SQL. Either Oracle or Access...
XYZ in access would be Date()

You really need to add this (sub) query and run it, joining back into your EmpCostHistory table.

 

[ayla]

CC.date are stored as a string and the date conversion gives a result as a number. Could this be the problem?

 

[namliam]

CC.Date is a string ??? *BLEG* What format is this string??

Use oracle: To_Date(CC.Date , 'Format here')
to convert it to a date...

Otherwize in Access use Cdate if your date is formatted MM/DD/YYYY or YYYY/MM/DD
If it is formatted differently use DateSerial