Re: ADP/SQL Data Selection Criteria in a Stored Procedure

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Gregory A. Larsen (greg.larsen_at_netzero.com)
Date: 08/31/04 

Date: Tue, 31 Aug 2004 09:26:05 -0700

Possibly you can use a UNION with a subquery to get the results. How about
something like this:

set nocount on
create table table1 (Client_Id int, DateEntered datetime, Employee_ID int)

insert into table1 values(23376,'1/9/2004',128)
insert into table1 values(23376,'1/8/2004',30)
insert into table1 values(23379,'1/6/2004',15)
insert into table1 values(23377,'1/6/2004',15)
insert into table1 values(23380,'12/31/2003',15)
insert into table1 values(23377,'12/31/2003',15)

create table table2 (Client_Id int, DateEntered datetime, Employee_ID int)

insert into table2 values(23376,'7/20/2004',30)
insert into table2 values(23376,'7/12/2004',30)
insert into table2 values(23376,'7/6/2004',30)
insert into table2 values(23376,'7/4/2004',30)
insert into table2 values(23377,'7/6/2004',30)
select * from
(
select client_id, dateentered, employee_id, 'table1' as record_source from
table1
union
select client_id, dateentered, employee_id, 'table2' as record_source from
table2
) a
where a.dateentered = (select top 1 dateentered from

             (
               select client_id, dateentered, employee_id, 'table1' as
record_source from table1
               union
               select client_id, dateentered, employee_id, 'table2' as
record_source from table2
              ) b
              where a.client_id = b.client_id
              order by dateentered desc)
and a.client_id = (select top 1 client_id from

             (
               select client_id, dateentered, employee_id, 'table1' as
record_source from table1
               union
               select client_id, dateentered, employee_id, 'table2' as
record_source from table2
              ) b
              where a.client_id = b.client_id
              order by dateentered desc)

drop table table1
drop table table2 

-- 
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"Ghulam" <a1besw@ccbq.org> wrote in message
news:6f78616e.0408310727.5d99cf62@posting.google.com...
> 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

Relevant Pages

  • Filtering and adding rows based on some condition
    ... DECLARE @TABLE1 TABLE ... (USGID INT NOT NULL,PCID INT NOT NULL) ... DECLARE @TABLE2 TABLE ... USGID PCID PCDESC Status ...
    (microsoft.public.sqlserver.programming)
  • Re: Text column copy across tables
    ... existing rows in TABLE1 using data present in the text column of a ... corresponding row in TABLE2. ... create table t(i int, t text) ... Don't forget to set textsize if datalength of the textfield ...
    (comp.databases.sybase)
  • Re: Tricky Visual Basic Code help...
    ... moves the current record into a new record in table2. ... this with a little VBA code and a couple custom queries. ... Set a "pushFlag" column in the record in table1 to a specific ... If a duplicate is not found, ...
    (microsoft.public.access.formscoding)
  • Re: Help needed with importing XML
    ... You could add a gratuitous identity column to #temp and use ... Those examples showed how to decompose arbitrary XML in multiple ... table1 and table2. ... But there has to be something in the relational schema tying table1 ...
    (microsoft.public.sqlserver.xml)
  • Re: 2 column pulldown
    ... Allen Browne - Microsoft MVP. ... these field heading names come from Table2 since it is a lookup and so ... Table1 of course only has a single column name ... fieldX from Table1 is pulldown revealing candidate values of Table2. ...
    (microsoft.public.access.forms)