Khant Maung Maung Khant Maung Maung - 11 months ago 44
SQL Question

Having difficulty in getting previous query record

I have 4 store at different levels. (Store A, Store B, Store C, Store D). The stores keep the items. (Item1, Item2, Item3, Item4 and Item5).

I have 3 tables (Item Table, Store Table,
Tracking Table) and 1 query (
Store_rec query for Item2 in Store B).

Tracking table has columns (ID, T_Date, Item_ID, Item_Name, Store_From, Store_To, Amount, Remark).

Store_rec query table is to check the changes of an Item in a Store (in my case - Item 2 in Store B). It has 5 columns (SrNo, T_Date, Location_To/From (From B to others/ From others to B), Received (B balance increase) and Dispatched (B balance decrease).

My problems are:

  1. I can't sort SrNo in ascending order without affecting the row number of T_Date.

  2. I want to add a balance column in Store_rec query as shown in the picture. (Balance = Previous balance + Received - Dispatched).

I searched in Google, read in forums, tutorials and examples but still can't solve my problem.

Please help.

FROM Tracking AS aa
t.[Store_From] = "Store B"
OR t.[Store_To] = "Store B"
AND aa.[ID] < t.[ID]
) AS SrNo
,IIf(t.[Store_To] = "Store B", t.[Store_From], t.[Store_To]) AS [Location_To/From]
,IIf(t.[Store_To] = "Store B", t.[Amount], 0) AS Received
,IIf(t.[Store_From] = "Store B", t.[Amount], 0) AS Dispatched
FROM Tracking AS t
t.[Store_From] = "Store B"
OR t.[Store_To] = "Store B"
AND t.[Item_Name] = "Item 2"
ORDER BY t.[T_Date];

Answer Source
select ,IIF(Store_From = 'Store B',Store_To, Store_From) ,IIF(Store_To = 'Store B',0, Amount) AS Dispatched, IIF(Store_From = 'Store B',0, Amount) AS Received,SUM(T.Received) AS RemainingBal
  From track  as T1

left join (

select  id,(-IIF(Store_To = 'Store B',0, Amount) + IIF(Store_From = 'Store B',0, Amount) ) AS Received
  From track 

) as  T ON <=

WHERE   Item_Id = 'Item_2'
        AND ( Store_From = 'Store B'
              OR Store_To = 'Store B'

        T1.Store_From ,
        ORDER BY