SQL Query help please

[firmusgrp]

Thanks for the help. Here is the logic. I need a View that will return the total available inventory. This inventory is the logical difference from tblRDLine and tblRSTLine. In addition there is a lookup to determine a multiplier that normalizes the two numbers. The parts look basically like:

tblRDLine - the quantity origin in inventory

dbo.tblRDLine.quantity

A lookup multiplier to put "quantity" in the same metric as the next table (which if it matters is pounds)

 ISNULL(CAST(dbo.tblRDLine.rdActLbs / dbo.tblRDLine.quantity AS decimal(9, 2)), ISNULL(CAST(dbo.tblTOLine.toActLbs / dbo.tblTOLine.quantity AS decimal(9, 2)), ISNULL(CAST(dbo.tblPOLine.poActLbs / dbo.tblPOLine.unitQty AS decimal(9, 2)), dbo.tblRawPack.convPounds)))

tblRSTLine - inventory that is gone

ISNULL(dbo.tblRSTLine.rstQty, 0)

There is a common KEY between tblRSTLine and tblRDLine of RDLineID.

The formula should be "(SUM(quantity) * lookup) - SUM(rstQty))" is what's left available for each RDLineID.

I'm close with the below but I believe it requires a subquery which is beyond what I know. Here is my larger code:

SELECT dbo.tblRDLine.quantity * ISNULL(CAST(dbo.tblRDLine.rdActLbs / dbo.tblRDLine.quantity AS decimal(9, 2)), ISNULL(CAST(dbo.tblTOLine.toActLbs / dbo.tblTOLine.quantity AS decimal(9, 2)), ISNULL(CAST(dbo.tblPOLine.poActLbs / dbo.tblPOLine.unitQty AS decimal(9, 2)), dbo.tblRawPack.convPounds))) - ISNULL(dbo.tblRSTLine.rstQty, 0) AS quantity, dbo.tblPOLine.transDesc, dbo.tblRawPack.packDesc, dbo.tblRawPack.convPounds, CAST(dbo.tblPOLine.poActLbs / dbo.tblPOLine.unitQty AS decimal(9, 4)) AS poActLbs, CAST(dbo.tblTOLine.toActLbs / dbo.tblTOLine.quantity AS decimal(9, 4)) AS toActLbs, CAST(dbo.tblRDLine.rdActLbs / dbo.tblRDLine.quantity AS decimal(9, 4)) AS rdActLbs, dbo.tblRDLine.RDLineID, dbo.tblRDLine.quantity AS Expr1, ISNULL(CAST(dbo.tblRDLine.rdActLbs / dbo.tblRDLine.quantity AS decimal(9, 2)), ISNULL(CAST(dbo.tblTOLine.toActLbs / dbo.tblTOLine.quantity AS decimal(9, 2)), ISNULL(CAST(dbo.tblPOLine.poActLbs / dbo.tblPOLine.unitQty AS decimal(9, 2)), dbo.tblRawPack.convPounds))) AS Expr2, ISNULL(dbo.tblRSTLine.rstQty, 0)AS Expr3
FROM dbo.tblRDLine INNER JOIN
dbo.tblTOLine ON dbo.tblRDLine.TOLineID = dbo.tblTOLine.TOLineID INNER JOIN dbo.tblPOLine ON dbo.tblTOLine.POLineID = dbo.tblPOLine.POLineID INNER JOIN dbo.tblRawPack ON dbo.tblPOLine.rawPackID = dbo.tblRawPack.rawPackID INNER JOIN dbo.tblVarietal ON dbo.tblPOLine.varietalID = dbo.tblVarietal.varietalID INNER JOIN dbo.tblOrigin ON dbo.tblVarietal.originID = dbo.tblOrigin.originID INNER JOIN dbo.tblGrade ON dbo.tblPOLine.gradeID = dbo.tblGrade.gradeID INNER JOIN dbo.tblGradeDensity ON dbo.tblPOLine.DensityID = dbo.tblGradeDensity.DensityID INNER JOIN dbo.tblGradeLength ON dbo.tblPOLine.LengthID = dbo.tblGradeLength.LengthID INNER JOIN dbo.tblGradeScreen ON dbo.tblPOLine.ScreenID = dbo.tblGradeScreen.ScreenID LEFT OUTER JOIN dbo.tblRSTLine ON dbo.tblRDLine.RDLineID = dbo.tblRSTLine.RDLineID
GROUP BY dbo.tblPOLine.transDesc, dbo.tblRawPack.packDesc, dbo.tblRawPack.convPounds, CAST(dbo.tblPOLine.poActLbs / dbo.tblPOLine.unitQty AS decimal(9, 4)), CAST(dbo.tblTOLine.toActLbs / dbo.tblTOLine.quantity AS decimal(9, 4)), CAST(dbo.tblRDLine.rdActLbs / dbo.tblRDLine.quantity AS decimal(9, 4)), dbo.tblRDLine.RDLineID, dbo.tblRDLine.quantity, ISNULL(CAST(dbo.tblRDLine.rdActLbs / dbo.tblRDLine.quantity AS decimal(9, 2)), ISNULL(CAST(dbo.tblTOLine.toActLbs / dbo.tblTOLine.quantity AS decimal(9, 2)), ISNULL(CAST(dbo.tblPOLine.poActLbs / dbo.tblPOLine.unitQty AS decimal(9, 2)), dbo.tblRawPack.convPounds))), ISNULL(dbo.tblRSTLine.rstQty, 0), dbo.tblRDLine.quantity * ISNULL(CAST(dbo.tblRDLine.rdActLbs / dbo.tblRDLine.quantity AS decimal(9,2)), ISNULL(CAST(dbo.tblTOLine.toActLbs / dbo.tblTOLine.quantity AS decimal(9, 2)), ISNULL(CAST(dbo.tblPOLine.poActLbs / dbo.tblPOLine.unitQty AS decimal(9, 2)), dbo.tblRawPack.convPounds))) - ISNULL(dbo.tblRSTLine.rstQty, 0)
HAVING      (dbo.tblRDLine.quantity * ISNULL(CAST(dbo.tblRDLine.rdActLbs / dbo.tblRDLine.quantity AS decimal(9, 2)), ISNULL(CAST(dbo.tblTOLine.toActLbs / dbo.tblTOLine.quantity AS decimal(9, 2)), ISNULL(CAST(dbo.tblPOLine.poActLbs / dbo.tblPOLine.unitQty AS decimal(9, 2)), dbo.tblRawPack.convPounds))) - ISNULL(dbo.tblRSTLine.rstQty, 0) > 0)

