ADP/SQL Data Selection Criteria

[patiya]

ADP/SQL Report Selection Criteria

I am using ADP front end and SQL on the back end. I want to run a report based on these 2 tables.

Table1 Data:

Client Id Date Employee ID Record Source

23376 1/9/2004 128 Table1
23376 1/8/2004 30 Table1
23379 1/6/2004 15 Table1
23377 1/6/2004 15 Table1
23380 12/31/2003 15 Table1
23377 12/31/2003 15 Table1


Table 2 Data:

Client Id Date Employee ID Record Source

23376 7/20/2004 30 Table2
23376 7/12/2004 30 Table2
23376 7/6/2004 30 Table2
23376 7/4/2004 30 Table2
23377 7/6/2004 30 Table2


Now I need to select the data from both Tables for the report based on this criteria:

Select Only one record (i.e. the same Client Id with the latest date). That is my report row source should look like this:

Final row source:

Client Id Date Employee ID Record Source

23376 7/20/2004 30 Table2
23377 7/6/2004 30 Table2
23379 1/6/2004 15 Table1
23380 12/31/2003 15 Table1


First I thought to create a Temporary File in a SP and append data from Table1 and Table2 but I don’t know how to populate the desired result in SP and return as a report row source in ADP.

But If I need to run everything in ADP, what will be my best approach? Create a Hard Coded Temporary file, append data from Table1 and Table2 and Populate the report result and run the report. But Only problem I think what will happen if 2 users run the same report at the same time since the report row source is a hard coded temporary file.

Any suggestion or help with coding will be appreciated. Thanks

 

[rlieving]

The solution is not difficult - although I did not fully understand your example.

Create a temporary table in SQL Server, dump the records you need into the table, then select the records from the temp table back to your report. Be sure to use 'SET NOCOUNT ON' in your stored procedure, otherwise you will get an empty recordset back.

 

[cmmrfrds]

This can be done with a query. I don't have time to code it now.

First UNION both tables.
Then Group by client id and get max(date)
This will be an inner join to an outer query that will also Union both tables with the Where clause using the Date = Max(date) from the inner query.

Look up "derived table" in sql server documentation. Also, union if you are not familiar.

 

[cmmrfrds]

Here is an example of what the query should look like. Just plug in your names. It looks kind of busy since it required 2 sub queries (derived tables).

SELECT x.vendor, x.datesold, x.id, x.item
FROM [Select D.vendor, D.datesold, D.id, D.item
From tblSales AS D
UNION ALL
Select D.vendor, D.datesold, D.id, D.item
From tblSales AS D]. AS X
INNER JOIN [Select vendor, max(datesold) as maxdatesold
FROM
(SELECT A.Vendor, A.DateSold
FROM tblSales AS A
UNION ALL
SELECT B.Vendor, B.DateSold
FROM tblSales AS B) AS C
Group by vendor]. AS inview ON inview.vendor = x.vendor
WHERE x.datesold = inview.maxdatesold
GROUP BY x.vendor, x.datesold, x.id, x.item;