非常非常郁闷的问题.跪求!!

[wyd531]

	select sum(po_cost)as cost,month(order_date)as month
	from purview
	where year(order_date)='2008'
	group by month(order_date)

cost month
---------------------------------------- -----------
5528413.271285 7
5694813.265629 6
2672584.647310 5
4082991.366537 4
222520.177494 11
3173254.189964 3
3969681.967661 10
2571155.117312 2
3857142.690119 9
5692678.669050 1
4357953.774225 8

（所影响的行数为 11 行）

但是现在,上面要求这样的条件,
2008年9月之前就是汇总整个月的数据.
2008年9月汇总的是9-1~9-25的汇总
2008年9月以后的是从上个月26到本月25(如果是10月就是9-26~10-25的汇总)
请问这样变态的要求应该怎样做呀.看来这次是死定了.晕.

[wyd531]

我一位很同事告诉我一个方法.我觉的很强大,共享出来让大家看看.不过还是谢谢各位

	select sum(po_cost*cur_rate)as dd,dbo.ToRealMM(order_date) as mm
	from purview left join arap_currency on curr_code=cur_code
	where year(order_date)=@year
	group by dbo.ToRealMM(order_date)
--ToRealMM函数
CREATE function ToRealMM(@TheDateTime datetime) returns integer
as
begin
	if year(@TheDateTime)<=2008 and month(@TheDateTime)<9
	return(month(@TheDateTime))

	if year(@TheDateTime)=2008 and month(@TheDateTime)=9 and day(@TheDateTime) between 1 and 25	
	return(month(@TheDateTime))

	if(day(@TheDateTime)<=25)
	return(month(@TheDateTime))

	return(month(@TheDateTime)+1)
end

[昵称被占用了]

select sum(po_cost)as cost,case when month(order_date) <9 then month(order_date)
   else
     case when day(order_date)<=25 then month(order_date)
     else month(order_date)+1
     end
   end  as month
    from purview
    where order_date>='2008-1-1'
    and order_date<'2008-12-26'
    group by case when month(order_date) <9 then month(order_date)
   else
     case when day(order_date)<=25 then month(order_date)
     else month(order_date)+1
     end
   end

[flairsky]

也不是不能做

结合case，month，day函数就可以，没那么难

select sum(po_cost)as cost,
       case when month(order_date) <9 then month(order_date) 
         else  
         case when day(order_date)<=25 then month(order_date) 
           else 
           month(order_date)+1  
           end  
         end as month  
from purview 
where order_date>='2008-1-1'  and order_date<'2008-12-26'  
group by 
case when month(order_date) <9 then month(order_date)
else  
  case when day(order_date)<=25 then month(order_date) 
    else month(order_date)+1  
  end  
end

[snowkk]

学习

[wonture]

[Quote=引用 1 楼 Haiwer 的回复:]
SQL codeselect sum(po_cost)as cost,case when month(order_date) <9 then month(order_date)
else
case when day(order_date)<=25 then month(order_date)
else month(order_date)+1
end
end as month
from purview
where order_date>='2008-1-1'
and order_date<'2008-12-26'
group by case when month(order_date) <9 then month(order_date)
else
case when day(…
[/Quote]
很牛很强大，学习啊！

[昵称被占用了]

这个函数逻辑有问题

select dbo.ToRealMM('2007-12-26')

-----------
13

(1 行受影响)

select dbo.ToRealMM('2008-12-26')

-----------
13

(1 行受影响)

[昵称被占用了]

建议函数如下改

alter function ToRealMM(@TheDateTime datetime) returns integer
as
begin
    if @TheDateTime<='2008-09-25'
    return(month(@TheDateTime))

    if(day(@TheDateTime)<=25)
    return(month(@TheDateTime))

    if(month(@TheDateTime)=12)
    return(1)

    return(month(@TheDateTime)+1)
end