Help is greatly appreciated. If anyone is wondering all these CASTS are to overcome some issues with queries and EF4. I understand that in pure SQL they aren't required.

 

[firmusgrp]

Here's a bit more condensed code listing, removing some items for clarity

SELECT dbo.tblRDLine.quantity * ISNULL(CAST(dbo.tblRDLine.rdActLbs / dbo.tblRDLine.quantity AS decimal(9, 2)), ISNULL(CAST(dbo.tblTOLine.toActLbs / dbo.tblTOLine.quantity AS decimal(9, 2)), ISNULL(CAST(dbo.tblPOLine.poActLbs / dbo.tblPOLine.unitQty AS decimal(9, 2)), dbo.tblRawPack.convPounds))) - ISNULL(dbo.tblRSTLine.rstQty, 0) AS quantity, dbo.tblPOLine.transDesc, dbo.tblRawPack.packDesc, dbo.tblRawPack.convPounds, CAST(dbo.tblPOLine.poActLbs / dbo.tblPOLine.unitQty AS decimal(9, 4)) AS poActLbs, CAST(dbo.tblTOLine.toActLbs / dbo.tblTOLine.quantity AS decimal(9, 4)) AS toActLbs, CAST(dbo.tblRDLine.rdActLbs / dbo.tblRDLine.quantity AS decimal(9, 4)) AS rdActLbs, dbo.tblRDLine.RDLineID, dbo.tblRDLine.quantity AS Expr1, ISNULL(CAST(dbo.tblRDLine.rdActLbs / dbo.tblRDLine.quantity AS decimal(9, 2)), ISNULL(CAST(dbo.tblTOLine.toActLbs / dbo.tblTOLine.quantity AS decimal(9, 2)), ISNULL(CAST(dbo.tblPOLine.poActLbs / dbo.tblPOLine.unitQty AS decimal(9, 2)), dbo.tblRawPack.convPounds))) AS Expr2, ISNULL(dbo.tblRSTLine.rstQty, 0) AS Expr3
FROM dbo.tblRDLine INNER JOIN dbo.tblTOLine ON dbo.tblRDLine.TOLineID = dbo.tblTOLine.TOLineID INNER JOIN dbo.tblPOLine ON dbo.tblTOLine.POLineID = dbo.tblPOLine.POLineID INNER JOIN dbo.tblRawPack ON dbo.tblPOLine.rawPackID = dbo.tblRawPack.rawPackID LEFT OUTER JOIN dbo.tblRSTLine ON dbo.tblRDLine.RDLineID = dbo.tblRSTLine.RDLineID
GROUP BY dbo.tblPOLine.transDesc, dbo.tblRawPack.packDesc, dbo.tblRawPack.convPounds, CAST(dbo.tblPOLine.poActLbs / dbo.tblPOLine.unitQty AS decimal(9, 4)), CAST(dbo.tblTOLine.toActLbs / dbo.tblTOLine.quantity AS decimal(9, 4)), CAST(dbo.tblRDLine.rdActLbs / dbo.tblRDLine.quantity AS decimal(9, 4)), dbo.tblRDLine.RDLineID, dbo.tblRDLine.quantity, ISNULL(CAST(dbo.tblRDLine.rdActLbs / dbo.tblRDLine.quantity AS decimal(9, 2)), ISNULL(CAST(dbo.tblTOLine.toActLbs / dbo.tblTOLine.quantity AS decimal(9, 2)), ISNULL(CAST(dbo.tblPOLine.poActLbs / dbo.tblPOLine.unitQty AS decimal(9, 2)), dbo.tblRawPack.convPounds))), ISNULL(dbo.tblRSTLine.rstQty, 0), dbo.tblRDLine.quantity * ISNULL(CAST(dbo.tblRDLine.rdActLbs / dbo.tblRDLine.quantity AS decimal(9,2)), ISNULL(CAST(dbo.tblTOLine.toActLbs / dbo.tblTOLine.quantity AS decimal(9, 2)), ISNULL(CAST(dbo.tblPOLine.poActLbs / dbo.tblPOLine.unitQty AS decimal(9, 2)), dbo.tblRawPack.convPounds))) - ISNULL(dbo.tblRSTLine.rstQty, 0)
HAVING (dbo.tblRDLine.quantity * ISNULL(CAST(dbo.tblRDLine.rdActLbs / dbo.tblRDLine.quantity AS decimal(9, 2)), ISNULL(CAST(dbo.tblTOLine.toActLbs / dbo.tblTOLine.quantity AS decimal(9, 2)), ISNULL(CAST(dbo.tblPOLine.poActLbs / dbo.tblPOLine.unitQty AS decimal(9, 2)), dbo.tblRawPack.convPounds))) - ISNULL(dbo.tblRSTLine.rstQty, 0) > 0)

 

