>create CURSOR mixed (open_qt integer,open_wt integer) >INSERT INTO mixed VALUES (20,30) > >create cursor arrival (date date,qty integer,weight integer) >INSERT INTO arrival VALUES (DATE(2016,7,15),11,3) >INSERT INTO arrival VALUES (DATE(2016,7,16),47,4) >INSERT INTO arrival VALUES (DATE(2016,7,17),300,5) >INSERT INTO arrival VALUES (DATE(2016,7,18),12345,2) >INSERT INTO arrival VALUES (DATE(2016,7,20),50,7) >INSERT INTO arrival VALUES (DATE(2016,7,21),15,4) >INSERT INTO arrival VALUES (DATE(2016,8,17),300,30) >INSERT INTO arrival VALUES (DATE(2016,8,20),50,5) > > >create cursor pouring (date date, qty integer,weight integer) >INSERT INTO pouring VALUES (DATE(2016,7,16),49,2) >INSERT INTO pouring VALUES (DATE(2016,7,17),500,3) >INSERT INTO pouring VALUES (DATE(2016,7,18),13,2) >INSERT INTO pouring VALUES (DATE(2016,7,20),70,4) >INSERT INTO pouring VALUES (DATE(2016,7,21),23,5) >INSERT INTO pouring VALUES (DATE(2016,7,22),23,3) >INSERT INTO pouring VALUES (DATE(2016,8,17),500,50) >INSERT INTO pouring VALUES (DATE(2016,8,20),70,7) > >SELECT INT(Ymonths.ymonth / 12) AS rYear, Ymonths.ymonth % 12 AS rMonth, ; > op.open_qt + (NVL(par.qty, 0) - NVL(ppo.qty, 0)) AS pQty, ; > op.open_wt + (NVL(par.weight, 0) - NVL(ppo.weight, 0)) AS pWeight, ; > NVL(ar.qty, 0) AS arrival_qty, NVL(ar.weight, 0) AS arrival_weight, ; > NVL(po.qty, 0) AS pouring_qty, NVL(po.weight, 0) AS pouring_weight, ; > op.open_qt + (NVL(par.qty, 0) + NVL(ar.qty,0) - NVL(ppo.qty, 0) - NVL(po.qty, 0)) AS rQty, ; > op.open_wt + (NVL(par.weight, 0) + NVL(ar.weight, 0) - NVL(ppo.weight, 0) - NVL(po.weight, 0)) AS rWeight ; > FROM (SELECT DISTINCT(YEAR(date) * 12 + MONTH(date)) AS ymonth FROM arrival ; > UNION ; > SELECT DISTINCT(YEAR(date) * 12 + MONTH(date)) AS ymonth FROM pouring) AS YMonths ; > INNER JOIN mixed op ON .T. ; > LEFT JOIN ; > (SELECT YEAR(date) * 12 + MONTH(date) AS YMonth, SUM(qty) AS qty, SUM(weight) AS weight FROM arrival GROUP BY ymonth) AS ar ; > ON ar.YMonth = YMonths.ymonth ; > LEFT JOIN ; > (SELECT YEAR(date) * 12 + MONTH(date) AS ymonth, SUM(qty) AS qty, SUM(weight) AS weight FROM pouring GROUP BY ymonth) AS po ; > ON po.YMonth = YMonths.ymonth ; > LEFT JOIN ; > (SELECT YEAR(date) * 12 + MONTH(date) AS ymonth, SUM(qty) AS qty, SUM(weight) AS weight FROM arrival GROUP BY ymonth) AS par ; > ON par.ymonth < YMonths.ymonth ; > LEFT JOIN ; > (SELECT YEAR(date) * 12 + MONTH(date) AS ymonth, SUM(qty) AS qty, SUM(weight) AS weight FROM pouring GROUP BY ymonth) AS ppo ; > ON ppo.ymonth < YMonths.ymonth ; > ORDER BY YMonths.ymonth ; > INTO CURSOR RunningReport > >BROWSE > >>
> > >SELECT Ymonths.ymonth/12 AS rYear, Ymonths.ymonth % 12 AS rMonth, > op.open_qt + (isnull(par.qty, 0) - isnull(ppo.qty, 0)) AS pQty, > op.open_wt + (isnull(par.weight, 0) - isnull(ppo.weight, 0)) AS pWeight, > isnull(ar.qty, 0) AS crprp_qty, isnull(ar.weight, 0) AS crprp_weight, > isnull(po.qty, 0) AS pouring_qty, isnull(po.weight, 0) AS pouring_weight, > op.open_qt + (isnull(par.qty, 0) + isnull(ar.qty,0) - isnull(ppo.qty, 0) - isnull(po.qty, 0)) AS rQty, > op.open_wt + (isnull(par.weight, 0) + isnull(ar.weight, 0) - isnull(ppo.weight, 0) -isnull(po.weight, 0)) AS rWeight > FROM (SELECT DISTINCT(YEAR(date) * 12 + MONTH(date)) AS ymonth FROM crprp > UNION > SELECT DISTINCT(YEAR(date) * 12 + MONTH(date)) AS ymonth FROM pouring) AS YMonths > INNER JOIN master op ON .T. > LEFT JOIN > (SELECT YEAR(date) * 12 + MONTH(date) AS YMonth, SUM(qty) AS qty, SUM(weight) AS weight FROM crprp GROUP BY ymonth) AS ar > ON ar.YMonth = YMonths.ymonth > LEFT JOIN > (SELECT YEAR(date) * 12 + MONTH(date) AS ymonth, SUM(qty) AS qty, SUM(weight) AS weight FROM pouring GROUP BY ymonth) AS po > ON po.YMonth = YMonths.ymonth > LEFT JOIN > (SELECT YEAR(date) * 12 + MONTH(date) AS ymonth, SUM(qty) AS qty, SUM(weight) AS weight FROM crprp GROUP BY ymonth) AS par > ON par.ymonth < YMonths.ymonth > LEFT JOIN > (SELECT YEAR(date) * 12 + MONTH(date) AS ymonth, SUM(qty) AS qty, SUM(weight) AS weight FROM pouring GROUP BY ymonth) AS ppo > ON ppo.ymonth < YMonths.ymonth > ORDER BY YMonths.ymonth > > >>
CROSS JOIN master op