การทำ stock card ให้มาแสดง ใน datagridview ต้องทำอย่างไรบ้าง ครับ

select '2003' as ProductID,'02/02/2009' as StockDate,614 as ProductIN,10 as ProductOUT 
into #temp
union
select '2003','03/02/2009',1000,248 
union
select '2003','10/02/2009',0, 6 
union
select '2003','11/02/2009',0,105 
union
select '2003','12/02/2009',0,302 
union
select '2003','13/02/2009',0,200 
union
select '2003','16/02/2009',0,200 
union
select '2003','23/02/2009',0,250 
union
select '2003','24/02/2009',243,0 
union
select '2003','26/02/2009',0,33 
union
select '2003','27/02/2009',6,0
-- แสดงผล
select a.*,
              (select sum(ProductIN) - sum(ProductOUT)
                from #temp
                where stockdate <= a.stockdate) as balance
from #temp a

private void search()
        {
            dataGridView1.DataSource = null;
            
            //Set Column                        
            DataGridViewTextBoxColumn c01 = new DataGridViewTextBoxColumn() { DataPropertyName = "add_date", HeaderText = "Add Date", Width = 200 };
            DataGridViewTextBoxColumn c02 = new DataGridViewTextBoxColumn() { DataPropertyName = "quantity", HeaderText = "จำนวน", Width = 200 };
            DataGridViewTextBoxColumn c03 = new DataGridViewTextBoxColumn() { DataPropertyName = "balance", HeaderText = "คงเหลือ", Width = 200 };
            DataGridViewTextBoxColumn c04 = new DataGridViewTextBoxColumn() { DataPropertyName = "company_name", HeaderText = "company_name", Width = 200 };
            DataGridViewTextBoxColumn c05 = new DataGridViewTextBoxColumn() { DataPropertyName = "remark", HeaderText = "remark", Width = 200 };

            // Add Column
            dataGridView1.Columns.AddRange(c01,c02,c03,c04,c05);

            // Set DataSource              
            dataGridView1.DataSource = GetTable(
   "select si.add_date,si.quantity,si.balance,sup.company_name,si.remark      from products p         join stock_incomings si on si.product_id=p.id        join receives re on re.id=si.receive_id     join suppliers sup on sup.id=re.supplier_id         where  p.id = '" + P_category.SelectedValue + "' and  si.add_date between CONVERT(varchar(50), '" + selectday1 + "')and CONVERT(varchar(50), '" + selectday2 + "')  union all  select so.add_date,so.quantity,so.balance,cus.company_name,so.remark      from products p           join stock_outgoings so on so.product_id=p.id     join disburse dis on dis.id=so.disburse_id    join customers cus on cus.id=dis.customer_id       where  p.id = '" + P_category.SelectedValue + "' and  so.add_date between CONVERT(varchar(50), '" + selectday1 + "')and CONVERT(varchar(50), '" + selectday2 + "')    ORDER BY  add_date ASC   "
             
);

            
            
            if (dataGridView1.Rows.Count == 0)
            {
                MessageBox.Show("No Data");
                dataGridView1.DataSource = null;
            }
        }

        private DataTable GetTable(String SQL)
        {
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            SqlDataAdapter sda = new SqlDataAdapter(SQL, conn);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            return dt;
        }

select si.add_date,si.quantity,so.quantity,si.balance ......

IF OBJECT_ID('tempdb..#temp_in') IS NOT NULL
    DROP TABLE #temp_in
    
IF OBJECT_ID('tempdb..#temp_out') IS NOT NULL
    DROP TABLE #temp_out   
    
select '2003' as ProductID,'02/02/2009' as StockDate,614 as ProductIN,'ST Balance' as Ref_Num
into #temp_in
union
select '2003','03/02/2009',100,'PO01-001'
union
select '2003','24/02/2009',24,'PO01-002'
union
select '2003','26/02/2009',33 ,'PO01-003'
union
select '2003','27/02/2009',40,'PO01-004'


select '2003' as ProductID,'02/02/2009' as StockDate,10 as ProductOUT,'I01-0001' as Ref_Num
into #temp_out
union
select '2003','03/02/2009',10,'I01-0002'
union
select '2003','03/02/2009',23,'I01-0003'
union
select '2003','26/02/2009',4 ,'I01-0004'
union
select '2003','27/02/2009',40,'I01-0005';

with View_CTE as ( 
select ProductID,StockDate,ProductIN,0as ProductOUT,Ref_Num,'A' as Stype from #temp_in
union
select ProductID,StockDate,0 as ProductIN,ProductOUT,Ref_Num,'B' as Stype from #temp_out
) 
,StockCard_CTE as (
select a.*,ROW_NUMBER() OVER (ORDER BY Stockdate ,Stype) AS RowNumber
from View_CTE a )

select a.*,(select  sum(ProductIN) - sum(ProductOUT)
                from StockCard_CTE
                where RowNumber <= a.RowNumber ) as balance
                
from StockCard_CTE a


DROP TABLE #temp_in;
DROP TABLE #temp_out;

select si.add_date,si.quantity,so.quantity,xyz......

with View_CTE  as (
	select si.product_id, si.add_date,si.quantity as qty_receives,0 as qty_outgoings
	,sup.company_name,si.remark ,'I' as Stype  
	from stock_incomings si
	left join receives re on re.id=si.receive_id 
	left join suppliers sup on sup.id=re.supplier_id 
	union
	select so.product_id,so.add_date ,0 as qty_receives,so.quantity as qty_outgoings
	,'' as company_name,'' as remark  ,'O' as Stype 	  
	from stock_outgoings so	
),StockCard_CTE as (
select a.*,ROW_NUMBER() OVER (ORDER BY  product_id,add_date ,Stype) AS RowNumber
from View_CTE a )

/*ตรงการจะ join อะไรมาทำตรงนี้ แต่ถ้ามากว่านี้ให้ไปเพิ่มที่ View_CTE ก่อน
select a.*,(select  sum(qty_receives) - sum(qty_outgoings)
                from StockCard_CTE
                where RowNumber <= a.RowNumber ) as balance
                
from StockCard_CTE a

with View_CTE  as (
	select si.product_id, si.add_date,si.quantity as qty_receives,0 as qty_outgoings
	,sup.company_name,si.remark ,'I' as Stype
	  
	from stock_incomings si
	left join receives re on re.id=si.receive_id 
	left join suppliers sup on sup.id=re.supplier_id 
	union
	select so.product_id,so.add_date,0 as qty_receives,so.quantity as qty_outgoings
	,cus.company_name,so.remark  ,'O' as Stype 
		  
	from stock_outgoings so	
	left join disburse dis on dis.id=so.disburse_id
	left join customers cus on cus.id=dis.customer_id
),StockCard_CTE as (
select a.*,ROW_NUMBER() OVER (ORDER BY  product_id,add_date ,Stype) AS RowNumber
from View_CTE a )

/*ตรงการจะ join อะไรมาทำตรงนี้ แต่ถ้ามากว่านี้ให้ไปเพิ่มที่ View_CTE ก่อน*/
select a.*,(select  sum(qty_receives) - sum(qty_outgoings)
                from StockCard_CTE
                where RowNumber <= a.RowNumber ) as balance
                
from StockCard_CTE a