[Qik3Coder]

Um, that's gnarly.
If you're just after remaining quantity what is with that?

Table aliases are your friend.

Do you have the relevent table structures?

Lod

I'll answer your question, not solve your problem

 

[firmusgrp]

@Qik3Coder

Thanks for looking at it. It isn't quite as bad as it appears...much of it is all because of the lookup process. There are points along the lifecycle where an actual weight may be taken. If it is taken I have to use that value. This is what caused this nested if / then with ISNULL. There is a presidence to it based on when the weight may have been taken. If no weight was ever taken it defaults to an estimate multiplier "convPounds".

I'd be happy to suppy the structures of tblRDLine and tblRSTLine but that really isn't terribly helpful. They are large and would only complicate things. The key fields I've identified above.

Essentially

--- tblRDLine ---
RDLineID (PK)
qtyIN

--- tblRSTLine ---
RSTLineID (PK)
RDLineID (FK)
qtyOUT

Any example created with those examples will work. I can I beleive go back and implement the lookup. It is aggregating differnce of the two SUM's I'm having difficulty with.

 

[markros]

If you're using SQL 2005+, then split the query using Common table expressions. Also, use aliases for tables everywhere to improve readability

PluralSight Learning Library

 

[firmusgrp]

With Aliases

SELECT     
SUM(rd.quantity * ISNULL(CAST(rd.rdActLbs / rd.quantity AS decimal(9, 2)), ISNULL(CAST(t.toActLbs / t.quantity AS decimal(9, 2)), ISNULL(CAST(po.poActLbs / po.unitQty AS decimal(9, 2)), rp.convPounds))) - ISNULL(dbo.tblRSTLine.rstQty, 0)) AS quantity, rp.convPounds, CAST(po.poActLbs / po.unitQty AS decimal(9, 4)) AS poActLbs, CAST(t.toActLbs / t.quantity AS decimal(9, 4)) AS toActLbs, CAST(rd.rdActLbs / rd.quantity AS decimal(9, 4)) AS rdActLbs, rd.RDLineID
FROM dbo.tblRDLine AS rd INNER JOIN dbo.tblTOLine AS t ON rd.TOLineID = t.TOLineID INNER JOIN dbo.tblPOLine AS po ON t.POLineID = po.POLineID INNER JOIN dbo.tblRawPack AS rp ON po.rawPackID = rp.rawPackID LEFT OUTER JOIN dbo.tblRSTLine ON rd.RDLineID = dbo.tblRSTLine.RDLineID
GROUP BY rp.convPounds, CAST(po.poActLbs / po.unitQty AS decimal(9, 4)), CAST(t.toActLbs / t.quantity AS decimal(9, 4)), CAST(rd.rdActLbs / rd.quantity AS decimal(9, 4)), rd.RDLineID
HAVING      (SUM(rd.quantity * ISNULL(CAST(rd.rdActLbs / rd.quantity AS decimal(9, 2)), ISNULL(CAST(t.toActLbs / t.quantity AS decimal(9, 2)), ISNULL(CAST(po.poActLbs / po.unitQty AS decimal(9, 2)), rp.convPounds))) - ISNULL(dbo.tblRSTLine.rstQty, 0)) > 0)

 

