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

（原帖）

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

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

/** MicrosoftSQL 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-02I901001    入库单      200    240.00                300   360.20

2009-01-03O901001    出库单                    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

*/