Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. firmusgrp

    Moving from UNION to ... ?

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

    Moving from UNION to ... ?

    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 =...
  3. firmusgrp

    Sql View / Query help

    @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)
  4. firmusgrp

    ASP 4 / PasswordRecovery

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

    ASP 4 / PasswordRecovery

    ahh sorry, tyvm
  6. firmusgrp

    ASP 4 / PasswordRecovery

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

    Sql View / Query help

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

    Sql View / Query help

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

    Sql View / Query help

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

    Sql View / Query help

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

    Sql View / Query help

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

    Sql View / Query help

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

    Convert CTE query to nested

    Thanks very much
  14. firmusgrp

    Convert CTE query to nested

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

    SQL Query help please

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

    SQL Query help please

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

    SQL Query help please

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

    SQL Query help please

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

    SQL Query help please

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

    Recommend Table Structure

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

Part and Inventory Search

Back
Top