[firmusgrp]

Thanks to makros for suggesting CTE.

This looks like it will do the job

WITH InvOut AS (SELECT     RDLineID, SUM(rstQty) AS qtyOut
                                     FROM         dbo.tblRSTLine AS rst
                                     GROUP BY RDLineID), InvIn AS
    (SELECT     rd.quantity * ISNULL(CAST(rd.rdActLbs / rd.quantity AS decimal(9, 2)), ISNULL(CAST(t.toActLbs / t.quantity AS decimal(9, 2)), 
                             ISNULL(CAST(po.poActLbs / po.unitQty AS decimal(9, 2)), rp.convPounds))) AS qtyIn, po.transDesc, rp.packDesc, rp.convPounds, o.originDesc AS origin, 
                             o.originDesc + ' (' + v.varietalName + ')' AS varietal, po.speciesID, po.isDecaf, rd.RDLedgerID, po.isFairTrade, po.isOrganic, po.isRainForest, po.isBirdFriendly, 
                             po.isCert, po.lotNumber, gd.DensityShort, gl.LengthName, gs.ScreenName, g.gradeName, CONVERT([varchar], DATEPART(year, po.cropYrStart), 0) 
                             + ' / ' + CONVERT([varchar], DATEPART(year, po.cropYrEnd), 0) AS cropYr, CAST(po.poActLbs / po.unitQty AS decimal(9, 4)) AS poActLbs, 
                             CAST(t.toActLbs / t.quantity AS decimal(9, 4)) AS toActLbs, CAST(rd.rdActLbs / rd.quantity AS decimal(9, 4)) AS rdActLbs, rd.RDLineID, po.isEuroPrep
      FROM          dbo.tblRDLine AS rd INNER JOIN
                             dbo.tblTOLine AS t ON rd.TOLineID = t.TOLineID INNER JOIN
                             dbo.tblPOLine AS po ON t.POLineID = po.POLineID INNER JOIN
                             dbo.tblRawPack AS rp ON po.rawPackID = rp.rawPackID INNER JOIN
                             dbo.tblVarietal AS v ON po.varietalID = v.varietalID INNER JOIN
                             dbo.tblOrigin AS o ON v.originID = o.originID INNER JOIN
                             dbo.tblGrade AS g ON po.gradeID = g.gradeID INNER JOIN
                             dbo.tblGradeDensity AS gd ON po.DensityID = gd.DensityID INNER JOIN
                             dbo.tblGradeLength AS gl ON po.LengthID = gl.LengthID INNER JOIN
                             dbo.tblGradeScreen AS gs ON po.ScreenID = gs.ScreenID LEFT OUTER JOIN
                             dbo.tblRSTLine AS rst ON rd.RDLineID = rst.RDLineID
      GROUP BY po.transDesc, rp.packDesc, rp.convPounds, o.originDesc, o.originDesc + ' (' + v.varietalName + ')', po.speciesID, po.isDecaf, rd.RDLedgerID, po.lotNumber, 
                             po.isOrganic, po.isRainForest, po.isBirdFriendly, po.isCert, po.isFairTrade, gd.DensityShort, gl.LengthName, gs.ScreenName, g.gradeName, CONVERT([varchar], 
                             DATEPART(year, po.cropYrStart), 0) + ' / ' + CONVERT([varchar], DATEPART(year, po.cropYrEnd), 0), CAST(po.poActLbs / po.unitQty AS decimal(9, 4)), 
                             CAST(t.toActLbs / t.quantity AS decimal(9, 4)), CAST(rd.rdActLbs / rd.quantity AS decimal(9, 4)), rd.RDLineID, po.isEuroPrep, 
                             rd.quantity * ISNULL(CAST(rd.rdActLbs / rd.quantity AS decimal(9, 2)), ISNULL(CAST(t.toActLbs / t.quantity AS decimal(9, 2)), 
                             ISNULL(CAST(po.poActLbs / po.unitQty AS decimal(9, 2)), rp.convPounds)))
      HAVING      (rd.quantity * ISNULL(CAST(rd.rdActLbs / rd.quantity AS decimal(9, 2)), ISNULL(CAST(t.toActLbs / t.quantity AS decimal(9, 2)), 
                             ISNULL(CAST(po.poActLbs / po.unitQty AS decimal(9, 2)), rp.convPounds))) > 0))
    SELECT     inb.qtyIn, out.qtyOut, inb.qtyIn - out.qtyOut AS qtyAvail
     FROM         InvIn AS inb INNER JOIN
                            InvOut AS out ON inb.RDLineID = out.RDLineID