Query (0x7f0d441395b0): UnfixedDiffPOsByDate AS ( SELECT po.POLedgerID, po.POLineID, po.transDesc, po.earlyFD, SUM(po.quantity) AS quantity, po.basePrice, po.diffPrice, po.lateFD, po.effPrice, po.isDecaf, po.speciesID, po.isFairTrade, po.isOrganic, po.isRainForest, po.isBirdFriendly, o.originDesc AS origin, v.varietalName AS varietal, rp.packDesc, rp.convPounds, g.gradeName, gl.LengthName, gd.DensityShort, gs.ScreenName, poL.orderDate FROM tblPOLedger AS poL INNER JOIN tblPOLine AS po ON poL.POLedgerID = po.POLedgerID LEFT OUTER JOIN tblGrade AS g ON po.gradeID = g.gradeID LEFT OUTER JOIN tblGradeScreen AS gs ON po.ScreenID = gs.ScreenID LEFT OUTER JOIN tblGradeLength AS gl ON po.LengthID = gl.LengthID LEFT OUTER JOIN tblGradeDensity AS gd ON po.DensityID = gd.DensityID LEFT OUTER JOIN tblRawPack AS rp ON po.rawPackID = rp.rawPackID LEFT OUTER JOIN tblOrigin AS o RIGHT OUTER JOIN tblVarietal AS v ON o.originID = v.originID ON po.varietalID = v.varietalID WHERE (po.diffPrice IS NULL) AND (po.basePrice IS NOT NULL) AND (poL.orderDate <= NAME_CONST('p_ENDDATE',_latin1'2017-04-26' COLLATE 'latin1_swedish_ci')) GROUP BY po.POLedgerID, po.POLineID, po.transDesc, po.basePrice, po.diffPrice, po.earlyFD, po.lateFD, po.effPrice, po.isDecaf, po.speciesID, po.isFairTrade, po.isOrganic, po.isRainForest, po.isBirdFriendly, o.originDesc, v.varietalName, rp.packDesc, rp.convPounds, g.gradeName, gl.LengthName, gd.DensityShort, gs.ScreenName, poL.orderDate ), FixedDiffPurchByDate AS ( SELECT pf.POLineID, SUM(pf.quantity) AS quantity, pfL.fixDate FROM tblPFLine AS pf INNER JOIN tblPFLedger AS pfL ON pf.PFLedgerID = pfL.PFLedgerID WHERE (pf.basePrice IS NULL) AND (pf.diffPrice IS NOT NULL) AND (pfL.fixDate <= NAME_CONST('p_ENDDATE',_latin1'2017-04-26' COLLATE 'latin1_swedish_ci')) GROUP BY pf.POLineID, pfL.fixDate ), UnfixedDiffPurchEndDate AS ( SELECT CONCAT('PO ', ino.POLedgerID) AS OrderID, 'Diff' AS FixType, ino.transDesc, ino.quantity - IFNULL(outa.quantity, 0) AS quantity, ino.packDesc, ino.basePrice, ino.diffPrice, ino.origin, ino.varietal, ino.earlyFD, ino.lateFD, DATEDIFF(ino.lateFD, NAME_CONST('p_ENDDATE',_latin1'2017-04-26' COLLATE 'latin1_swedish_ci')) AS DaysTillDue, ino.POLedgerID, ino.convPounds FROM UnfixedDiffPOsByDate AS ino LEFT OUTER JOIN FixedDiffPurchByDate AS outa ON ino.POLineID = outa.POLineID WHERE (ino.quantity - IFNULL(outa.quantity, 0) > 0) ), UnfixedDiffSOsByDate AS ( SELECT SUM(so.quantity) AS quantity, so.SOLineID, so.POLineID, so.basePrice, so.diffPrice, so.earlyFD, so.lateFD, so.effPrice, so.SOLedgerID, po.POLedgerID, po.transDesc, po.speciesID, po.isDecaf, po.isFairTrade, po.isOrganic, po.isRainForest, po.isBirdFriendly, o.originDesc AS origin, v.varietalName AS varietal, rp.packDesc, rp.convPounds, gd.DensityShort, g.gradeName, gl.LengthName, gs.ScreenName, poL.orderDate FROM tblGradeLength AS gl RIGHT OUTER JOIN tblPOLedger AS poL INNER JOIN tblPOLine AS po ON poL.POLedgerID = po.POLedgerID LEFT OUTER JOIN tblGradeDensity AS gd ON po.DensityID = gd.DensityID LEFT OUTER JOIN tblRawPack AS rp ON po.rawPackID = rp.rawPackID LEFT OUTER JOIN tblGrade AS g ON po.gradeID = g.gradeID LEFT OUTER JOIN tblVarietal AS v ON po.varietalID = v.varietalID LEFT OUTER JOIN tblOrigin AS o ON v.originID = o.originID LEFT OUTER JOIN tblGradeScreen AS gs ON po.ScreenID = gs.ScreenID ON gl.LengthID = po.LengthID RIGHT OUTER JOIN tblSOLine AS so ON po.POLineID = so.POLineID WHERE (so.basePrice IS NOT NULL) AND (so.diffPrice IS NULL) AND (poL.orderDate <= NAME_CONST('p_ENDDATE',_latin1'2017-04-26' COLLATE 'latin1_swedish_ci')) GROUP BY so.SOLineID, so.POLineID, so.basePrice, so.diffPrice, so.earlyFD, so.lateFD, so.effPrice, so.SOLedgerID, po.POLedgerID, po.transDesc, po.speciesID, po.isDecaf, po.isFairTrade, po.isOrganic, po.isRainForest, po.isBirdFriendly, o.originDesc, v.varietalName, rp.packDesc, rp.convPounds, gd.DensityShort, g.gradeName, gl.LengthName, gs.ScreenName, poL.orderDate ), FixedDiffSaleByDate AS ( SELECT SOLineID, SUM(quantity) AS quantity, pfL.fixDate FROM tblPFLine AS pf INNER JOIN tblPFLedger AS pfL ON pf.PFLedgerID = pfL.PFLedgerID WHERE (SOLineID IS NOT NULL) AND (basePrice IS NULL) AND (diffPrice IS NOT NULL) AND (pfL.fixDate <= NAME_CONST('p_ENDDATE',_latin1'2017-04-26' COLLATE 'latin1_swedish_ci')) GROUP BY SOLineID, pfL.fixDate ), UnfixedDiffSaleEndDate AS ( SELECT CONCAT('SO ',ino.SOLedgerID) AS OrderID, 'Diff' AS FixType, ino.transDesc, ino.quantity - IFNULL(outa.quantity, 0) AS quantity, ino.packDesc, ino.basePrice, ino.diffPrice, ino.origin, ino.varietal, ino.earlyFD, ino.lateFD, DATEDIFF(ino.lateFD, NAME_CONST('p_ENDDATE',_latin1'2017-04-26' COLLATE 'latin1_swedish_ci')) AS DaysTillDue, ino.POLedgerID, ino.convPounds FROM UnfixedDiffSOsByDate AS ino LEFT OUTER JOIN FixedDiffSaleByDate AS outa ON ino.SOLineID = outa.SOLineID WHERE (ino.quantity - IFNULL(outa.quantity, 0) > 0) ), UnfixedBasePOsByDate AS ( SELECT po.POLedgerID, po.POLineID, po.transDesc, SUM(po.quantity) AS quantity, po.basePrice, po.diffPrice, po.earlyFD, po.lateFD, po.effPrice, po.isDecaf, po.speciesID, po.isFairTrade, po.isOrganic, po.isRainForest, po.isBirdFriendly, o.originDesc AS origin, v.varietalName AS varietal, rp.packDesc, rp.convPounds, g.gradeName, gl.LengthName, gd.DensityShort, gs.ScreenName, poL.orderDate FROM tblPOLedger AS poL INNER JOIN tblPOLine AS po ON poL.POLedgerID = po.POLedgerID LEFT OUTER JOIN tblGrade AS g ON po.gradeID = g.gradeID LEFT OUTER JOIN tblGradeScreen AS gs ON po.ScreenID = gs.ScreenID LEFT OUTER JOIN tblGradeLength AS gl ON po.LengthID = gl.LengthID LEFT OUTER JOIN tblGradeDensity AS gd ON po.DensityID = gd.DensityID LEFT OUTER JOIN tblRawPack AS rp ON po.rawPackID = rp.rawPackID LEFT OUTER JOIN tblOrigin AS o RIGHT OUTER JOIN tblVarietal AS v ON o.originID = v.originID ON po.varietalID = v.varietalID WHERE (po.basePrice IS NULL) AND (po.diffPrice IS NOT NULL) AND (poL.orderDate <= NAME_CONST('p_ENDDATE',_latin1'2017-04-26' COLLATE 'latin1_swedish_ci')) GROUP BY po.POLedgerID, po.POLineID, po.transDesc, po.basePrice, po.diffPrice, po.earlyFD, po.lateFD, po.effPrice, po.isDecaf, po.speciesID, po.isFairTrade, po.isOrganic, po.isRainForest, po.isBirdFriendly, o.originDesc, v.varietalName, rp.packDesc, rp.convPounds, g.gradeName, gl.LengthName, gd.DensityShort, gs.ScreenName, poL.orderDate ), FixedBasePurchByDate AS ( SELECT pf.POLineID, SUM(pf.quantity) AS quantity, pfL.fixDate FROM tblPFLine AS pf INNER JOIN tblPFLedger AS pfL ON pf.PFLedgerID = pfL.PFLedgerID WHERE (pf.basePrice IS NOT NULL) AND (pf.diffPrice IS NULL) AND (pfL.fixDate <= NAME_CONST('p_ENDDATE',_latin1'2017-04-26' COLLATE 'latin1_swedish_ci')) GROUP BY pf.POLineID, pfL.fixDate ), UnfixedBasePurchEndDate AS ( SELECT CONCAT('PO ',ino.POLedgerID) AS OrderID, 'Base' AS FixType, ino.transDesc, ino.quantity - IFNULL(outa.quantity, 0) AS quantity, ino.packDesc, ino.basePrice, ino.diffPrice, ino.origin, ino.varietal, ino.earlyFD, ino.lateFD, DATEDIFF(ino.lateFD, NAME_CONST('p_ENDDATE',_latin1'2017-04-26' COLLATE 'latin1_swedish_ci')) AS DaysTillDue, ino.POLedgerID, ino.convPounds FROM UnfixedBasePOsByDate AS ino LEFT OUTER JOIN FixedBasePurchByDate AS outa ON ino.POLineID = outa.POLineID WHERE (ino.quantity - IFNULL(outa.quantity, 0) > 0) ), UnfixedBaseSOsByDate AS ( SELECT SUM(so.quantity) AS quantity, so.SOLineID, so.POLineID, so.basePrice, so.diffPrice, so.earlyFD, so.lateFD, so.effPrice, so.SOLedgerID, po.POLedgerID, po.transDesc, po.speciesID, po.isDecaf, po.isFairTrade, po.isOrganic, po.isRainForest, po.isBirdFriendly, o.originDesc AS origin, v.varietalName AS varietal, rp.packDesc, rp.convPounds, gd.DensityShort, g.gradeName, gl.LengthName, gs.ScreenName, poL.orderDate FROM tblGradeLength AS gl RIGHT OUTER JOIN tblPOLedger AS poL INNER JOIN tblPOLine AS po ON poL.POLedgerID = po.POLedgerID LEFT OUTER JOIN tblGradeDensity AS gd ON po.DensityID = gd.DensityID LEFT OUTER JOIN tblRawPack AS rp ON po.rawPackID = rp.rawPackID LEFT OUTER JOIN tblGrade AS g ON po.gradeID = g.gradeID LEFT OUTER JOIN tblVarietal AS v ON po.varietalID = v.varietalID LEFT OUTER JOIN tblOrigin AS o ON v.originID = o.originID LEFT OUTER JOIN tblGradeScreen AS gs ON po.ScreenID = gs.ScreenID ON gl.LengthID = po.LengthID RIGHT OUTER JOIN tblSOLine AS so ON po.POLineID = so.POLineID WHERE (so.basePrice IS NULL) AND (so.diffPrice IS NOT NULL) AND (poL.orderDate <= NAME_CONST('p_ENDDATE',_latin1'2017-04-26' COLLATE 'latin1_swedish_ci')) GROUP BY so.SOLineID, so.POLineID, so.basePrice, so.diffPrice, so.earlyFD, so.lateFD, so.effPrice, so.SOLedgerID, po.POLedgerID, po.transDesc, po.speciesID, po.isDecaf, po.isFairTrade, po.isOrganic, po.isRainForest, po.isBirdFriendly, o.originDesc, v.varietalName, rp.packDesc, rp.convPounds, gd.DensityShort, g.gradeName, gl.LengthName, gs.ScreenName, poL.orderDate ), FixedBaseSaleByDate AS ( SELECT pf.SOLineID, SUM(pf.quantity) AS quantity, pfL.fixDate FROM tblPFLine AS pf INNER JOIN tblPFLedger AS pfL ON pf.PFLedgerID = pfL.PFLedgerID WHERE (pf.SOLineID IS NOT NULL) AND (pf.basePrice IS NOT NULL) AND (pf.diffPrice IS NULL) AND (pfL.fixDate <= NAME_CONST('p_ENDDATE',_latin1'2017-04-26' COLLATE 'latin1_swedish_ci')) GROUP BY SOLineID, pfL.fixDate ), UnfixedBaseSaleEndDate AS ( SELECT CONCAT('SO ',ino.SOLedgerID) AS OrderID, 'Base' AS FixType, ino.transDesc, ino.quantity - IFNULL(outa.quantity, 0) AS quantity, ino.packDesc, ino.basePrice, ino.diffPrice, ino.origin, ino.varietal, ino.earlyFD, ino.lateFD, DATEDIFF(ino.lateFD, NAME_CONST('p_ENDDATE',_latin1'2017-04-26' COLLATE 'latin1_swedish_ci')) AS DaysTillDue, ino.POLedgerID, ino.convPounds FROM UnfixedBaseSOsByDate AS ino LEFT OUTER JOIN FixedBaseSaleByDate AS outa ON ino.SOLineID = outa.SOLineID WHERE (ino.quantity - IFNULL(outa.quantity, 0) > 0) ), UnfixedBaseAndDiffPOsByDate AS ( SELECT po.POLedgerID, po.POLineID, po.transDesc, po.earlyFD, SUM(po.quantity) AS quantity, po.basePrice, po.diffPrice, po.lateFD, po.effPrice, po.isDecaf, po.speciesID, po.isFairTrade, po.isOrganic, po.isRainForest, po.isBirdFriendly, o.originDesc AS origin, v.varietalName AS varietal, rp.packDesc, rp.convPounds, g.gradeName, gl.LengthName, gd.DensityShort, gs.ScreenName, poL.orderDate FROM tblPOLedger AS poL INNER JOIN tblPOLine AS po ON poL.POLedgerID = po.POLedgerID LEFT OUTER JOIN tblGradeScreen AS gs ON po.ScreenID = gs.ScreenID LEFT OUTER JOIN tblGradeLength AS gl ON po.LengthID = gl.LengthID LEFT OUTER JOIN tblGradeDensity AS gd ON po.DensityID = gd.DensityID LEFT OUTER JOIN tblGrade AS g ON po.gradeID = g.gradeID LEFT OUTER JOIN tblRawPack AS rp ON po.rawPackID = rp.rawPackID LEFT OUTER JOIN tblOrigin AS o RIGHT OUTER JOIN tblVarietal AS v ON o.originID = v.originID ON po.varietalID = v.varietalID WHERE (po.basePrice IS NULL) AND (po.diffPrice IS NULL) AND (poL.orderDate <= NAME_CONST('p_ENDDATE',_latin1'2017-04-26' COLLATE 'latin1_swedish_ci')) GROUP BY po.POLedgerID, po.POLineID, po.transDesc, po.basePrice, po.diffPrice, po.earlyFD, po.lateFD, po.effPrice, po.isDecaf, po.speciesID, po.isFairTrade, po.isOrganic, po.isRainForest, po.isBirdFriendly, o.originDesc, v.varietalName, rp.packDesc, rp.convPounds, g.gradeName, gl.LengthName, gd.DensityShort, gs.ScreenName, poL.orderDate ), FixedBaseAndDiffPurchByDate AS ( SELECT pf.POLineID, SUM(pf.quantity) AS quantity, pfL.fixDate FROM tblPFLine AS pf INNER JOIN tblPFLedger AS pfL ON pf.PFLedgerID = pfL.PFLedgerID WHERE (pf.basePrice IS NOT NULL) AND (pf.diffPrice IS NOT NULL) AND (pfL.fixDate <= NAME_CONST('p_ENDDATE',_latin1'2017-04-26' COLLATE 'latin1_swedish_ci')) GROUP BY pf.POLineID, pfL.fixDate ), UnfixedBaseAndDiffPurchEndDate AS ( SELECT CONCAT('PO ',ino.POLedgerID) AS OrderID, 'B & D' AS FixType, ino.transDesc, ino.quantity - IFNULL(outa.quantity, 0) AS quantity, ino.packDesc, ino.basePrice, ino.diffPrice, ino.origin, ino.varietal, ino.earlyFD, ino.lateFD, DATEDIFF(ino.lateFD, NAME_CONST('p_ENDDATE',_latin1'2017-04-26' COLLATE 'latin1_swedish_ci')) AS DaysTillDue, ino.POLedgerID, ino.convPounds FROM UnfixedBaseAndDiffPOsByDate AS ino LEFT OUTER JOIN FixedBaseAndDiffPurchByDate AS outa ON ino.POLineID = outa.POLineID WHERE (ino.quantity - IFNULL(outa.quantity, 0) > 0) ), UnfixedBaseAndDiffSOsByDate AS ( SELECT SUM(so.quantity) AS quantity, so.SOLineID, so.POLineID, so.basePrice, so.diffPrice, so.earlyFD, so.lateFD, so.effPrice, so.SOLedgerID, po.POLedgerID, po.transDesc, po.speciesID, po.isDecaf, po.isFairTrade, po.isOrganic, po.isBirdFriendly, po.isRainForest, o.originDesc AS origin, v.varietalName AS varietal, rp.packDesc, rp.convPounds, gd.DensityShort, g.gradeName, gl.LengthName, gs.ScreenName, poL.orderDate FROM tblGradeLength AS gl RIGHT OUTER JOIN tblPOLedger AS poL INNER JOIN tblPOLine AS po ON poL.POLedgerID = poL.POLedgerID LEFT OUTER JOIN tblGradeDensity AS gd ON po.DensityID = gd.DensityID LEFT OUTER JOIN tblRawPack AS rp ON po.rawPackID = rp.rawPackID LEFT OUTER JOIN tblGrade AS g ON po.gradeID = g.gradeID LEFT OUTER JOIN tblVarietal AS v ON po.varietalID = v.varietalID LEFT OUTER JOIN tblOrigin AS o ON v.originID = o.originID LEFT OUTER JOIN tblGradeScreen AS gs ON po.ScreenID = gs.ScreenID ON gl.LengthID = po.LengthID RIGHT OUTER JOIN tblSOLine AS so ON po.POLineID = so.POLineID WHERE (so.basePrice IS NULL) AND (so.diffPrice IS NULL) AND (poL.orderDate <= NAME_CONST('p_ENDDATE',_latin1'2017-04-26' COLLATE 'latin1_swedish_ci')) GROUP BY so.SOLineID, so.POLineID, so.basePrice, so.diffPrice, so.earlyFD, so.lateFD, so.effPrice, so.SOLedgerID, po.POLedgerID, po.transDesc, po.speciesID, po.isDecaf, po.isFairTrade, po.isOrganic, po.isRainForest, po.isBirdFriendly, o.originDesc, v.varietalName, rp.packDesc, rp.convPounds, gd.DensityShort, g.gradeName, gl.LengthName, gs.ScreenName, poL.orderDate ), FixedBaseAndDiffSaleByDate AS ( SELECT SOLineID, SUM(quantity) AS quantity, pfL.fixDate FROM tblPFLine AS pf INNER JOIN tblPFLedger AS pfL ON pf.PFLedgerID = pfL.PFLedgerID WHERE (SOLineID IS NOT NULL) AND (basePrice IS NOT NULL) AND (diffPrice IS NOT NULL) AND (pfL.fixDate <= NAME_CONST('p_ENDDATE',_latin1'2017-04-26' COLLATE 'latin1_swedish_ci')) GROUP BY SOLineID, pfL.fixDate ), UnfixedBaseAndDiffSaleEndDate AS ( SELECT CONCAT('SO ',ino.SOLedgerID) AS OrderID, 'B & D' AS FixType, ino.transDesc, ino.quantity - IFNULL(outa.quantity, 0) AS quantity, ino.packDesc, ino.basePrice, ino.diffPrice, ino.origin, ino.varietal, ino.earlyFD, ino.lateFD, DATEDIFF(ino.lateFD, NAME_CONST('p_ENDDATE',_latin1'2017-04-26' COLLATE 'latin1_swedish_ci')) AS DaysTillDue, ino.POLedgerID, ino.convPounds FROM UnfixedBaseAndDiffSOsByDate AS ino LEFT OUTER JOIN FixedBaseAndDiffSaleByDate AS outa ON ino.SOLineID = outa.SOLineID WHERE (ino.quantity - IFNULL(outa.quantity, 0) > 0) ) SELECT *, quantity * convPounds AS LbsToBeFixed FROM UnfixedDiffPurchEndDate UNION SELECT *, quantity * convPounds AS LbsToBeFixed FROM UnfixedDiffSaleEndDate UNION SELECT *, quantity * convPounds AS LbsToBeFixed FROM UnfixedBasePurchEndDate UNION SELECT *, quantity * convPounds AS LbsToBeFixed FROM UnfixedBaseSaleEndDate UNION SELECT *, quantity * convPounds AS LbsToBeFixed FROM UnfixedBaseAndDiffPurchEndDate UNION SELECT *, quantity * convPounds AS LbsToBeFixed FROM UnfixedBaseAndDiffSaleEndDate
|