递减问题处理

Pact_Alice 2015-10-13 04:22:35
--我想得到一列如
--第一行的Number不变
--第二行的Number是第二行的Number减去第一行的Number值
--第三行的Number 是第三行Number减去第二行的减值(也就是第二行的Number减去第一行的Number值)
--第四行的Number 是第四行的Number减去第三行的减值
--依次地推,,
--跨年分的话 如果年份变为12年 那么12年1月的数据就是用12年1月 减去11年12月的数据
--
--令一列就是即使跨年分了,那么12年一月的数据还是12年的数据,不用减去11年12月份的,12年2月的Number就是2月的数据减去12年1月的NUmber

--总体说我想得到两列 区别就在于跨年分的第一个月的计算是否包括上一年的最后一个月
;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 * FROM cet
...全文
181 9 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
道玄希言 2015-10-13
  • 打赏
  • 举报
回复
上面条件范围可以写成

where [Year] >= DATEPART(YEAR,convert(date, @StartDate,120))
and [Year] <= DATEPART(YEAR,convert(date, @enddate,120))
and [Month]>= DATEPART(MONTH,convert(date, @StartDate,120))
and [Month]<= DATEPART(MONTH,convert(date, @enddate,120))


where convert(date, convert(nvarchar(4),[Year])+'-'+convert(nvarchar(2),[Month])+'-01', 120) >= convert(date, @StartDate,120)
and convert(date, convert(nvarchar(4),[Year])+'-'+convert(nvarchar(2),[Month])+'-01', 120) <= convert(date, @enddate,120)
道玄希言 2015-10-13
  • 打赏
  • 举报
回复
引用 7 楼 Landa_Alice 的回复:
DECLARE @StartDate DATETIME,@enddate DATETIME
SET @StartDate='2011-02-01'
SET @enddate='2011-05-04'
--用2011-05对应的NUmber减去2-4月NUmber的总和
;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 --183491.4000

)
SELECT * FROM cet


;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
where (时间范围)
)
select
t1.CustomerCode, t1.[Year], t1.[Month],
isnull(t1.[Number],0) as [本月],
isnull(t2.[Number],0) as [上月] ,
isnull(t2.[Num],0) as [之前月份和],
(isnull(t2.[Number],0) - isnull(t1.[Number],0)) as [本月减上月],
(case t1.[Month] when 1 then isnull(t1.[Number],0)
else isnull(t2.[Number],0) - isnull(t1.[Number],0) end) as [本月减上月不包年初],
(isnull(t1.[Number],0) - isnull(t2.[Num],0)) as [本月减之前月和]
from tb1 as t1
left join
(select nid, Number, (select sum(isnull(Number,0))
from tb1 where nid <= t.nid) as [Num] from tb1 as t)
as t2
on t1.nid = t2.nid + 1
Pact_Alice 2015-10-13
  • 打赏
  • 举报
回复
引用 5 楼 yangb0803 的回复:

;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

DECLARE @StartDate DATETIME,@enddate DATETIME
SET @StartDate='2011-02-01'
SET @enddate='2011-05-04'
--用2011-05对应的NUmber减去2-4月NUmber的总和
;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 --183491.4000

)
SELECT * FROM cet
道玄希言 2015-10-13
  • 打赏
  • 举报
回复

;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
Pact_Alice 2015-10-13
  • 打赏
  • 举报
回复
引用 4 楼 zbdzjx 的回复:
再试一下这个,看看对不对。
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]


天天说递归,为什么我想不起来递归处理呢,哎,没带脑子,
就是刚刚问您的问题请你帮我看看啊,就是定义两个参数的问题,
DECLARE @StartDate DATETIME,@enddate DATETIME
SET @StartDate='2011-02-01'
SET @enddate='2011-05-04'
--用2011-05-04对应的NUmber减去2-4月NUmber的总和
;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 --183491.4000

)
SELECT * FROM cet


zbdzjx 2015-10-13
  • 打赏
  • 举报
回复
再试一下这个,看看对不对。
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]

Pact_Alice 2015-10-13
  • 打赏
  • 举报
回复
引用 2 楼 zbdzjx 的回复:
噢,内容看错了,上面的代码不对。

谢谢你的回答,没事,我是实在没有思路才问各位朋友们的,
上两个问题是我自己想写出来的,最急最主要的问题就是
DECLARE @StartDate DATETIME,@enddate DATETIME
SET @StartDate='2011-02'
SET @enddate='2011-05'
--如果我定义两个参数,开始时间结束时间
--我想用结束时间的Number 减去开始时间到结束前时间的月NUmber
--比如开始时间是2011-02 结束时间是2011-05
--那么得到的结果就是用 2011-05的NUmber值减去(2011-02的值+2011-03的值+2011-04的值)
--354945.8000-(146905.9200+224084.9200+318360.3200)
这个问题比较的急,可以先帮我看看吗,上面的两个问题不太急,给自己做练习的
zbdzjx 2015-10-13
  • 打赏
  • 举报
回复
噢,内容看错了,上面的代码不对。
zbdzjx 2015-10-13
  • 打赏
  • 举报
回复
这样??
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

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