34,838
社区成员




;WITH cte AS (
select '1036000' AS CustomerCode, 2011 AS [Year], 1 AS [Month], 146435.4600 AS [Number] union all
select '1036000', 2011, 2, 146905.9200 union ALL --77179.0000
select '1036000', 2011, 3, 224084.9200 union ALL --146905.9200
select '1036000', 2011, 4, 318360.3200 union ALL --171454.4000
select '1036000', 2011, 5, 354945.8000 union ALL --183491.4000
select '1036000', 2011, 6, 384152.5000 union ALL --200661.1000
select '1036000', 2011, 7, 353857.8900 union ALL --153196.7900
select '1036000', 2011, 8, 304991.9800 union ALL --151795.1900
select '1036000', 2011, 9, 233037.2000 union ALL --81242.0100
select '1036000', 2011, 10, 217856.1600 union ALL --136614.1500
select '1036000', 2011, 11, 141653.8400 union ALL --5039.6900
select '1036000', 2011, 12, 194081.8600 union ALL --189042.1700
select '1036000', 2012, 1, 181120.0100 union ALL ---7922.1600
select '1036000', 2012, 2, 211208.4000 --219130.5600
),
tb1 as
(
select
ROW_NUMBER() OVER(order by [Year], [Month]) as nid,
CustomerCode, [Year], [Month], [Number] from cte
)
select
t1.CustomerCode, t1.[Year], t1.[Month], t1.[Number], t2.[Number],
(t2.[Number] - t1.[Number]) as n1,
(case t1.[Month] when 1 then t1.[Number]
else t2.[Number] - t1.[Number] end) as n2
from tb1 as t1
left join tb1 as t2
on t1.nid = t2.nid + 1
WITH cet AS (
select '1036000' AS CustomerCode, 2011 AS [Year], 1 AS [Month], 146435.4600 AS [Number] union all
select '1036000', 2011, 2, 146905.9200 union ALL --77179.0000
select '1036000', 2011, 3, 224084.9200 union ALL --146905.9200
select '1036000', 2011, 4, 318360.3200 union ALL --171454.4000
select '1036000', 2011, 5, 354945.8000 union ALL --183491.4000
select '1036000', 2011, 6, 384152.5000 union ALL --200661.1000
select '1036000', 2011, 7, 353857.8900 union ALL --153196.7900
select '1036000', 2011, 8, 304991.9800 union ALL --151795.1900
select '1036000', 2011, 9, 233037.2000 union ALL --81242.0100
select '1036000', 2011, 10, 217856.1600 union ALL --136614.1500
select '1036000', 2011, 11, 141653.8400 union ALL --5039.6900
select '1036000', 2011, 12, 194081.8600 union ALL --189042.1700
select '1036000', 2012, 1, 181120.0100 union ALL ---7922.1600
select '1036000', 2012, 2, 211208.4000 --219130.5600
)
,cet2 as
(
SELECT CustomerCode, [Year], [Month],[Number], 0 level, convert(decimal(18,4), isnull([Number],0)) [Number1] FROM cet WHERE [Year]=2011 and [Month]=1
UNION ALL
SELECT a.CustomerCode, a.[Year], a.[Month],a.[Number],b.level+1,CONVERT(decimal(18,4), isnull(a.[Number],0)-isnull(b.Number1,0)) FROM cet A,cet2 b
where a.Year*12+a.Month=b.Year*12+b.Month+1
)
select * from cet2
order by [YEAR],[MONTH]
WITH cet AS (
select '1036000' AS CustomerCode, 2011 AS [Year], 1 AS [Month], 146435.4600 AS [Number] union all
select '1036000', 2011, 2, 146905.9200 union ALL --77179.0000
select '1036000', 2011, 3, 224084.9200 union ALL --146905.9200
select '1036000', 2011, 4, 318360.3200 union ALL --171454.4000
select '1036000', 2011, 5, 354945.8000 union ALL --183491.4000
select '1036000', 2011, 6, 384152.5000 union ALL --200661.1000
select '1036000', 2011, 7, 353857.8900 union ALL --153196.7900
select '1036000', 2011, 8, 304991.9800 union ALL --151795.1900
select '1036000', 2011, 9, 233037.2000 union ALL --81242.0100
select '1036000', 2011, 10, 217856.1600 union ALL --136614.1500
select '1036000', 2011, 11, 141653.8400 union ALL --5039.6900
select '1036000', 2011, 12, 194081.8600 union ALL --189042.1700
select '1036000', 2012, 1, 181120.0100 union ALL ---7922.1600
select '1036000', 2012, 2, 211208.4000 --219130.5600
)
SELECT a.*
, b.Number
, c.Number
FROM cet a
left join cet b on a.Year*12+a.Month=b.Year*12+b.Month-1
left join cet c on a.Year=c.Year and a.Month=c.Month-1