DB2 Stored Procedure and Crystal Report

Cancelled Posted Dec 3, 2007 Paid on delivery
Cancelled Paid on delivery

Hello -

Need to create the SQL stored procedure & the Crystal report to compare the data in the DB query with the data in the CONT_DETAIL_BS_REC_DETAIL.

Report:

Sub-totaling and grand totaling at the following levels:

Rate

Assoc

Employer NUMBER / NAME (in addition to individual levels, highlight the non-matched in blue)

Right now this query produces one to many results:

------

query:

------

select CONTRACTORS_KEY,EMPLOYER,rate as bs_rate,db_rate,[url removed, login to view] as bs_ass,app, [url removed, login to view],[url removed, login to view],case

when REPT_QTY <> DB_REPORTED_QTY then cast ( 'Basys and DB cash is out of sync:' as varchar(60))end

as QTY_Mismach,case when CONTRACTORS_KEY is null then cast ('On Basys & not on DB2:' as varchar(60)) end ||

EMPLOYER as Bs_employer ,[url removed, login to view], EMPLOYER, CONTRACTOR_NAME, PERIOD, REPT_QTY, REF_NUMBER FROM CONT_DETAIL_BS_REC_detail BS

left outer join (SELECT mast.cont_detail_key,NAME,case when ASSOCIATION is null then 'B' else ASSOCIATION end ASSOCIATION

,CONTRACTORS_KEY,REPORTED_QTY AS DB_REPORTED_QTY,FUND,RATE

as db_rate,app as db_app,RECEIVED_DATE,work_period FROM contractors inner join CONT_DETAIL mast

on CONTRACTORS_KEY=employer_number

inner join CONT_DETAIL_FUND f on mast.CONT_DETAIL_KEY=f.CONT_DETAIL_KEY Where ((GROUPS ='BA' and deposit_type ='ME') or (GROUPS <>'BA'

and deposit_type in ('CA' ,'CAI','CC','CS','BW' )))

and RECEIVED_DATE >= CURRENT DATE - 7

days and) db

On CONTRACTORS_KEY =EMPLOYER

and PERIOD=work_period

AND DB_REPORTED_QTY=REPT_QTY

and cont_detail_key=REF_NUMBER

and bs.fund=[url removed, login to view]

and app=db_app and rate=db_rate

and bs.ASSOCIATION=[url removed, login to view] ;

Please see the additional files with data samples & DDL.

thx.

## Deliverables

1) Complete and fully-functional working program(s) in executable form as well as complete source code of all work done.

2) Deliverables must be in ready-to-run condition, as follows (depending on the nature of the deliverables):

a) For web sites or other server-side deliverables intended to only ever exist in one place in the Buyer's environment--Deliverables must be installed by the Seller in ready-to-run condition in the Buyer's environment.

b) For all others including desktop software or software the buyer intends to distribute: A software installation package that will install the software in ready-to-run condition on the platform(s) specified in this bid request.

3) All deliverables will be considered "work made for hire" under U.S. Copyright law. Buyer will receive exclusive and complete copyrights to all work purchased. (No GPL, GNU, 3rd party components, etc. unless all copyright ramifications are explained AND AGREED TO by the buyer on the site per the coder's Seller Legal Agreement).

## Platform

UDB DB2 v9.1 on unix

Crystal Report 9.2

Database Administration Engineering Linux MySQL Odd Jobs PHP Software Architecture Software Testing SQL UNIX

Project ID: #3527043

About the project

Remote project Active Dec 3, 2007