while(@id>0) begin select @a= #order.a,@cust = #order.顾客 from #order where id=@id set @期末=cast((substring(CONVERT(varchar(100), @a,112),7,2)) as int)
while(convert(date,@a) >=convert(date,'2012-10-01')) begin declare @末 datetime declare @前 datetime set @末 =CONVERT(date, @a) set @前 =CONVERT(date, dateadd(month,datediff(month,0, @a)-1,@期末)) update #order set 一个月=isnull(#order.一个月,0)+A.一个月 from (select T_顾客.顾客, sum((isnull(dbo.T_顾客详细.金額,0))) as 一个月 from dbo.T_顾客 where dbo.T_顾客.工厂区分='1' and 受注日 <=@末 and 受注日>=@前 group by T_顾客.顾客)A where #order.顾客=@cust
set @a =dateadd(month,datediff(month,0, @a)-1,@期末-1) end set @id =@id-1
其实我觉得大概这样的思路也可以统计: select 客户id,日期,sum(销售额) from tb where 条件 group by 客户id,日期 当然日期可能需要做些处理,你觉得这个思路在你系统中柯行不?
其实只需要这个就行了,然后把上面的语句合并进去:update #order set 一个月=isnull(#order.一个月,0)+A.一个月 from ( select T_顾客.顾客, sum((isnull(dbo.T_顾客详细.金額,0))) as 一个月 from dbo.T_顾客 where dbo.T_顾客.工厂区分='1' and 受注日 <=@末 and 受注日>=@前 group by T_顾客.顾客 )A where #order.顾客=@cust
看不怎么懂你的意思可以说详细点吗呵呵,我把有些你原来的代码注释掉了,给适当简化了,不知道是不是这个意思: while(@id>0) begin/* select @a= #order.a,@cust = #order.顾客 from #order where id=@id set @期末=cast((substring(CONVERT(varchar(100), @a,112),7,2)) as int) while(convert(date,@a) >=convert(date,'2012-10-01')) begin declare @末 datetime declare @前 datetime set @末 =CONVERT(date, @a) set @前 =CONVERT(date, dateadd(month,datediff(month,0, @a)-1,@期末)) */update #order set 一个月=isnull(#order.一个月,0)+A.一个月 from ( select T_顾客.顾客, sum((isnull(dbo.T_顾客详细.金額,0))) as 一个月 --此处计算的是从期初-期末的总和 from dbo.T_顾客 where dbo.T_顾客.工厂区分='1' and 受注日 <= #order.a and 受注日>= convert(date,'2012-10-01')) group by T_顾客.顾客 )A where id= @id/* set @a =dateadd(month,datediff(month,0, @a)-1,@期末-1) end */set @id =@id-1end
看不怎么懂你的意思可以说详细点吗呵呵,我把有些你原来的代码注释掉了,给适当简化了,不知道是不是这个意思: while(@id>0) begin/* select @a= #order.a,@cust = #order.顾客 from #order where id=@id set @期末=cast((substring(CONVERT(varchar(100), @a,112),7,2)) as int) while(convert(date,@a) >=convert(date,'2012-10-01')) begin declare @末 datetime declare @前 datetime set @末 =CONVERT(date, @a) set @前 =CONVERT(date, dateadd(month,datediff(month,0, @a)-1,@期末)) */update #order set 一个月=isnull(#order.一个月,0)+A.一个月 from ( select T_顾客.顾客, sum((isnull(dbo.T_顾客详细.金額,0))) as 一个月 --此处计算的是从期初-期末的总和 from dbo.T_顾客 where dbo.T_顾客.工厂区分='1' and 受注日 <= #order.a and 受注日>= convert(date,'2012-10-01')) group by T_顾客.顾客 )A where id= @id/* set @a =dateadd(month,datediff(month,0, @a)-1,@期末-1) end */set @id =@id-1end谢谢啦,我以前是这样写的 但是会出现 7月出售了价格1250元的产品 税收是1.05 所以价格为 1250*1.05=1312.5 四舍五入 为1313 入金表 入金1313元8月出售了价格2250元的产品 税收是1.05 所以价格为 2250*1.05=2362.5 四舍五入 为2363 入金表 入金2363元9月出售了价格2250元的产品 税收是1.05 所以价格为 2250*1.05=2362.5 四舍五入 为2363 入金表 入金2363元当我查询的时候10月以前的收支平衡的时候 税后出售的金额的总和-收入的金额总和 即 税后出售的金额的总和 为 1312.5+2362.5+2632.5 =6037.5 四舍五入 6038 收入的金额总和为1313+2363+2363 =6039 这时 收支出现了-1的误差(其实本来每个月的税后出售额跟入金额都没错的)这样的错误。所以我才会写了个循环 让先算出每个月的出售额的合计,然后四舍五入。 再把每个月四舍五入的值给他加起来。 (因为之前的数据已经到了去年10月份,而且帐票也出了, 实在没办法改以前计算出售额的逻辑了,现在如果改以前的逻辑,那会有好多的数据不匹配)
看不怎么懂你的意思可以说详细点吗呵呵,我把有些你原来的代码注释掉了,给适当简化了,不知道是不是这个意思: while(@id>0) begin/* select @a= #order.a,@cust = #order.顾客 from #order where id=@id set @期末=cast((substring(CONVERT(varchar(100), @a,112),7,2)) as int) while(convert(date,@a) >=convert(date,'2012-10-01')) begin declare @末 datetime declare @前 datetime set @末 =CONVERT(date, @a) set @前 =CONVERT(date, dateadd(month,datediff(month,0, @a)-1,@期末)) */update #order set 一个月=isnull(#order.一个月,0)+A.一个月 from ( select T_顾客.顾客, sum((isnull(dbo.T_顾客详细.金額,0))) as 一个月 --此处计算的是从期初-期末的总和 from dbo.T_顾客 where dbo.T_顾客.工厂区分='1' and 受注日 <= #order.a and 受注日>= convert(date,'2012-10-01')) group by T_顾客.顾客 )A where id= @id/* set @a =dateadd(month,datediff(month,0, @a)-1,@期末-1) end */set @id =@id-1end谢谢啦,我以前是这样写的 但是会出现 7月出售了价格1250元的产品 税收是1.05 所以价格为 1250*1.05=1312.5 四舍五入 为1313 入金表 入金1313元8月出售了价格2250元的产品 税收是1.05 所以价格为 2250*1.05=2362.5 四舍五入 为2363 入金表 入金2363元9月出售了价格2250元的产品 税收是1.05 所以价格为 2250*1.05=2362.5 四舍五入 为2363 入金表 入金2363元当我查询的时候10月以前的收支平衡的时候 税后出售的金额的总和-收入的金额总和 即 税后出售的金额的总和 为 1312.5+2362.5+2632.5 =6037.5 四舍五入 6038 收入的金额总和为1313+2363+2363 =6039 这时 收支出现了-1的误差(其实本来每个月的税后出售额跟入金额都没错的)这样的错误。所以我才会写了个循环 让先算出每个月的出售额的合计,然后四舍五入。 再把每个月四舍五入的值给他加起来。 (因为之前的数据已经到了去年10月份,而且帐票也出了, 实在没办法改以前计算出售额的逻辑了,现在如果改以前的逻辑,那会有好多的数据不匹配)哦,改了一下,你看看这样行吗: while(@id>0) begin/* select @a= #order.a,@cust = #order.顾客 from #order where id=@id set @期末=cast((substring(CONVERT(varchar(100), @a,112),7,2)) as int) while(convert(date,@a) >=convert(date,'2012-10-01')) begin declare @末 datetime declare @前 datetime set @末 =CONVERT(date, @a) set @前 =CONVERT(date, dateadd(month,datediff(month,0, @a)-1,@期末)) */declare @one_month 一个月字段的数据类型select @one_month = 一个月 from #order where id= @idselect @one_month = @one_month + sum((isnull(dbo.T_顾客详细.金額,0))) from #order inner join dbo.T_顾客 on dbo.T_顾客.工厂区分='1' and 受注日 <= #order.a and 受注日>= convert(date,'2012-10-01') group by T_顾客.顾客,convert(varchar(7),受注日,120) update #order set 一个月=isnull(#order.一个月,0)+@one_month where id= @id/* set @a =dateadd(month,datediff(month,0, @a)-1,@期末-1) end */set @id =@id-1end
看不怎么懂你的意思可以说详细点吗呵呵,我把有些你原来的代码注释掉了,给适当简化了,不知道是不是这个意思: while(@id>0) begin/* select @a= #order.a,@cust = #order.顾客 from #order where id=@id set @期末=cast((substring(CONVERT(varchar(100), @a,112),7,2)) as int) while(convert(date,@a) >=convert(date,'2012-10-01')) begin declare @末 datetime declare @前 datetime set @末 =CONVERT(date, @a) set @前 =CONVERT(date, dateadd(month,datediff(month,0, @a)-1,@期末)) */update #order set 一个月=isnull(#order.一个月,0)+A.一个月 from ( select T_顾客.顾客, sum((isnull(dbo.T_顾客详细.金額,0))) as 一个月 --此处计算的是从期初-期末的总和 from dbo.T_顾客 where dbo.T_顾客.工厂区分='1' and 受注日 <= #order.a and 受注日>= convert(date,'2012-10-01')) group by T_顾客.顾客 )A where id= @id/* set @a =dateadd(month,datediff(month,0, @a)-1,@期末-1) end */set @id =@id-1end谢谢啦,我以前是这样写的 但是会出现 7月出售了价格1250元的产品 税收是1.05 所以价格为 1250*1.05=1312.5 四舍五入 为1313 入金表 入金1313元8月出售了价格2250元的产品 税收是1.05 所以价格为 2250*1.05=2362.5 四舍五入 为2363 入金表 入金2363元9月出售了价格2250元的产品 税收是1.05 所以价格为 2250*1.05=2362.5 四舍五入 为2363 入金表 入金2363元当我查询的时候10月以前的收支平衡的时候 税后出售的金额的总和-收入的金额总和 即 税后出售的金额的总和 为 1312.5+2362.5+2632.5 =6037.5 四舍五入 6038 收入的金额总和为1313+2363+2363 =6039 这时 收支出现了-1的误差(其实本来每个月的税后出售额跟入金额都没错的)这样的错误。所以我才会写了个循环 让先算出每个月的出售额的合计,然后四舍五入。 再把每个月四舍五入的值给他加起来。 (因为之前的数据已经到了去年10月份,而且帐票也出了, 实在没办法改以前计算出售额的逻辑了,现在如果改以前的逻辑,那会有好多的数据不匹配)这种小数点的问题一般是最后统计才四舍五入好,而且最好精度要高
里面的#order.a 可能会等于2013-10-20哦这样的我希望的是 求 sum((isnull(dbo.T_顾客详细.金額,0))) 这个sum 的时候 受注日的判断是1个月一个月的 例如 受注日 >=2013-09-21 and 受注日 <=2013-10-20这样的 也就是说 那个order.a 在我这边 仅仅只是为了判断得出20这个数字 哦,那你的统计规则是什么,就是这个:select T_顾客.顾客, sum((isnull(dbo.T_顾客详细.金額,0))) as 一个月 from dbo.T_顾客 where dbo.T_顾客.工厂区分='1' and 受注日 <=@末 and 受注日>=@前 group by T_顾客.顾客计算的是每个客户的,金额的总和,这个受注日是日期吗 还是其他的
如果客户数很多,你的while哪怕就统计一天都不行的,按照我昨天给你的脚本有哪些问题啊?
里面的#order.a 可能会等于2013-10-20哦这样的我希望的是 求 sum((isnull(dbo.T_顾客详细.金額,0))) 这个sum 的时候 受注日的判断是1个月一个月的 例如 受注日 >=2013-09-21 and 受注日 <=2013-10-20这样的 也就是说 那个order.a 在我这边 仅仅只是为了判断得出20这个数字 哦,那你的统计规则是什么,就是这个:select T_顾客.顾客, sum((isnull(dbo.T_顾客详细.金額,0))) as 一个月 from dbo.T_顾客 where dbo.T_顾客.工厂区分='1' and 受注日 <=@末 and 受注日>=@前 group by T_顾客.顾客计算的是每个客户的,金额的总和,这个受注日是日期吗 还是其他的受注日是日期 但是@末跟@前 是重新计算过的,是一个月的区间
里面的#order.a 可能会等于2013-10-20哦这样的我希望的是 求 sum((isnull(dbo.T_顾客详细.金額,0))) 这个sum 的时候 受注日的判断是1个月一个月的 例如 受注日 >=2013-09-21 and 受注日 <=2013-10-20这样的 也就是说 那个order.a 在我这边 仅仅只是为了判断得出20这个数字 哦,那你的统计规则是什么,就是这个:select T_顾客.顾客, sum((isnull(dbo.T_顾客详细.金額,0))) as 一个月 from dbo.T_顾客 where dbo.T_顾客.工厂区分='1' and 受注日 <=@末 and 受注日>=@前 group by T_顾客.顾客计算的是每个客户的,金额的总和,这个受注日是日期吗 还是其他的受注日是日期 但是@末跟@前 是重新计算过的,是一个月的区间是个怎么样的区间呢,一个月的几号-几号
select 客户id,日期,sum(销售额) from tb where 条件 group by 客户id,日期昨天的这个我后面试了一下, 客户id 和日期是在一张表里面 而销售额 是在另外一张表 所以我要一个while语句先把顾客表里面的 客户id,日期给取出来吧(因为日期需要处理) 然后在处理日期区间的时候,也许是因为我之前思维被固定了,感觉还是只能靠while语句来处理。
里面的#order.a 可能会等于2013-10-20哦这样的我希望的是 求 sum((isnull(dbo.T_顾客详细.金額,0))) 这个sum 的时候 受注日的判断是1个月一个月的 例如 受注日 >=2013-09-21 and 受注日 <=2013-10-20这样的 也就是说 那个order.a 在我这边 仅仅只是为了判断得出20这个数字 哦,那你的统计规则是什么,就是这个:select T_顾客.顾客, sum((isnull(dbo.T_顾客详细.金額,0))) as 一个月 from dbo.T_顾客 where dbo.T_顾客.工厂区分='1' and 受注日 <=@末 and 受注日>=@前 group by T_顾客.顾客计算的是每个客户的,金额的总和,这个受注日是日期吗 还是其他的受注日是日期 但是@末跟@前 是重新计算过的,是一个月的区间是个怎么样的区间呢,一个月的几号-几号这个区间会变的哦,不同客户 不一样
select 客户id,日期,sum(销售额) from tb where 条件 group by 客户id,日期昨天的这个我后面试了一下, 客户id 和日期是在一张表里面 而销售额 是在另外一张表 所以我要一个while语句先把顾客表里面的 客户id,日期给取出来吧(因为日期需要处理) 然后在处理日期区间的时候,也许是因为我之前思维被固定了,感觉还是只能靠while语句来处理。你再关联另外一张
select @a= #order.a,@cust = #order.顾客 from #order where id=@idset @期末=cast((substring(CONVERT(varchar(100), @a,112),7,2)) as int)根据这个#order.a 临时表的然后推算回来
select 客户id,日期,sum(销售额) from tb where 条件 group by 客户id,日期昨天的这个我后面试了一下, 客户id 和日期是在一张表里面 而销售额 是在另外一张表 所以我要一个while语句先把顾客表里面的 客户id,日期给取出来吧(因为日期需要处理) 然后在处理日期区间的时候,也许是因为我之前思维被固定了,感觉还是只能靠while语句来处理。你再关联另外一张但是日期处理要怎么办,我2个while 第一个是为了区分顾客跟取出日期 第二个是根据日期推算每个月的区间while(@id>0) begin select @a= #order.a,@cust = #order.顾客 from #order where id=@id set @期末=cast((substring(CONVERT(varchar(100), @a,112),7,2)) as int)
while(convert(date,@a) >=convert(date,'2012-10-01')) begin declare @末 datetime declare @前 datetime set @末 =CONVERT(date, @a) set @前 =CONVERT(date, dateadd(month,datediff(month,0, @a)-1,@期末)) update #order set 一个月=isnull(#order.一个月,0)+A.一个月 from (select T_顾客.顾客, sum((isnull(dbo.T_顾客详细.金額,0))) as 一个月 from dbo.T_顾客 where dbo.T_顾客.工厂区分='1' and 受注日 <=@末 and 受注日>=@前 group by T_顾客.顾客)A where #order.顾客=@custset @a =dateadd(month,datediff(month,0, @a)-1,@期末-1) end set @id =@id-1 end
我的意思是直接统计,不用while
对了,这个:set @期末=cast((substring(CONVERT(varchar(100), @a,112),7,2)) as int) 这个计算出来的 @期末是什么意思,就是一个int值?然后后面,再把 set @前 =CONVERT(date, dateadd(month,datediff(month,0, @a)-1,@期末))转为日期?这些是什么意思
@前 就是起始日期 @末 结束日期嗯,我明白, 但是对于某一个确定的客户,他的区间应该是固定的,比如想你说的,是按10月21号到11月20号,那么往前推,就是9月21到10月20 对吧。而且,不需要太复杂,比如 10月21号到11月20号,对这个客户,我们可以计算为 11月,9月21到10月20就是9月,然后group by 一下,就可以计算出 9月和10月,这个客户分别的金额了。对就是这样,但是这个group by要怎么弄呢这个: while(@id>0) begindeclare @a datetime declare @one_month 一个月字段的数据类型select @one_month = 一个月,@a = a from #order where id= @idselect @one_month = @one_month + sum((isnull(dbo.T_顾客详细.金額,0))) from ( select dateadd(MONTH,-number-0,@a) 末,dateadd(MONTH,-number-1,@a) 前 from master..spt_values t where t.type = 'P' and datediff(month,'2012-10-01',@a)>= t.number ) t inner join dbo.T_顾客 on dbo.T_顾客.工厂区分='1' and 受注日 <= t.末 and 受注日>= t.前 group by T_顾客.顾客,update #order set 一个月=isnull(#order.一个月,0)+@one_month where id= @id set @id =@id-1end
修改一下: while(@id>0) begindeclare @a datetime declare @one_month 一个月字段的数据类型select @one_month = 一个月,@a = a from #order where id= @idselect @one_month = @one_month + sum((isnull(dbo.T_顾客详细.金額,0))) from ( select dateadd(MONTH,-number-0,@a) 末,dateadd(MONTH,-number-1,@a) 前, from master..spt_values t where t.type = 'P' and datediff(month,'2012-10-01',@a)>= t.number ) t inner join dbo.T_顾客 on dbo.T_顾客.工厂区分='1' and 受注日 <= t.末 and 受注日>= t.前 group by T_顾客.顾客,dateadd(MONTH,-number-0,@a)update #order set 一个月=isnull(#order.一个月,0)+@one_month where id= @id set @id =@id-1end
再改一下:while(@id>0) begindeclare @a datetime declare @one_month 一个月字段的数据类型select @one_month = 一个月,@a = a from #order where id= @idselect @one_month = @one_month + sum((isnull(dbo.T_顾客详细.金額,0))) from ( select dateadd(MONTH,-number-0,@a) 末,dateadd(day,1,dateadd(MONTH,-number-1,@a)) 前 from master..spt_values t where t.type = 'P' and datediff(month,'2012-10-01',@a)>= t.number ) t inner join dbo.T_顾客 on dbo.T_顾客.工厂区分='1' and 受注日 <= t.末 and 受注日>= t.前 group by T_顾客.顾客,dateadd(MONTH,-number-0,@a)update #order set 一个月=isnull(#order.一个月,0)+@one_month where id= @id set @id =@id-1enddeclare @a datetime--如果@a 为'2013-10-28' set @a = '2013-10-28' --自动计算月份的末、前 select dateadd(MONTH,-number-0,@a) 末,dateadd(day,1,dateadd(MONTH,-number-1,@a)) 前 from master..spt_values t where t.type = 'P' and datediff(month,'2012-10-01',@a)>= t.number /* 末 前 2013-10-28 00:00:00.000 2013-09-29 00:00:00.000 2013-09-28 00:00:00.000 2013-08-29 00:00:00.000 2013-08-28 00:00:00.000 2013-07-29 00:00:00.000 2013-07-28 00:00:00.000 2013-06-29 00:00:00.000 2013-06-28 00:00:00.000 2013-05-29 00:00:00.000 2013-05-28 00:00:00.000 2013-04-29 00:00:00.000 2013-04-28 00:00:00.000 2013-03-29 00:00:00.000 2013-03-28 00:00:00.000 2013-03-01 00:00:00.000 2013-02-28 00:00:00.000 2013-01-29 00:00:00.000 2013-01-28 00:00:00.000 2012-12-29 00:00:00.000 2012-12-28 00:00:00.000 2012-11-29 00:00:00.000 2012-11-28 00:00:00.000 2012-10-29 00:00:00.000 2012-10-28 00:00:00.000 2012-09-29 00:00:00.000 */
生成时间维的脚本 IF OBJECT_ID('Fn_ISDATE','FN') IS NOT NULL DROP FUNCTION Fn_ISDATE GO CREATE FUNCTION [Fn_ISDATE] (@i_tjrq INTEGER) RETURNS INTEGER--返回0为正确,1为错误。 AS BEGIN DECLARE @i_year INTEGER;--定义变量存储输入参数的年部分 DECLARE @i_month INTEGER;--定义变量存储输入参数的月部分 DECLARE @i_day INTEGER;--定义变量存储输入参数的日部分 -- DECLARE @d_tjrq DATETIME; --对日期长度判断 IF @i_tjrq<10000000 or @i_tjrq>99999999 or @i_tjrq IS null BEGIN RETURN 1; END ELSE SET @i_year=convert(INT,(substring(rtrim(convert(CHAR,(@i_tjrq))),1,4))); SET @i_month=convert(INT,(substring(rtrim(convert(CHAR,(@i_tjrq))),5,2))); SET @i_day=convert(INT,(substring(rtrim(convert(CHAR,(@i_tjrq))),7,2)));
--对月判断,必须在1-12范围内 IF @i_month not between 1 and 12 BEGIN RETURN 1; END --对日判断,1,3,5,7,8,10,12月最大日为31,4,6,9,11月最大日为30,2月若为闰年则为29,其它年则为28. IF @i_day between 1 and 31 BEGIN IF @i_day=31 and @i_month not in (1,3,5,7,8,10,12) BEGIN RETURN 1; END IF @i_month=2 BEGIN IF (((@i_year%4)=0 and (@i_year0)<>0) or (@i_year%400)=0) BEGIN --若为闰年,则最大日为29 IF @i_day>29 BEGIN RETURN 1; END END ELSE --若不为闰年,则最大日为28 IF @i_day>28 BEGIN RETURN 1; END END END ELSE RETURN 1; --若以上情况都不满足,则认为时间合法,返回0 RETURN 0; END /* select pas.Fn_ISDATE(20100119) */
go
go IF OBJECT_ID('Fn_Change_Days','FN') IS NOT NULL DROP FUNCTION Fn_Change_Days GO CREATE FUNCTION [Fn_Change_Days]( @i_day INTEGER, @i_number INTEGER) RETURNS INTEGER AS
BEGIN --modify by huangzj 20100119 添加判断输入日期是否符合要求 DECLARE @DAYS INTEGER SET @DAYS=(SELECT dbo.Fn_ISDATE(@i_day))--调用检查日期函数 DECLARE @RESULT INTEGER--定义变量储存输出结果 IF @DAYS=0--若输入日期合法,返回计算结果 BEGIN SET @RESULT=( convert(INTEGER,convert(CHAR(8),dateadd(d,@i_number,convert(DATETIME,convert(CHAR(8),@i_day))),112))) END ELSE--输入日期不合法,返回@i_day的值以示不计算不成功 BEGIN SET @RESULT= @I_DAY END RETURN @RESULT--返回结果 END
/* select pas.Fn_Change_Days(20100119,-5) */
go IF OBJECT_ID('Fn_DATE','FN') IS NOT NULL DROP FUNCTION Fn_DATE GO CREATE FUNCTION [Fn_DATE] (@i_tjrq INTEGER) RETURNS datetime AS
BEGIN DECLARE @d_tjrq DATETIME--定义变量储存要输出的结果。 --对日期长度判断 IF @i_tjrq<10000000 or @i_tjrq>99999999 or @i_tjrq IS null BEGIN RETURN 1; END ELSE SET @d_tjrq=convert(DATETIME,substring(convert(CHAR,(@i_tjrq)),1,4)+'/'+substring(convert(CHAR,(@i_tjrq)),5,2)+'/'+substring(convert(CHAR,(@i_tjrq)),7,2)) RETURN @d_tjrq; END
/* select pas.Fn_DATE(20100119) */
go
IF OBJECT_ID('Sys_SJW','u') IS NOT NULL DROP TABLE Sys_SJW GO CREATE TABLE [Sys_SJW]( [tjrq] [int] NOT NULL, [yc] [int] NULL, [sr] [int] NULL, [sym] [int] NULL, [xc] [int] NULL, [xm] [int] NULL, [jc] [int] NULL, [jm] [int] NULL, [nc] [int] NULL, [nm] [int] NULL, [ym] [int] NULL, PRIMARY KEY CLUSTERED ( [tjrq] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
go
IF OBJECT_ID('Sp_Init_SJWB','p') IS NOT NULL DROP PROC Sp_Init_SJWB GO CREATE PROCEDURE [Sp_Init_SJWB] @i_tjrq INTEGER AS SET NOCOUNT ON--不返回影响的行数
DECLARE @d_tjrq datetime--时间类型日期 --EXEC sp_Pas_xtrz '0','Sp_Init_SJWB','时间维表数据生成开始' SET @d_tjrq=dbo.Fn_DATE(@i_tjrq) --月初 SET @i_yc=convert(INTEGER,substring(convert(CHAR,(@i_tjrq)),1,4)+substring(convert(CHAR,(@i_tjrq)),5,2)+'01') --月末 SET @i_ym=convert(INTEGER,substring(convert(CHAR,(@i_tjrq)),1,4)+substring(convert(CHAR,(@i_tjrq)),5,2)+right('00'+convert(VARCHAR(2),day(dateadd(month,1,dbo.Fn_DATE(@i_yc))-1)),2)) --上日 SET @i_sr=convert(INTEGER,dbo.Fn_Change_Days(@i_tjrq,-1)) --上月末 SET @i_sym=convert(INTEGER,dbo.Fn_Change_Days(@i_yc,-1))
--旬初旬末 --1至10号为上旬,11至20号为中旬,21至月底为下旬 IF convert(INTEGER,substring(convert(CHAR,(@i_tjrq)),7,2))<=10 --上旬 BEGIN SET @i_xc=@i_yc--旬初 SET @i_xm=convert(INTEGER,substring(convert(CHAR,(@i_tjrq)),1,4)+substring(convert(CHAR,(@i_tjrq)),5,2)+'10')--旬末 END ELSE IF convert(INTEGER,substring(convert(CHAR,(@i_tjrq)),7,2))>10 and convert(INTEGER,substring(convert(CHAR,(@i_tjrq)),7,2))<=20 --中旬 BEGIN SET @i_xc=convert(INTEGER,substring(convert(CHAR,(@i_tjrq)),1,4)+substring(convert(CHAR,(@i_tjrq)),5,2)+'11')--旬初 SET @i_xm=convert(INTEGER,substring(convert(CHAR,(@i_tjrq)),1,4)+substring(convert(CHAR,(@i_tjrq)),5,2)+'20')--旬末 END ELSE --下旬 BEGIN SET @i_xc=convert(INTEGER,substring(convert(CHAR,(@i_tjrq)),1,4)+substring(convert(CHAR,(@i_tjrq)),5,2)+'21')--旬初 SET @i_xm=@i_ym--旬末 END --季初季末 SET @i_jc=convert(INTEGER,substring(convert(CHAR,(@i_tjrq)),1,4)+right('00'+(convert(VARCHAR(2),((((convert(INTEGER,substring(convert(CHAR,(@i_tjrq)),5,2))-1) / 3) * 3) + 1))) ,2)+ '01') SET @i_jm2=DATEADD(DAY,-1,DATEADD(month,3,dbo.Fn_DATE(@i_jc)))
SET @i_jm=rtrim(convert(char(4),year(@i_jm2)))+rtrim(convert(char(2),right('00'+convert(VARCHAR(2),month(@i_jm2)),2)))+rtrim(convert(char(2),right('00'+convert(VARCHAR(2),day(@i_jm2)),2))) --年初 SET @i_nc=substring(convert(CHAR,(@i_tjrq)),1,4)+'0101' --年末 SET @i_nm=substring(convert(CHAR,(@i_tjrq)),1,4)+'1231' -- --上年末 IF exists(select top 1 1 from Sys_SJW where tjrq=@i_tjrq) delete from Sys_SJW where tjrq=@i_tjrq ELSE insert into Sys_SJW([tjrq], [yc], [sr], [sym], [xc], [xm], [jc], [jm], [nc], [nm], [ym]) values(@i_tjrq,@i_yc,@i_sr,@i_sym,@i_xc,@i_xm,@i_jc,@i_jm,@i_nc,@i_nm,@i_ym) --EXEC sp_Pas_xtrz '0','Sp_Init_SJWB','时间维表数据生成结束'
/* exec Sp_Init_SJWB 20090119 select * from xtb_xtrz order by sjdh desc */
go
IF OBJECT_ID('Sp_Init_SJWB_While','p') IS NOT NULL DROP PROC Sp_Init_SJWB_While GO CREATE PROCEDURE [Sp_Init_SJWB_While] @i_tjrq INTEGER AS
BEGIN DECLARE @i_jsrq INTEGER--年末日期 DECLARE @i_ksrq INTEGER --渐增的日期 SET @i_jsrq=convert(CHAR(4),substring(convert(CHAR,(@i_tjrq)),1,4))+'1231' IF substring(convert(CHAR,(@i_tjrq)),5,4)='0101' BEGIN --EXEC sp_Pas_xtrz '0','Sp_Init_SJWB_while','时间维表数据批量生成程序数据生成开始' DELETE FROM Sys_SJW WHERE tjrq>=@i_tjrq SET @i_ksrq=@i_tjrq WHILE (@i_ksrq<=@i_jsrq) BEGIN EXEC Sp_Init_SJWB @i_ksrq SET @i_ksrq=convert(INTEGER,dbo.Fn_Change_Days(@i_ksrq,1)) END --EXEC sp_Pas_xtrz '0','Sp_Init_SJWB_while','时间维表数据批量生成程序数据生成结束' END END /* exec Sp_Init_SJWB_While 20110101 select * from Sys_SJW */
不是固定的 你试试,上面的代码,应该是可以的 不行,那个看起来好像是 计算月份的末、前然后加起来 但其实select @one_month = @one_month + sum((isnull(dbo.T_顾客详细.金額,0))) 这个取得的@one_month 跟select @one_month = sum((isnull(dbo.T_顾客详细.金額,0))) 这样取出来的@one_month 的值是一样的,也就是说这样写跟之前的 受注日 <= #order.a and 受注日>= convert(date,'2012-10-01') 直接取得的值是一样的你是说,下面代码,和 select @one_month = sum((isnull(dbo.T_顾客详细.金額,0)))是一样的? 不会吧,下面的代码就是相当于一个累加的,你运行过了不while(@id>0) begindeclare @a datetime declare @one_month 一个月字段的数据类型select @one_month = 一个月,@a = a from #order where id= @idselect @one_month = @one_month + sum((isnull(dbo.T_顾客详细.金額,0))) from ( select dateadd(MONTH,-number-0,@a) 末,dateadd(day,1,dateadd(MONTH,-number-1,@a)) 前 from master..spt_values t where t.type = 'P' and datediff(month,'2012-10-01',@a)>= t.number ) t inner join dbo.T_顾客 on dbo.T_顾客.工厂区分='1' and 受注日 <= t.末 and 受注日>= t.前 group by T_顾客.顾客,dateadd(MONTH,-number-0,@a)update #order set 一个月=isnull(#order.一个月,0)+@one_month where id= @id set @id =@id-1end这个例子: declare @t intset @t = 10select @t = @t + v from ( select 1 v union all select 2 union all select 3 )tselect @t /* 16 */
用来计算 客户一个月的每个月(不同客户的结算时间不一样)销售额, 这是一个查询画面。
第一个while用来取出客户id 跟结算时间
第二个while就是根据结算时间跟客户id 把每个月数据插入到一张临时表
while(@id>0)
begin
select @a= #order.a,@cust = #order.顾客 from #order
where id=@id
set @期末=cast((substring(CONVERT(varchar(100), @a,112),7,2)) as int)
while(convert(date,@a) >=convert(date,'2012-10-01'))
begin
declare @末 datetime
declare @前 datetime
set @末 =CONVERT(date, @a)
set @前 =CONVERT(date, dateadd(month,datediff(month,0, @a)-1,@期末))
update #order
set 一个月=isnull(#order.一个月,0)+A.一个月
from (select T_顾客.顾客, sum((isnull(dbo.T_顾客详细.金額,0))) as 一个月
from dbo.T_顾客
where dbo.T_顾客.工厂区分='1' and 受注日 <=@末
and 受注日>=@前
group by T_顾客.顾客)A
where #order.顾客=@cust
set @a =dateadd(month,datediff(month,0, @a)-1,@期末-1)
end
set @id =@id-1
end因为以前的一些数据没办法改,所以 中间的那个update是为了求每个月的收入,然后再加起来
原因可以看看 http://bbs.csdn.net/topics/390675305 这个帖子
select 客户id,日期,sum(销售额)
from tb
where 条件
group by 客户id,日期
当然日期可能需要做些处理,你觉得这个思路在你系统中柯行不?
set 一个月=isnull(#order.一个月,0)+A.一个月
from
(
select T_顾客.顾客, sum((isnull(dbo.T_顾客详细.金額,0))) as 一个月
from dbo.T_顾客
where dbo.T_顾客.工厂区分='1' and 受注日 <=@末
and 受注日>=@前
group by T_顾客.顾客
)A
where #order.顾客=@cust
看不怎么懂你的意思可以说详细点吗呵呵,我把有些你原来的代码注释掉了,给适当简化了,不知道是不是这个意思:
while(@id>0)
begin/*
select @a= #order.a,@cust = #order.顾客 from #order
where id=@id
set @期末=cast((substring(CONVERT(varchar(100), @a,112),7,2)) as int)
while(convert(date,@a) >=convert(date,'2012-10-01'))
begin
declare @末 datetime
declare @前 datetime
set @末 =CONVERT(date, @a)
set @前 =CONVERT(date, dateadd(month,datediff(month,0, @a)-1,@期末))
*/update #order
set 一个月=isnull(#order.一个月,0)+A.一个月
from
(
select T_顾客.顾客,
sum((isnull(dbo.T_顾客详细.金額,0))) as 一个月 --此处计算的是从期初-期末的总和
from dbo.T_顾客
where dbo.T_顾客.工厂区分='1'
and 受注日 <= #order.a
and 受注日>= convert(date,'2012-10-01'))
group by T_顾客.顾客
)A
where id= @id/*
set @a =dateadd(month,datediff(month,0, @a)-1,@期末-1)
end
*/set @id =@id-1end
看不怎么懂你的意思可以说详细点吗呵呵,我把有些你原来的代码注释掉了,给适当简化了,不知道是不是这个意思:
while(@id>0)
begin/*
select @a= #order.a,@cust = #order.顾客 from #order
where id=@id
set @期末=cast((substring(CONVERT(varchar(100), @a,112),7,2)) as int)
while(convert(date,@a) >=convert(date,'2012-10-01'))
begin
declare @末 datetime
declare @前 datetime
set @末 =CONVERT(date, @a)
set @前 =CONVERT(date, dateadd(month,datediff(month,0, @a)-1,@期末))
*/update #order
set 一个月=isnull(#order.一个月,0)+A.一个月
from
(
select T_顾客.顾客,
sum((isnull(dbo.T_顾客详细.金額,0))) as 一个月 --此处计算的是从期初-期末的总和
from dbo.T_顾客
where dbo.T_顾客.工厂区分='1'
and 受注日 <= #order.a
and 受注日>= convert(date,'2012-10-01'))
group by T_顾客.顾客
)A
where id= @id/*
set @a =dateadd(month,datediff(month,0, @a)-1,@期末-1)
end
*/set @id =@id-1end谢谢啦,我以前是这样写的 但是会出现
7月出售了价格1250元的产品 税收是1.05
所以价格为 1250*1.05=1312.5 四舍五入 为1313
入金表 入金1313元8月出售了价格2250元的产品 税收是1.05
所以价格为 2250*1.05=2362.5 四舍五入 为2363
入金表 入金2363元9月出售了价格2250元的产品 税收是1.05
所以价格为 2250*1.05=2362.5 四舍五入 为2363
入金表 入金2363元当我查询的时候10月以前的收支平衡的时候
税后出售的金额的总和-收入的金额总和
即 税后出售的金额的总和 为 1312.5+2362.5+2632.5 =6037.5 四舍五入 6038
收入的金额总和为1313+2363+2363 =6039
这时 收支出现了-1的误差(其实本来每个月的税后出售额跟入金额都没错的)这样的错误。所以我才会写了个循环 让先算出每个月的出售额的合计,然后四舍五入。
再把每个月四舍五入的值给他加起来。
(因为之前的数据已经到了去年10月份,而且帐票也出了,
实在没办法改以前计算出售额的逻辑了,现在如果改以前的逻辑,那会有好多的数据不匹配)
看不怎么懂你的意思可以说详细点吗呵呵,我把有些你原来的代码注释掉了,给适当简化了,不知道是不是这个意思:
while(@id>0)
begin/*
select @a= #order.a,@cust = #order.顾客 from #order
where id=@id
set @期末=cast((substring(CONVERT(varchar(100), @a,112),7,2)) as int)
while(convert(date,@a) >=convert(date,'2012-10-01'))
begin
declare @末 datetime
declare @前 datetime
set @末 =CONVERT(date, @a)
set @前 =CONVERT(date, dateadd(month,datediff(month,0, @a)-1,@期末))
*/update #order
set 一个月=isnull(#order.一个月,0)+A.一个月
from
(
select T_顾客.顾客,
sum((isnull(dbo.T_顾客详细.金額,0))) as 一个月 --此处计算的是从期初-期末的总和
from dbo.T_顾客
where dbo.T_顾客.工厂区分='1'
and 受注日 <= #order.a
and 受注日>= convert(date,'2012-10-01'))
group by T_顾客.顾客
)A
where id= @id/*
set @a =dateadd(month,datediff(month,0, @a)-1,@期末-1)
end
*/set @id =@id-1end谢谢啦,我以前是这样写的 但是会出现
7月出售了价格1250元的产品 税收是1.05
所以价格为 1250*1.05=1312.5 四舍五入 为1313
入金表 入金1313元8月出售了价格2250元的产品 税收是1.05
所以价格为 2250*1.05=2362.5 四舍五入 为2363
入金表 入金2363元9月出售了价格2250元的产品 税收是1.05
所以价格为 2250*1.05=2362.5 四舍五入 为2363
入金表 入金2363元当我查询的时候10月以前的收支平衡的时候
税后出售的金额的总和-收入的金额总和
即 税后出售的金额的总和 为 1312.5+2362.5+2632.5 =6037.5 四舍五入 6038
收入的金额总和为1313+2363+2363 =6039
这时 收支出现了-1的误差(其实本来每个月的税后出售额跟入金额都没错的)这样的错误。所以我才会写了个循环 让先算出每个月的出售额的合计,然后四舍五入。
再把每个月四舍五入的值给他加起来。
(因为之前的数据已经到了去年10月份,而且帐票也出了,
实在没办法改以前计算出售额的逻辑了,现在如果改以前的逻辑,那会有好多的数据不匹配)哦,改了一下,你看看这样行吗:
while(@id>0)
begin/*
select @a= #order.a,@cust = #order.顾客 from #order
where id=@id
set @期末=cast((substring(CONVERT(varchar(100), @a,112),7,2)) as int)
while(convert(date,@a) >=convert(date,'2012-10-01'))
begin
declare @末 datetime
declare @前 datetime
set @末 =CONVERT(date, @a)
set @前 =CONVERT(date, dateadd(month,datediff(month,0, @a)-1,@期末))
*/declare @one_month 一个月字段的数据类型select @one_month = 一个月
from #order
where id= @idselect @one_month = @one_month + sum((isnull(dbo.T_顾客详细.金額,0)))
from #order
inner join dbo.T_顾客
on dbo.T_顾客.工厂区分='1'
and 受注日 <= #order.a
and 受注日>= convert(date,'2012-10-01')
group by T_顾客.顾客,convert(varchar(7),受注日,120)
update #order
set 一个月=isnull(#order.一个月,0)+@one_month
where id= @id/*
set @a =dateadd(month,datediff(month,0, @a)-1,@期末-1)
end
*/set @id =@id-1end
看不怎么懂你的意思可以说详细点吗呵呵,我把有些你原来的代码注释掉了,给适当简化了,不知道是不是这个意思:
while(@id>0)
begin/*
select @a= #order.a,@cust = #order.顾客 from #order
where id=@id
set @期末=cast((substring(CONVERT(varchar(100), @a,112),7,2)) as int)
while(convert(date,@a) >=convert(date,'2012-10-01'))
begin
declare @末 datetime
declare @前 datetime
set @末 =CONVERT(date, @a)
set @前 =CONVERT(date, dateadd(month,datediff(month,0, @a)-1,@期末))
*/update #order
set 一个月=isnull(#order.一个月,0)+A.一个月
from
(
select T_顾客.顾客,
sum((isnull(dbo.T_顾客详细.金額,0))) as 一个月 --此处计算的是从期初-期末的总和
from dbo.T_顾客
where dbo.T_顾客.工厂区分='1'
and 受注日 <= #order.a
and 受注日>= convert(date,'2012-10-01'))
group by T_顾客.顾客
)A
where id= @id/*
set @a =dateadd(month,datediff(month,0, @a)-1,@期末-1)
end
*/set @id =@id-1end谢谢啦,我以前是这样写的 但是会出现
7月出售了价格1250元的产品 税收是1.05
所以价格为 1250*1.05=1312.5 四舍五入 为1313
入金表 入金1313元8月出售了价格2250元的产品 税收是1.05
所以价格为 2250*1.05=2362.5 四舍五入 为2363
入金表 入金2363元9月出售了价格2250元的产品 税收是1.05
所以价格为 2250*1.05=2362.5 四舍五入 为2363
入金表 入金2363元当我查询的时候10月以前的收支平衡的时候
税后出售的金额的总和-收入的金额总和
即 税后出售的金额的总和 为 1312.5+2362.5+2632.5 =6037.5 四舍五入 6038
收入的金额总和为1313+2363+2363 =6039
这时 收支出现了-1的误差(其实本来每个月的税后出售额跟入金额都没错的)这样的错误。所以我才会写了个循环 让先算出每个月的出售额的合计,然后四舍五入。
再把每个月四舍五入的值给他加起来。
(因为之前的数据已经到了去年10月份,而且帐票也出了,
实在没办法改以前计算出售额的逻辑了,现在如果改以前的逻辑,那会有好多的数据不匹配)这种小数点的问题一般是最后统计才四舍五入好,而且最好精度要高
的时候 受注日的判断是1个月一个月的
例如 受注日 >=2013-09-21 and 受注日 <=2013-10-20这样的
也就是说 那个order.a 在我这边 仅仅只是为了判断得出20这个数字
的时候 受注日的判断是1个月一个月的
例如 受注日 >=2013-09-21 and 受注日 <=2013-10-20这样的
也就是说 那个order.a 在我这边 仅仅只是为了判断得出20这个数字
哦,那你的统计规则是什么,就是这个:select T_顾客.顾客, sum((isnull(dbo.T_顾客详细.金額,0))) as 一个月
from dbo.T_顾客
where dbo.T_顾客.工厂区分='1' and 受注日 <=@末
and 受注日>=@前
group by T_顾客.顾客计算的是每个客户的,金额的总和,这个受注日是日期吗 还是其他的
的时候 受注日的判断是1个月一个月的
例如 受注日 >=2013-09-21 and 受注日 <=2013-10-20这样的
也就是说 那个order.a 在我这边 仅仅只是为了判断得出20这个数字
哦,那你的统计规则是什么,就是这个:select T_顾客.顾客, sum((isnull(dbo.T_顾客详细.金額,0))) as 一个月
from dbo.T_顾客
where dbo.T_顾客.工厂区分='1' and 受注日 <=@末
and 受注日>=@前
group by T_顾客.顾客计算的是每个客户的,金额的总和,这个受注日是日期吗 还是其他的受注日是日期 但是@末跟@前 是重新计算过的,是一个月的区间
的时候 受注日的判断是1个月一个月的
例如 受注日 >=2013-09-21 and 受注日 <=2013-10-20这样的
也就是说 那个order.a 在我这边 仅仅只是为了判断得出20这个数字
哦,那你的统计规则是什么,就是这个:select T_顾客.顾客, sum((isnull(dbo.T_顾客详细.金額,0))) as 一个月
from dbo.T_顾客
where dbo.T_顾客.工厂区分='1' and 受注日 <=@末
and 受注日>=@前
group by T_顾客.顾客计算的是每个客户的,金额的总和,这个受注日是日期吗 还是其他的受注日是日期 但是@末跟@前 是重新计算过的,是一个月的区间是个怎么样的区间呢,一个月的几号-几号
from tb
where 条件
group by 客户id,日期昨天的这个我后面试了一下,
客户id 和日期是在一张表里面
而销售额 是在另外一张表
所以我要一个while语句先把顾客表里面的 客户id,日期给取出来吧(因为日期需要处理)
然后在处理日期区间的时候,也许是因为我之前思维被固定了,感觉还是只能靠while语句来处理。
的时候 受注日的判断是1个月一个月的
例如 受注日 >=2013-09-21 and 受注日 <=2013-10-20这样的
也就是说 那个order.a 在我这边 仅仅只是为了判断得出20这个数字
哦,那你的统计规则是什么,就是这个:select T_顾客.顾客, sum((isnull(dbo.T_顾客详细.金額,0))) as 一个月
from dbo.T_顾客
where dbo.T_顾客.工厂区分='1' and 受注日 <=@末
and 受注日>=@前
group by T_顾客.顾客计算的是每个客户的,金额的总和,这个受注日是日期吗 还是其他的受注日是日期 但是@末跟@前 是重新计算过的,是一个月的区间是个怎么样的区间呢,一个月的几号-几号这个区间会变的哦,不同客户 不一样
from tb
where 条件
group by 客户id,日期昨天的这个我后面试了一下,
客户id 和日期是在一张表里面
而销售额 是在另外一张表
所以我要一个while语句先把顾客表里面的 客户id,日期给取出来吧(因为日期需要处理)
然后在处理日期区间的时候,也许是因为我之前思维被固定了,感觉还是只能靠while语句来处理。你再关联另外一张
select @a= #order.a,@cust = #order.顾客 from #order
where id=@idset @期末=cast((substring(CONVERT(varchar(100), @a,112),7,2)) as int)根据这个#order.a 临时表的然后推算回来
from tb
where 条件
group by 客户id,日期昨天的这个我后面试了一下,
客户id 和日期是在一张表里面
而销售额 是在另外一张表
所以我要一个while语句先把顾客表里面的 客户id,日期给取出来吧(因为日期需要处理)
然后在处理日期区间的时候,也许是因为我之前思维被固定了,感觉还是只能靠while语句来处理。你再关联另外一张但是日期处理要怎么办,我2个while 第一个是为了区分顾客跟取出日期
第二个是根据日期推算每个月的区间while(@id>0)
begin
select @a= #order.a,@cust = #order.顾客 from #order
where id=@id
set @期末=cast((substring(CONVERT(varchar(100), @a,112),7,2)) as int)
while(convert(date,@a) >=convert(date,'2012-10-01'))
begin
declare @末 datetime
declare @前 datetime
set @末 =CONVERT(date, @a)
set @前 =CONVERT(date, dateadd(month,datediff(month,0, @a)-1,@期末))
update #order
set 一个月=isnull(#order.一个月,0)+A.一个月
from (select T_顾客.顾客, sum((isnull(dbo.T_顾客详细.金額,0))) as 一个月
from dbo.T_顾客
where dbo.T_顾客.工厂区分='1' and 受注日 <=@末
and 受注日>=@前
group by T_顾客.顾客)A
where #order.顾客=@custset @a =dateadd(month,datediff(month,0, @a)-1,@期末-1)
end
set @id =@id-1
end
set @前 =CONVERT(date, dateadd(month,datediff(month,0, @a)-1,@期末))转为日期?这些是什么意思
@期末 是每个月的截至日期(根据这个日期来往前面推算出客户一个月的计算区间)
例如 有的客户一个月是按10月21号到11月20号为一个区间,而有的是10月1号到 10月31号为一个区间
@期末就是求出 他是21号还是1号
@前 就是起始日期
@末 结束日期嗯,我明白,
但是对于某一个确定的客户,他的区间应该是固定的,比如想你说的,是按10月21号到11月20号,那么往前推,就是9月21到10月20 对吧。而且,不需要太复杂,比如 10月21号到11月20号,对这个客户,我们可以计算为 11月,9月21到10月20就是9月,然后group by 一下,就可以计算出 9月和10月,这个客户分别的金额了。
@期末 是每个月的截至日期(根据这个日期来往前面推算出客户一个月的计算区间)
例如 有的客户一个月是按10月21号到11月20号为一个区间,而有的是10月1号到 10月31号为一个区间
@期末就是求出 他是21号还是1号
@前 就是起始日期
@末 结束日期嗯,我明白,
但是对于某一个确定的客户,他的区间应该是固定的,比如想你说的,是按10月21号到11月20号,那么往前推,就是9月21到10月20 对吧。而且,不需要太复杂,比如 10月21号到11月20号,对这个客户,我们可以计算为 11月,9月21到10月20就是9月,然后group by 一下,就可以计算出 9月和10月,这个客户分别的金额了。对就是这样,但是这个group by要怎么弄呢
@期末 是每个月的截至日期(根据这个日期来往前面推算出客户一个月的计算区间)
例如 有的客户一个月是按10月21号到11月20号为一个区间,而有的是10月1号到 10月31号为一个区间
@期末就是求出 他是21号还是1号
@前 就是起始日期
@末 结束日期嗯,我明白,
但是对于某一个确定的客户,他的区间应该是固定的,比如想你说的,是按10月21号到11月20号,那么往前推,就是9月21到10月20 对吧。而且,不需要太复杂,比如 10月21号到11月20号,对这个客户,我们可以计算为 11月,9月21到10月20就是9月,然后group by 一下,就可以计算出 9月和10月,这个客户分别的金额了。对就是这样,但是这个group by要怎么弄呢这个:
while(@id>0)
begindeclare @a datetime
declare @one_month 一个月字段的数据类型select @one_month = 一个月,@a = a
from #order
where id= @idselect @one_month = @one_month + sum((isnull(dbo.T_顾客详细.金額,0)))
from
(
select dateadd(MONTH,-number-0,@a) 末,dateadd(MONTH,-number-1,@a) 前
from master..spt_values t
where t.type = 'P' and datediff(month,'2012-10-01',@a)>= t.number
) t
inner join dbo.T_顾客
on dbo.T_顾客.工厂区分='1'
and 受注日 <= t.末
and 受注日>= t.前
group by T_顾客.顾客,update #order
set 一个月=isnull(#order.一个月,0)+@one_month
where id= @id
set @id =@id-1end
while(@id>0)
begindeclare @a datetime
declare @one_month 一个月字段的数据类型select @one_month = 一个月,@a = a
from #order
where id= @idselect @one_month = @one_month + sum((isnull(dbo.T_顾客详细.金額,0)))
from
(
select dateadd(MONTH,-number-0,@a) 末,dateadd(MONTH,-number-1,@a) 前,
from master..spt_values t
where t.type = 'P' and datediff(month,'2012-10-01',@a)>= t.number
) t
inner join dbo.T_顾客
on dbo.T_顾客.工厂区分='1'
and 受注日 <= t.末
and 受注日>= t.前
group by T_顾客.顾客,dateadd(MONTH,-number-0,@a)update #order
set 一个月=isnull(#order.一个月,0)+@one_month
where id= @id
set @id =@id-1end
set @a = '2013-10-28'
--自动计算月份的末、前
select dateadd(MONTH,-number-0,@a) 末,dateadd(MONTH,-number-1,@a) 前
from master..spt_values t
where t.type = 'P' and datediff(month,'2012-10-01',@a)>= t.number
/*
末 前
2013-10-28 00:00:00.000 2013-09-28 00:00:00.000
2013-09-28 00:00:00.000 2013-08-28 00:00:00.000
2013-08-28 00:00:00.000 2013-07-28 00:00:00.000
2013-07-28 00:00:00.000 2013-06-28 00:00:00.000
2013-06-28 00:00:00.000 2013-05-28 00:00:00.000
2013-05-28 00:00:00.000 2013-04-28 00:00:00.000
2013-04-28 00:00:00.000 2013-03-28 00:00:00.000
2013-03-28 00:00:00.000 2013-02-28 00:00:00.000
2013-02-28 00:00:00.000 2013-01-28 00:00:00.000
2013-01-28 00:00:00.000 2012-12-28 00:00:00.000
2012-12-28 00:00:00.000 2012-11-28 00:00:00.000
2012-11-28 00:00:00.000 2012-10-28 00:00:00.000
2012-10-28 00:00:00.000 2012-09-28 00:00:00.000
*/
begindeclare @a datetime
declare @one_month 一个月字段的数据类型select @one_month = 一个月,@a = a
from #order
where id= @idselect @one_month = @one_month + sum((isnull(dbo.T_顾客详细.金額,0)))
from
(
select dateadd(MONTH,-number-0,@a) 末,dateadd(day,1,dateadd(MONTH,-number-1,@a)) 前
from master..spt_values t
where t.type = 'P' and datediff(month,'2012-10-01',@a)>= t.number
) t
inner join dbo.T_顾客
on dbo.T_顾客.工厂区分='1'
and 受注日 <= t.末
and 受注日>= t.前
group by T_顾客.顾客,dateadd(MONTH,-number-0,@a)update #order
set 一个月=isnull(#order.一个月,0)+@one_month
where id= @id
set @id =@id-1enddeclare @a datetime--如果@a 为'2013-10-28'
set @a = '2013-10-28'
--自动计算月份的末、前
select dateadd(MONTH,-number-0,@a) 末,dateadd(day,1,dateadd(MONTH,-number-1,@a)) 前
from master..spt_values t
where t.type = 'P' and datediff(month,'2012-10-01',@a)>= t.number
/*
末 前
2013-10-28 00:00:00.000 2013-09-29 00:00:00.000
2013-09-28 00:00:00.000 2013-08-29 00:00:00.000
2013-08-28 00:00:00.000 2013-07-29 00:00:00.000
2013-07-28 00:00:00.000 2013-06-29 00:00:00.000
2013-06-28 00:00:00.000 2013-05-29 00:00:00.000
2013-05-28 00:00:00.000 2013-04-29 00:00:00.000
2013-04-28 00:00:00.000 2013-03-29 00:00:00.000
2013-03-28 00:00:00.000 2013-03-01 00:00:00.000
2013-02-28 00:00:00.000 2013-01-29 00:00:00.000
2013-01-28 00:00:00.000 2012-12-29 00:00:00.000
2012-12-28 00:00:00.000 2012-11-29 00:00:00.000
2012-11-28 00:00:00.000 2012-10-29 00:00:00.000
2012-10-28 00:00:00.000 2012-09-29 00:00:00.000
*/
DROP FUNCTION Fn_ISDATE
GO
CREATE FUNCTION [Fn_ISDATE]
(@i_tjrq INTEGER)
RETURNS INTEGER--返回0为正确,1为错误。
AS
BEGIN
DECLARE @i_year INTEGER;--定义变量存储输入参数的年部分
DECLARE @i_month INTEGER;--定义变量存储输入参数的月部分
DECLARE @i_day INTEGER;--定义变量存储输入参数的日部分
-- DECLARE @d_tjrq DATETIME;
--对日期长度判断
IF @i_tjrq<10000000 or @i_tjrq>99999999 or @i_tjrq IS null
BEGIN
RETURN 1;
END
ELSE
SET @i_year=convert(INT,(substring(rtrim(convert(CHAR,(@i_tjrq))),1,4)));
SET @i_month=convert(INT,(substring(rtrim(convert(CHAR,(@i_tjrq))),5,2)));
SET @i_day=convert(INT,(substring(rtrim(convert(CHAR,(@i_tjrq))),7,2)));
--对月判断,必须在1-12范围内
IF @i_month not between 1 and 12
BEGIN
RETURN 1;
END
--对日判断,1,3,5,7,8,10,12月最大日为31,4,6,9,11月最大日为30,2月若为闰年则为29,其它年则为28.
IF @i_day between 1 and 31
BEGIN
IF @i_day=31 and @i_month not in (1,3,5,7,8,10,12)
BEGIN
RETURN 1;
END
IF @i_month=2
BEGIN
IF (((@i_year%4)=0 and (@i_year0)<>0) or (@i_year%400)=0)
BEGIN
--若为闰年,则最大日为29
IF @i_day>29
BEGIN
RETURN 1;
END
END
ELSE
--若不为闰年,则最大日为28
IF @i_day>28
BEGIN
RETURN 1;
END
END
END
ELSE
RETURN 1;
--若以上情况都不满足,则认为时间合法,返回0
RETURN 0;
END
/*
select pas.Fn_ISDATE(20100119)
*/
go
go
IF OBJECT_ID('Fn_Change_Days','FN') IS NOT NULL
DROP FUNCTION Fn_Change_Days
GO
CREATE FUNCTION [Fn_Change_Days](
@i_day INTEGER,
@i_number INTEGER)
RETURNS INTEGER
AS
BEGIN
--modify by huangzj 20100119 添加判断输入日期是否符合要求
DECLARE @DAYS INTEGER
SET @DAYS=(SELECT dbo.Fn_ISDATE(@i_day))--调用检查日期函数
DECLARE @RESULT INTEGER--定义变量储存输出结果
IF @DAYS=0--若输入日期合法,返回计算结果
BEGIN
SET @RESULT=( convert(INTEGER,convert(CHAR(8),dateadd(d,@i_number,convert(DATETIME,convert(CHAR(8),@i_day))),112)))
END
ELSE--输入日期不合法,返回@i_day的值以示不计算不成功
BEGIN
SET @RESULT= @I_DAY
END
RETURN @RESULT--返回结果
END
/*
select pas.Fn_Change_Days(20100119,-5)
*/
go
IF OBJECT_ID('Fn_DATE','FN') IS NOT NULL
DROP FUNCTION Fn_DATE
GO
CREATE FUNCTION [Fn_DATE]
(@i_tjrq INTEGER)
RETURNS datetime
AS
BEGIN
DECLARE @d_tjrq DATETIME--定义变量储存要输出的结果。
--对日期长度判断
IF @i_tjrq<10000000 or @i_tjrq>99999999 or @i_tjrq IS null
BEGIN
RETURN 1;
END
ELSE
SET @d_tjrq=convert(DATETIME,substring(convert(CHAR,(@i_tjrq)),1,4)+'/'+substring(convert(CHAR,(@i_tjrq)),5,2)+'/'+substring(convert(CHAR,(@i_tjrq)),7,2))
RETURN @d_tjrq;
END
/*
select pas.Fn_DATE(20100119)
*/
go
IF OBJECT_ID('Sys_SJW','u') IS NOT NULL
DROP TABLE Sys_SJW
GO
CREATE TABLE [Sys_SJW](
[tjrq] [int] NOT NULL,
[yc] [int] NULL,
[sr] [int] NULL,
[sym] [int] NULL,
[xc] [int] NULL,
[xm] [int] NULL,
[jc] [int] NULL,
[jm] [int] NULL,
[nc] [int] NULL,
[nm] [int] NULL,
[ym] [int] NULL,
PRIMARY KEY CLUSTERED
(
[tjrq] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
go
IF OBJECT_ID('Sp_Init_SJWB','p') IS NOT NULL
DROP PROC Sp_Init_SJWB
GO
CREATE PROCEDURE [Sp_Init_SJWB]
@i_tjrq INTEGER
AS
SET NOCOUNT ON--不返回影响的行数
--定义变量
DECLARE @i_yc INTEGER --月初
DECLARE @i_ym INTEGER --月末
DECLARE @i_sr INTEGER --上日
DECLARE @i_sym INTEGER --上月末
DECLARE @i_xc INTEGER --旬初
DECLARE @i_xm INTEGER --旬末
DECLARE @i_jc INTEGER --季初
DECLARE @i_jm INTEGER --季末
DECLARE @i_nc INTEGER --年初
DECLARE @i_nm INTEGER --年末
DECLARE @i_jm2 datetime --季末
-- DECLARE @i_snm INTEGER --上年末
DECLARE @d_tjrq datetime--时间类型日期
--EXEC sp_Pas_xtrz '0','Sp_Init_SJWB','时间维表数据生成开始'
SET @d_tjrq=dbo.Fn_DATE(@i_tjrq)
--月初
SET @i_yc=convert(INTEGER,substring(convert(CHAR,(@i_tjrq)),1,4)+substring(convert(CHAR,(@i_tjrq)),5,2)+'01')
--月末
SET @i_ym=convert(INTEGER,substring(convert(CHAR,(@i_tjrq)),1,4)+substring(convert(CHAR,(@i_tjrq)),5,2)+right('00'+convert(VARCHAR(2),day(dateadd(month,1,dbo.Fn_DATE(@i_yc))-1)),2))
--上日
SET @i_sr=convert(INTEGER,dbo.Fn_Change_Days(@i_tjrq,-1))
--上月末
SET @i_sym=convert(INTEGER,dbo.Fn_Change_Days(@i_yc,-1))
--旬初旬末
--1至10号为上旬,11至20号为中旬,21至月底为下旬
IF convert(INTEGER,substring(convert(CHAR,(@i_tjrq)),7,2))<=10 --上旬
BEGIN
SET @i_xc=@i_yc--旬初
SET @i_xm=convert(INTEGER,substring(convert(CHAR,(@i_tjrq)),1,4)+substring(convert(CHAR,(@i_tjrq)),5,2)+'10')--旬末
END
ELSE IF convert(INTEGER,substring(convert(CHAR,(@i_tjrq)),7,2))>10 and convert(INTEGER,substring(convert(CHAR,(@i_tjrq)),7,2))<=20 --中旬
BEGIN
SET @i_xc=convert(INTEGER,substring(convert(CHAR,(@i_tjrq)),1,4)+substring(convert(CHAR,(@i_tjrq)),5,2)+'11')--旬初
SET @i_xm=convert(INTEGER,substring(convert(CHAR,(@i_tjrq)),1,4)+substring(convert(CHAR,(@i_tjrq)),5,2)+'20')--旬末
END
ELSE --下旬
BEGIN
SET @i_xc=convert(INTEGER,substring(convert(CHAR,(@i_tjrq)),1,4)+substring(convert(CHAR,(@i_tjrq)),5,2)+'21')--旬初
SET @i_xm=@i_ym--旬末
END
--季初季末
SET @i_jc=convert(INTEGER,substring(convert(CHAR,(@i_tjrq)),1,4)+right('00'+(convert(VARCHAR(2),((((convert(INTEGER,substring(convert(CHAR,(@i_tjrq)),5,2))-1) / 3) * 3) + 1))) ,2)+ '01')
SET @i_jm2=DATEADD(DAY,-1,DATEADD(month,3,dbo.Fn_DATE(@i_jc)))
SET @i_jm=rtrim(convert(char(4),year(@i_jm2)))+rtrim(convert(char(2),right('00'+convert(VARCHAR(2),month(@i_jm2)),2)))+rtrim(convert(char(2),right('00'+convert(VARCHAR(2),day(@i_jm2)),2)))
--年初
SET @i_nc=substring(convert(CHAR,(@i_tjrq)),1,4)+'0101'
--年末
SET @i_nm=substring(convert(CHAR,(@i_tjrq)),1,4)+'1231'
-- --上年末
IF exists(select top 1 1 from Sys_SJW where tjrq=@i_tjrq)
delete from Sys_SJW where tjrq=@i_tjrq
ELSE
insert into Sys_SJW([tjrq], [yc], [sr], [sym], [xc], [xm], [jc], [jm], [nc], [nm], [ym])
values(@i_tjrq,@i_yc,@i_sr,@i_sym,@i_xc,@i_xm,@i_jc,@i_jm,@i_nc,@i_nm,@i_ym)
--EXEC sp_Pas_xtrz '0','Sp_Init_SJWB','时间维表数据生成结束'
/*
exec Sp_Init_SJWB 20090119
select * from xtb_xtrz order by sjdh desc
*/
go
IF OBJECT_ID('Sp_Init_SJWB_While','p') IS NOT NULL
DROP PROC Sp_Init_SJWB_While
GO
CREATE PROCEDURE [Sp_Init_SJWB_While]
@i_tjrq INTEGER
AS
BEGIN
DECLARE @i_jsrq INTEGER--年末日期
DECLARE @i_ksrq INTEGER --渐增的日期
SET @i_jsrq=convert(CHAR(4),substring(convert(CHAR,(@i_tjrq)),1,4))+'1231'
IF substring(convert(CHAR,(@i_tjrq)),5,4)='0101'
BEGIN
--EXEC sp_Pas_xtrz '0','Sp_Init_SJWB_while','时间维表数据批量生成程序数据生成开始'
DELETE FROM Sys_SJW WHERE tjrq>=@i_tjrq
SET @i_ksrq=@i_tjrq
WHILE (@i_ksrq<=@i_jsrq)
BEGIN
EXEC Sp_Init_SJWB @i_ksrq
SET @i_ksrq=convert(INTEGER,dbo.Fn_Change_Days(@i_ksrq,1))
END
--EXEC sp_Pas_xtrz '0','Sp_Init_SJWB_while','时间维表数据批量生成程序数据生成结束'
END
END
/*
exec Sp_Init_SJWB_While 20110101
select * from Sys_SJW
*/
不是固定的 你试试,上面的代码,应该是可以的
不行,那个看起来好像是 计算月份的末、前然后加起来
但其实select @one_month = @one_month + sum((isnull(dbo.T_顾客详细.金額,0)))
这个取得的@one_month
跟select @one_month = sum((isnull(dbo.T_顾客详细.金額,0)))
这样取出来的@one_month 的值是一样的,也就是说这样写跟之前的
受注日 <= #order.a and 受注日>= convert(date,'2012-10-01') 直接取得的值是一样的
不是固定的 你试试,上面的代码,应该是可以的
不行,那个看起来好像是 计算月份的末、前然后加起来
但其实select @one_month = @one_month + sum((isnull(dbo.T_顾客详细.金額,0)))
这个取得的@one_month
跟select @one_month = sum((isnull(dbo.T_顾客详细.金額,0)))
这样取出来的@one_month 的值是一样的,也就是说这样写跟之前的
受注日 <= #order.a and 受注日>= convert(date,'2012-10-01') 直接取得的值是一样的你是说,下面代码,和 select @one_month = sum((isnull(dbo.T_顾客详细.金額,0)))是一样的?
不会吧,下面的代码就是相当于一个累加的,你运行过了不while(@id>0)
begindeclare @a datetime
declare @one_month 一个月字段的数据类型select @one_month = 一个月,@a = a
from #order
where id= @idselect @one_month = @one_month + sum((isnull(dbo.T_顾客详细.金額,0)))
from
(
select dateadd(MONTH,-number-0,@a) 末,dateadd(day,1,dateadd(MONTH,-number-1,@a)) 前
from master..spt_values t
where t.type = 'P' and datediff(month,'2012-10-01',@a)>= t.number
) t
inner join dbo.T_顾客
on dbo.T_顾客.工厂区分='1'
and 受注日 <= t.末
and 受注日>= t.前
group by T_顾客.顾客,dateadd(MONTH,-number-0,@a)update #order
set 一个月=isnull(#order.一个月,0)+@one_month
where id= @id
set @id =@id-1end这个例子:
declare @t intset @t = 10select @t = @t + v
from
(
select 1 v union all
select 2 union all
select 3
)tselect @t
/*
16
*/