总帐明细账查询报表--案例

（原帖）

-- 总帐明细账查询报表 -- 案例

--select left('claro',2) 整理于西安， -06-06 01:45:12.750

/** Microsoft SQL Server 2008 (RTM) - 10.0.1600.22

    (Intel X86)   Jul  9 2008 14:43:34   Copyright (c) 1988-2008

    Microsoft Corporation  Express Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3) **/

 

CREATE TABLE #TBStart (

ZDate varchar ( 10) ,    -- 起账日期

ZCode varchar ( 40) , -- 物料编号

ZStartQty decimal ( 9, 2),   -- 期初数量

ZStartAmt decimal ( 9, 2) ) -- 期初金额

GO

INSERT INTO #TBStart

SELECT '2009-01-01' , 'DFAM102/4*1' , 100.00, 120.20 UNION ALL

SELECT '2008-12-31' , 'DFAM102/4*4' , 500.00, 522.08

 

CREATE TABLE #TBBill (

ZRefno varchar ( 20) , -- 单据编号

ZDate varchar ( 10) , -- 发生日期

ZFlag varchar ( 20) , -- 出入库的区分标志 ( 入库单 / 出库单 )

Code varchar ( 40) , -- 物料编号

ZPrice decimal ( 9, 4) , -- 单价

ZQty decimal ( 9, 2) , -- 发生数量

ZAmt decimal ( 9, 2) ) -- 发生金额

GO

INSERT INTO #TBBill

SELECT 'I901001' , '2009-01-02' , ' 入库单 ' , 'DFAM102/4*1' , 2.0000, 200.00, 240.00 UNION ALL

SELECT 'O901001' , '2009-01-03' , ' 出库单 ' , 'DFAM102/4*1' , 1.2020, 100.00, 120.20

 

/*

要求输出台账结果：

物料编号： DFAM102/4*1  物料名称：安全型压线帽            物料规格： *1mm  Φ

 

发生日期 单据编号 摘要      期初数 期初额 本期入数本期入额 本期出数本期出额本期存数本期存额

2009-01-01 期初数据     100   120.20                             100    120.20

2009-01-02 I901001    入库单       200    240.00                300    360.20

2009-01-03 O901001    出库单                     100   120.20   200    240.00

......*/

/** 从结果看楼主给的 TBStart 表中 DFAM102/4*1 物料的起账日期应该是 -01-01 ，故更正 **/

--sql2005

--> 建立公用表达式查询

; with cte as (

select a . ZDate ZDate1 , a . ZCode , a . ZStartQty , a . ZStartAmt , b . ZDate ZDate2 , b . ZRefno , b . ZFlag , b . ZPrice , b . ZQty , b . ZAmt

from #TBStart a

join #TBBill b on a . ZCode = b . Code )

, cte1 as (

SELECT ZDate2 as 发生日期

, ZRefno as 单据编号 , ZFlag as 摘要 , '' as 期初数 , '' as 期初额 ,

case when ZFlag = ' 入库单 ' then cast ( ZQty as   varchar ( 20)) else '' end as 本期入数 ,

case when ZFlag = ' 入库单 ' then cast ( ZAmt as   varchar ( 20)) else '' end as 本期入额 ,

case when ZFlag = ' 出库单 ' then cast ( ZQty as   varchar ( 20)) else '' end as 本期出数 ,

case when ZFlag = ' 出库单 ' then cast ( ZAmt as   varchar ( 20)) else '' end as 本期出额 ,

case when ZFlag = ' 入库单 ' then cast ( ZQty + ZStartQty as   varchar ( 20)) when ZFlag = ' 出库单 '

  then '-' + cast (( select ZQty + ZStartQty from cte a where ZFlag = ' 入库单 ' )- ZQty as   varchar ( 20)) else '' end as 本期存数 ,

case when ZFlag = ' 入库单 ' then ZAmt + ZStartAmt when ZFlag = ' 出库单 '

  then '-' + cast (( select ZAmt + ZStartAmt from cte a where ZFlag = ' 入库单 ' )- ZAmt as   varchar ( 20)) else '' end as 本期存额

from cte )

 

SELECT distinct ZDate1 as 发生日期

, ' 期初数据 ' as 单据编号

, '' as 摘要

, cast ( ZStartQty as   varchar ( 20)) as 期初数

, cast ( ZStartAmt as   varchar ( 20)) as 期初额

, '' as 本期入数

, '' as 本期入额

, '' as 本期出数

, '' as 本期出额

, 本期存数 =( select sum ( cast ( 本期存数 as decimal ( 9, 2))) from cte1 )

, 本期存额 =( select sum ( cast ( 本期存额 as decimal ( 9, 2))) from cte1 )

  from cte

  union all

select 发生日期

, 单据编号

, 摘要

, 期初数

, 期初额

, 本期入数

, 本期入额

, 本期出数

, 本期出额

, cast ( replace ( 本期存数 , '-' , '' ) as decimal ( 9, 2)) 本期存数

, cast ( replace ( 本期存额 , '-' , '' ) as decimal ( 9, 2)) 本期存额

  from cte1

 

  /*

发生日期 单据编号 摘要 期初数   期初额   本期入数 本期入额 本期出数 本期出额 本期存数 本期存额

2009-01-01 期初数据   100.00 120.20                              100.00    120.20

2009-01-02 I901001    入库单      200.00 240.00                300.00 360.20

2009-01-03 O901001    出库单                    100.00 120.20  200.00 240.00

*/