DB2 Stored Procedure and Crystal Report
$20-25 USD
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
Project ID: #3527043