Search results for query: *

... always a bit funny (read: sad) when you are reminded of the basics you've forgotten from lack of use. Thanks imex, I appreciate the refresher.

Hello all and thanks for the help. At the moment I have a SQL UNION within a CTE as follows (simplified for the thread) ... WITH InvTrans AS ( SELECT po.unitQty AS qtyIn, po.POLineID FROM dbo.tblPOLine AS po LEFT OUTER JOIN dbo.tblPOLedger AS poL ON po.POLedgerID =...

@JonFer Hi John, Thanks for having a look and I should clarify my remarks. When I say none existst, I meant with the same status (i.e. the WHERE condition)

Hi All, I was hoping to move our ASP.NET PasswordRecovery control functionality into a silverlight control that duplicates its process.Is there code behind or methods that can reproduce its functionality without too much heavy lifting? I was hoping to keep the user within the application...

ahh sorry, tyvm

Hi All, I was hoping to move our ASP.NET PasswordRecovery control functionality into a silverlight control that duplicates its process. Is there code behind or methods that can reproduce its functionality without too much heavy lifting? I was hoping to keep the user within the application...

Thanks for the tip, appreciated. Agreed on the oddity. Definitely correct database, I emptied all data to test the query when I noticed the strangeness. In fact it is a pretty complex one and I removed all the other fields so as to get to the root of the problem. I only have 2 result lines...

@markos Thanks again for your efforts. Specific to your questions, on the UNION no POLineID exists in both tables so so there is no SUM needed. Each quantity per po.POLineID is a total sum as is the same with tr.POLineID. That's what made the UNION idea so clean and neat. It seemed to...

Changing the CTE end Select to Distinct as ... SELECT DISTINCT ino.qtyIn - ISNULL(out.qtyOut, 0) as qtyAtSea FROM InvAtSea AS ino LEFT OUTER JOIN InvLanded AS out ON ino.POLineID = out.POLineID produces the correct results, I just am not sure why. Can anyone...

@markos Please ignore my last post, I misread yours and apparently we can't go back an edit.

@markros Thanks for your thoughts. If you'll see by the larger code I am using CTE and getting this odd result.

Hi all and thanks for the assistance ... I'm trying to (logically) combine to tables for a total inventory count, then subtract items from a third table. I UNION two tables as such: /* Inventory Purchased Afloat */ SELECT unitQty AS qtyIn, POLineID FROM dbo.tblPOLine AS po...

Thanks very much

Hi all, I have the following CTE query which I need to convert to a nested query. Any help in converting is appreciated. WITH InvAtA AS ( SELECT SUM(Lin.qty) AS qtyOut, Lin.POLineID, Led.locationID FROM dbo.tblTRLedger AS Led INNER JOIN dbo.tblTRLine AS Lin ON Led.TRLedgerID = Lin.TRLedgerID...

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...

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...

@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...

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))...

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...

Thargy, Thanks very much for your thoughts. I agree mostly with what you've said. In respect to the recursion, I couldn't agree more however the business model requires it...or at least my first efforts at dissuasion failed. Luckily the amount of blends is trivial in terms of the DB...