transdate       itembarcode       itemname      qty      
11/02/2011      0012400132        choco milk     20
12/05/2011      0245212213        choco milk     30

tbl_soldout

transdate       itembarcode       itemname      qty      
11/03/2011      0012400132        choco milk     5
12/05/2011      0245212213        choco milk     4

tbl_adjustment

transdate       itembarcode       itemname      qty     memo     
11/03/2011      0012400132        choco milk     2      "added"
12/05/2011      0245212213        choco milk     1      "deducted"

transdate      Memo      itembarcode   itemname   stock in  stockout    remaining_qty 
11/02/2011     purchase   0012400132   choco milk    20                       20
11/03/2011     sales      0012400132   choco milk              5              15
11/03/2011     adjst      0012400132   choco milk              2              17

If the user will filter until 12/05/2011, the result should be:

transdate      Memo      itembarcode   itemname   stock in  stockout    remaining_qty 
11/02/2011     purchase   0012400132   choco milk    20                       20
11/03/2011     sales      0012400132   choco milk              5              15
11/03/2011     adjst      0012400132   choco milk              2              17
12/05/2011     purchase   0012400132   choco milk    30                       47
12/05/2011     sales      0012400132   choco milk              4              43
12/05/2011     adjst      0012400132   choco milk              1              42

quote:

---

Originally posted by khtan

declare @transdate date = '2011-11-30'

;with stk_card as
(
	select	row_no = row_number() over (partition by itembarcode order by transdate, stock_in desc, stock_out),
		transdate, memo, itembarcode, itemname, stock_in, stock_out
	from	
	(
		select	transdate, memo = 'purchase', itembarcode, itemname, stock_in = qty, stock_out = NULL
		from	tbl_purchases
		where	transdate	<= @transdate
		union all
		select	transdate, memo = 'sales', itembarcode, itemname, stock_in = NULL, stock_out = qty
		from	tbl_soldout
		where	transdate	<= @transdate
		union all
		select	transdate, memo = 'adjst', itembarcode, itemname, 
			stock_in = case when memo = 'added' then qty end, 
			stock_out = case when memo = 'deducted' then qty end
		from	tbl_adjustment
		where	transdate	<= @transdate
	) t
)
select	*
from	stk_card s
	cross apply
	(
		select	remaining_qty = sum(isnull(stock_in, 0) - isnull(stock_out, 0))
		from	stk_card x
		where	x.itembarcode	= s.itembarcode
		and	x.row_no	<= s.row_no
	) r

---

KH
[spoiler]Time is always against us[/spoiler]

---

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[item_quickrpt]
	-- Add the parameters for the stored procedure here
	@itemcode as varchar(max),
	@fromdate as date,
	@todate as date
AS
BEGIN

;with stk_card as
(
	select	row_no = row_number() over (partition by prodcode order by podate, stock_in, stock_out),
		    podate, memo, porefno,vendorname,prodcode, itemname, stock_in, stock_out
	from	
	(
		select	podate, memo = 'purchased', porefno,vendorname= (select suppliername from tbl_supplier y
		                                                         where y.suppliercode=v.supplierid),prodcode, itemname, 
		                                                         stock_in = qty, stock_out = 0
		from	PO_detail v
		where	prodcode=@itemcode and (CONVERT(Date, podate) between @fromdate AND @todate)
		
		union all
		
		select	transdate, memo = 'sales', salesid,customername=(select [Last Name] from tbl_costumers l
		                                                         where l.[ID No.]=(select costumerid from tbl_pos_sales_summary c
		                                                         where c.salesid=b.salesid)),productcode, productdesc, 
		                                                         stock_in = 0, stock_out = qty
		from	tbl_pos_sales_detail b
		where	productcode=@itemcode and (CONVERT(Date, transdate) between @fromdate AND @todate)
		
		union all
		
		select	adjustmentdatetime, memo = 'adjustment', [index],userlogin, itemcode, itemname, 
			    stock_in =  case when memo = 'added'    then adjustmenttotal else 0 end, 
			    stock_out = case when memo = 'deducted' then adjustmenttotal else 0 end
		from	tbl_item_quantity_adjusments
		where	itemcode=@itemcode and (CONVERT(Date, adjustmentdatetime) between @fromdate AND @todate)
	) t
)
select	*
from	stk_card s
	cross apply
	(
		select	remaining_qty = sum(isnull(stock_in, 0) - isnull(stock_out, 0))
		from	stk_card x
		where	x.prodcode=@itemcode
	    and	x.row_no	<= s.row_no
	) r
END

quote:

---

Originally posted by Jeffreys

I would do something like this - using the combo of unions, derived tables (inline views) and case statements. Joins aren't really appropriate for this kind of query. Of course you'll have to be careful of how you handle the temporal comparisons.

disclaimer - I didn't debug or run - meant for example only.


DECLARE @transdate datetime
,@barcode VARCHAR(??)

SELECT transdate
,memo
,itembarcode
,itemname
,stockin
,stockout
,remaining_qty = ISNULL(stockin,0) - ISNULL(stockout,0)
FROM (
SELECT transdate
,memo
,itembarcode
,itemname
,CASE
WHEN memo in ('purchase','added') THEN qty
ELSE null
END AS stockin
,CASE
WHEN memo in 'sales','deducted' THEN qty
ELSE null
END AS stockout
,transtype
FROM (
SELECT transdate
,itembarcode
,itemname
,qty
,memo = 'purchase'
,transtype = 1
FROM tbl_Purchases
WHERE itembarcode = @barcode
AND transdate <= @transdate

UNION ALL

SELECT transdate
,itembarcode
,itemname
,qty
,memo = 'sales'
,transtype = 2
FROM tbl_Soldout
WHERE itembarcode = @barcode
AND transdate <= @transdate

UNION ALL

SELECT transdate
,itembarcode
,itemname
,qty
,memo
,transtype = 3
FROM tbl_adjustment
WHERE itembarcode = @barcode
AND transdate <= @transdate
)dt1
)dt2
ORDER BY transtype

---