SELECT S1.MISREP_NO, S1.MISREP_CRIT, S1.ROUTEPAPER_NO, S1.ROUTEPAPER_CRIT, S1.TRUCK1, S1.DRIVERNAME, S1.OCOUNT, S1.DAYS, S1.DIST, S1.BRB, S1.EUR, S1.EURCOURSE, S2.NORMBRB, S2.NORMEUR FROM (SELECT MR.MISREP_NO, MR.MISREP_CRIT, MR.VERSIONID, MR.ROUTEPAPER_NO, MR.ROUTEPAPER_CRIT, RP.TRUCK1, D.NAME DRIVERNAME, DEVEL.PKG_ROUTE.RouteOrdersCount(MR.ROUTEPAPER_NO, MR.ROUTEPAPER_CRIT) OCOUNT, DEVEL.PKG_ROUTE.DaysInWork(MR.ROUTEPAPER_NO, MR.ROUTEPAPER_CRIT) DAYS, DEVEL.GET_TRUCK_TOTALDISTANCE(TRUCK1, ATE1, ATE2) DIST, SUM(ROUND(DEVEL.ANYTOANY(MRD.CUR, :NEWCUR1, MRD.COST, MR.MISDATE),2)) BRB, SUM(ROUND(DEVEL.ANYTOANY(MRD.CUR, :NEWCUR2, MRD.COST, MR.MISDATE),2)) EUR, SUM(ROUND(DEVEL.ANYTOANY(:NEWCUR2, :NEWCUR1, 1, MR.MISDATE),2)) EURCOURSE FROM DEVEL.MISREP MR, DEVEL.ROUTEPAPER RP, DEVEL.DRIVERS D, DEVEL.MISREPDOCS MRD, DEVEL.MISREPCAT MRC WHERE MR.VERSIONID=1 AND RP.DATE2 BETWEEN ATE1 AND ATE2 AND MR.ROUTEPAPER_NO = RP.ROUTEPAPER_NO AND MR.ROUTEPAPER_CRIT = RP.ROUTEPAPER_CRIT AND MR.DRIVERID = RP.DRIVER1ID AND MR.DRIVERID = D.DRIVERID AND MR.MISREP_NO = MRD.MISREP_NO AND MR.MISREP_CRIT = MRD.MISREP_CRIT AND MR.VERSIONID = MRD.VERSIONID AND MRD.MISREPCATID = MRC.MISREPCATID AND MRC.NAME LIKE 'Ñóòî÷íûå%' AND MRD.COST > 0 GROUP BY MR.MISREP_NO, MR.MISREP_CRIT, MR.VERSIONID, MR.ROUTEPAPER_NO, MR.ROUTEPAPER_CRIT, RP.TRUCK1, D.NAME) S1 LEFT OUTER JOIN (SELECT MR.MISREP_NO, MR.MISREP_CRIT, MR.VERSIONID, MR.ROUTEPAPER_NO, MR.ROUTEPAPER_CRIT, RP.TRUCK1, D.NAME DRIVERNAME, SUM(ROUND(DEVEL.ANYTOANY(MRD.CUR, :NEWCUR1, MRD.COST, MR.MISDATE),2)) NORMBRB, SUM(ROUND(DEVEL.ANYTOANY(MRD.CUR, :NEWCUR2, MRD.COST, MR.MISDATE),2)) NORMEUR FROM DEVEL.MISREP MR, DEVEL.ROUTEPAPER RP, DEVEL.DRIVERS D, DEVEL.MISREPDOCS MRD, DEVEL.MISREPCAT MRC WHERE MR.VERSIONID=2 AND RP.DATE2 BETWEEN ATE1 AND ATE2 AND MR.ROUTEPAPER_NO = RP.ROUTEPAPER_NO AND MR.ROUTEPAPER_CRIT = RP.ROUTEPAPER_CRIT AND MR.DRIVERID = RP.DRIVER1ID AND MR.DRIVERID = D.DRIVERID AND MR.MISREP_NO = MRD.MISREP_NO AND MR.MISREP_CRIT = MRD.MISREP_CRIT AND MR.VERSIONID = MRD.VERSIONID AND MRD.MISREPCATID = MRC.MISREPCATID AND MRC.NAME LIKE 'Ñóòî÷íûå%' AND MRD.COST > 0 GROUP BY MR.MISREP_NO, MR.MISREP_CRIT, MR.VERSIONID, MR.ROUTEPAPER_NO, MR.ROUTEPAPER_CRIT, RP.TRUCK1, D.NAME) S2 ON ((S1.MISREP_NO=S2.MISREP_NO) AND (S1.MISREP_CRIT=S2.MISREP_CRIT) AND (S1.ROUTEPAPER_NO=S2.ROUTEPAPER_NO) AND (S1.ROUTEPAPER_CRIT=S2.ROUTEPAPER_CRIT) AND (S1.TRUCK1=S2.TRUCK1) AND (S1.DRIVERNAME=S2.DRIVERNAME)) |