如何實現,PROCEDURE DECLARE @DT_Start DATETIME,@DT_End DATETIME
查詢日期段@DT_Start到 @DT_End 求出同站別,同產品的 期初直,期末值?--table : isnull(qt_stk_b,0),isnull(qt_stk,0)
日期 站別 產品 期初 期末
dt_op,no_wc,no_item,qt_stk_b,qt_stk,
061101 wr01 sr01 100 10
061102 wr01 sr01 10 500
061103 wr01 sr01 500 0
061104 wr01 sr01 0 230
061105 wr01 sr01 230 120
.. .. .. .. ..
061130 wr01 sr01 100 200
061101 wr02 sr02 100 10
061102 wr02 sr02 10 500
061103 wr02 sr02 500 0
061104 wr02 sr02 0 230
061105 wr02 sr02 230 120
.. .. .. .. ..
061130 wr02 sr02 100 200
061201 wr02 sr02 200 20
-----實現結果----------------------
061101--061130 :wr01 sr01 100 200
wr02 sr02 100 20
-----------------------------------
--@DT_Start>=@DT_End
--1.0如果table 061130 無期末值 要往前找061130的值>0
--20.如果table 061101 無期初值 要往前找061130的值>0
查詢日期段@DT_Start到 @DT_End 求出同站別,同產品的 期初直,期末值?--table : isnull(qt_stk_b,0),isnull(qt_stk,0)
日期 站別 產品 期初 期末
dt_op,no_wc,no_item,qt_stk_b,qt_stk,
061101 wr01 sr01 100 10
061102 wr01 sr01 10 500
061103 wr01 sr01 500 0
061104 wr01 sr01 0 230
061105 wr01 sr01 230 120
.. .. .. .. ..
061130 wr01 sr01 100 200
061101 wr02 sr02 100 10
061102 wr02 sr02 10 500
061103 wr02 sr02 500 0
061104 wr02 sr02 0 230
061105 wr02 sr02 230 120
.. .. .. .. ..
061130 wr02 sr02 100 200
061201 wr02 sr02 200 20
-----實現結果----------------------
061101--061130 :wr01 sr01 100 200
wr02 sr02 100 20
-----------------------------------
--@DT_Start>=@DT_End
--1.0如果table 061130 無期末值 要往前找061130的值>0
--20.如果table 061101 無期初值 要往前找061130的值>0
insert into t
select '061101','wr01','sr01',100,10 union all
select '061102','wr01','sr01',100,10 union all
select '061130','wr01','sr01',100,200 union allselect '061101','wr02','sr02',50,10 union all
select '061103','wr02','sr02',100,10 union all
select '061130','wr02','sr02',100,220 union allselect '061101','wr03','sr03',0,10 union all
select '061102','wr03','sr03',30,10 union all
select '061103','wr03','sr03',100,70 union all
select '061130','wr03','sr03',100,0
declare @s datetime,@e datetime
set @s='061101'
set @e='061130'select convert(varchar(10),@s,120)+'到'+convert(varchar(10),@e,120) as [時間範圍],
no_wc,
no_item,
[qt_stk_b]=(select top 1 qt_stk_b from t a where no_wc=t.no_wc and no_item=t.no_item and qt_stk_b>0 and dt_op between @s and @e order by dt_op ),
[qt_stk]=(select top 1 qt_stk from t b where no_wc=t.no_wc and no_item=t.no_item and qt_stk>0 and dt_op between @s and @e order by dt_op desc )from t
where dt_op between @s and @e
group by no_wc,no_item
drop table t 時間範圍 no_wc no_item qt_stk_b qt_stk
---------------------- -------------------- -------------------- ----------- -----------
2006-11-01到2006-11-30 wr01 sr01 100 200
2006-11-01到2006-11-30 wr02 sr02 50 220
2006-11-01到2006-11-30 wr03 sr03 30 70
期出值=前天期末值--------------------------------------------------------------
什麼意思?? 偶需求搞不清@_@
dt_op qt_stk_b qt_stk
1101 100 20
1102 20 10
1103 10 0
1104 0 53
1105 53 120
----------------------查詢時賦值條件:@DT_Start>=@DT_End
在table中:期出值=前天期末值
修改:如果期初,期末 可以為>=0 也可以 說明庫存為=0
假如1130在table中期末沒有回報數據:要找到1130之前回報的qt_stk>=0的值
---請在幫我看吧。謝謝!!
playwarcraft(时间就像乳沟,挤挤还是有的) ( ) 信誉:100 Blog 2006-11-28 11:03:52 得分: 0 非常感謝你的幫忙,你的代碼可以實現我目前的要求。
thanks all
____________________________________________________