I am trying to write a query on a trades table where there are buy and sell transactions for various items. Trying to build a query where on a given date I wish to know how many of each item do I hold. That part is achieved.
Now the issue is, the query I have written returns a list of items which were once bought, and sold already, so on a given date that item shows 0 as balance. I want to avoid all items with zero balance as of that particular date.
My query looks like this.
Select
I.itemName, I.iID,
sum(case when Tr.Buy=1 then Tr.qty when Tr.Buy=0 then Tr.qty*-1 else 0 end) as TotalQty
from
Trades Tr, ItemMast I
where
Tr.iID = I.iID
and I.iCode = '1253'
and Tr.tDate <= '5/13/2015'
group by
I.itemName, I.iID
order by
I.itemName, I.iID
I tried to modify the where clause adding Where TotalQty > 0
, but it does not work.
Not sure how to go about with this. I am trying to build this on SQL Server CE.
Appreciate your help.
Thanks
Try use full expression in having
clause as below
Select I.itemName, I.iID, sum(case when Tr.Buy=1 then Tr.qty when Tr.Buy=0 then Tr.qty*-1 else 0 end) as TotalQty
from Trades Tr,ItemMast I
where Tr.iID = I.iID
and I.iCode = '1253'
and Tr.tDate<= '5/13/2015'
group by I.itemName, I.iID
having sum(case when Tr.Buy=1 then Tr.qty when Tr.Buy=0 then Tr.qty*-1 else 0 end) > 0
order by I.itemName, I.iID