CODEWITHSUNDEEP
sql-serversql-server-2008subquery
noone noone
noone noone

Reputation: 3

How to place subquery in SQL Server 2008

I have a main SQL query which returns stock quantity in dates between and I also need to return the stock as on date along with between dates result.

Main query:

SELECT 
    TT.TranRId, MAX(TT.TInQty) AS InQty, MAX(TT.TOutQty) AS OutQty
FROM
    TReg TR, TTrans TT
WHERE 
    TR.TRegId = TT.TrRegId
    AND TT.Stid = 2
    AND TR.TransDate BETWEEN '2018-08-25' AND '2018-08-28'
GROUP BY 
    TT.TranRId 
ORDER BY 
    TT.TranRId

Sub query:

(SELECT TT.TransRId, (SUM(TT.TInQty) - SUM(TT.TOutQty)) 
FROM TTrans TT, TReg TR
WHERE TR.TransDate <= '2018-08-24' 
  AND TR.TRegId = TT.TrRegId
  AND TT.Stid = 2 GROUP BY TT.TranRId) --AS Stock

Please help where I should include my sub query in my main query

To get output as follows:

TransRId    Stock   InQty   OutQty
----------------------------------
41          700     1       1000
42          800     5       500

Upvotes: 0

Views: 57

Answers (1)

A.Rennick
A.Rennick

Reputation: 48

I am not sure I am following your question 100%, if you are just looking to join it as a sub query the below logic should work.

SELECT TT.TranRId
    ,MAX(TT.TInQty) AS InQty
    ,MAX(TT.TOutQty) AS OutQty
    ,Stock.[Sum]
FROM TReg TR
LEFT JOIN TTrans TT
    ON TR.TRegId = TT.TrRegId
LEFT JOIN (
    SELECT TT.TransRId
        ,(SUM(TT.TInQty) - SUM(TT.TOutQty)) as Sum
    FROM TTrans TT
    LEFT JOIN TReg TR
        ON TR.TRegId = TT.TrRegId
    WHERE TR.TransDate <= '2018-08-24'
        AND TT.Stid = 2
    GROUP BY TT.TranRId
    ) AS Stock
    ON Stock.TranRId = TT.TranRId
WHERE TT.Stid = 2
    AND TR.TransDate BETWEEN '2018-08-25' AND '2018-08-28'
GROUP BY TT.TranRId
ORDER BY TT.TranRId

edit: I noticed tt.TranRId and tt.Tran***s***RId if this is not a typo it would need to be corrected, if not my answer will not work for you.

If you need specific dates, including the date in the join logic along with the ID would give you the appropriate result...without knowing your data set I am not sure if the TranRId is unique...sorry about that!

Upvotes: 1

Related Questions