重新开贴,SQL查询统计的表格,1号到31号的进销转存统计表

实际上这是个3维表,  
纵是商品代码,横是1-31日期,还有一个是业务类型,进,销,转出,转入,更多一点"进"前还有上存和  
"转入"后面的结存,所以比较复杂.  
基础数据表:  
商品表dm_wz  字段有dm,    ,代表商品代码  
仓库表dm_ck  字段有dm,    ,代表仓库代码  
进货表ins1  字段有dm,in_sl,in_date,ckdm      ,代表商品代码,进货数量,进货日期,仓库代码  
销售表out      字段有dm,o_sl,o_date,ckdm      ,代表商品代码,出货数量,出货日期,仓库代码  
转仓表zc      字段有dm,sl1,sl2,o_date,ckdm1,ckdm2      ,代表商品代码,转仓(sl1出,sl2入)数量,转仓日期,仓库代码(ckdm1转出仓,ckdm2转入仓)  
库存表cun  字段有dm,c_sl,ckdm                          ,代表商品代码,库存数量,仓库代码  
 
为了简便显示,其他的不统计字段就不写出来,比如单价等,只要上述字段,  
 
想得到以下效果,即是所有商品一个月的每天进销存的数量情况  
商品代码    类型      1号       2号       3  号              ...            31  号  
001         上存       0        10       7                                      8    
            进        10         0       0                                     100  
            销        0          3       0                                     100
            转入      0          0       0                                      0      
            转出      0          0       0                                      0  
            结存      10         7       7                                      8    
002    
...  
 
上述的进,销,转,库中都有对应的记录,  
比如销售表out  
out        
商品代码      出货数量      出货日期            仓库代码    
001                          3          2005-11-01          0001  
--以下是测试数据  
select  top  10  dm  from  dm_wz  
110100000000                                      
110100000001                                      
110100004101                                      
110100004102                                      
110100004103                                      
110100004104                                      
110100004105                                      
110100004201                                      
110100004202                                      
110100004203                                      
 
select  top  10  dm  from  dm_ck  
0001      
0002      
0003      
0004      
0005      
002        
003        
006        
101        
102        
select  top  10  wzdm,in_sl,in_date,ckdm  from  ins1  
110100004102                                                1            2005-11-17  00:00:00.000            0001      
110100004103                                                1            2005-11-17  00:00:00.000            0001      
110100004104                                                1            2005-11-17  00:00:00.000            0001      
110100004105                                                1            2005-11-17  00:00:00.000            0001      
110100004201                                                1            2005-11-17  00:00:00.000            0001      
110100004202                                                1            2005-11-17  00:00:00.000            0001      
110100000000                                                8            2005-11-18  00:00:00.000            0001      
110100000000                                                50            2005-11-23  00:00:00.000            0001      
110100004101                                                30            2005-11-23  00:00:00.000            0001      
110100004102                                                30            2005-11-23  00:00:00.000            0001      
 
select  top  10  wzdm,o_sl,o_date,ckdm  from  out  
110100000000                                                5            2004-11-21  17:07:05.000            0001      
110100000000                                                2            2005-11-01  17:07:59.000            0001      
110100000000                                                2            2005-11-02  17:08:05.000            0003      
110100000000                                                5            2005-11-03  17:08:11.000            0001      
110100000000                                                1            2005-11-10  17:11:17.000            0004      
110100000000                                                1            2005-11-11  17:11:23.000            0004      
110100000000                                                1            2005-11-12  17:11:41.000            0004      
110100000000                                                1            2005-11-13  17:11:46.000            0004      
110100000000                                                1            2005-11-16  09:17:09.000            0001      
110100004101                                                1            2005-11-16  09:17:09.000            0001      
 
select  top  10  wzdm1,sl1,ckdm1,wzdm2,sl2,ckdm2,o_date  from  zc  
110100000000                                                3            0001                110100000000                                                3            0002                2005-11-18  00:00:00.000  
110100000000                                                2            0001                110100000000                                                2            0002                2005-11-23  00:00:00.000  
110100000000                                                3            0001                110100000000                                                3            0002                2005-11-26  00:00:00.000  
110100000000                                                3            0001                110100000000                                                3            0002                2005-11-27  00:00:00.000  
110100000000                                                2            0001                110100000000                                                2            0002                2005-11-27  00:00:00.000  
110100000000                                                2            0001                110100000000                                                2            0002                2005-11-27  00:00:00.000  
110100000000                                                1            0001                110100000000                                                1            0002                2005-11-27  00:00:00.000  
110100000000                                                2            0001                110100000000                                                2            0002                2005-11-27  00:00:00.000  
110100000000                                                1            0001                110100000000                                                1            0002                2005-11-27  00:00:00.000  
110100000000                                                1            0001                110100000000                                                1            0002                2005-11-27  00:00:00.000  
 
select  top  10  wzdm,sl,ckdm  from  cun  
110100000000                                                -393            0001      
110100000000                                                -96            0002      
110100004102                                                0            0001      
110100004103                                                5            0001      
110100004104                                                8            0001      
110100004105                                                0            0001      
110100004201                                                8            0001      
110100004202                                                2            0001      
110100000001                                                1            0001      
110100000000                                                2            0004