我想知道根据输入的日期判断它是属于哪个月的哪一旬,大月的下旬有11天,上,中旬为10天,得出上个月,与上年的,好麻烦 我写了一个,太长,高手给指正一下,或给个思路也行呀 ALTER PROCEDURE DBO.asp_GetQueryAvgPriceAsTendays ( @timens char(10), @timene char(10), @timels char(10), @timele char(10), @variety char(2000) ) ASDECLARE @select char(8000) set @select = ' select rtrim(vname) as variety_name, --品种名称 rtrim(pgname) as grade_name, --等级名称
isnull(case when (((mavg-lastw)/lastw*100)*100+0.5)/100=0 then ''平'' when (((mavg-lastw)/lastw*100)*100+0.5)/100>0 then ''+''+rtrim( cast( cast((((mavg-lastw)/lastw*100)*100+0.5)/100 as decimal(8,2)) as char(10) ) )+''%'' when (((mavg-lastw)/lastw*100)*100+0.5)/100<0 then rtrim( cast( cast((((mavg-lastw)/lastw*100)*100+0.5)/100 as decimal(8,2)) as char(10) ) )+''%'' end ,''----'')as compare,--本期与上期相比年% cast( mavg as decimal(8,1))as nowavg, --本期平均 isnull(case when (((mavg-lastq)/lastq*100)*100+0.5)/100=0 then ''平'' when (((mavg-lastq)/lastq*100)*100+0.5)/100>0 then ''+''+rtrim( cast( cast((((mavg-lastq)/lastq*100)*100+0.5)/100 as decimal(8,2)) as char(10) ) )+''%'' when (((mavg-lastq)/lastq*100)*100+0.5)/100<0 then rtrim( cast( cast((((mavg-lastq)/lastq*100)*100+0.5)/100 as decimal(8,2)) as char(10) ) )+''%'' end ,''----'')as lastcompare --本期与上期比 from (--本年的平均 select q.variety_id,
v.[name] as vname, pg.[name] pgname, q.grade_id,
avg(q.price) as mavg, --上年同旬的平均价格 ( select mavgt from ( select qq.variety_id, qq.grade_id, avg(qq.price) as mavgt
from p_Quote qq
where qq.variety_id in ('+rtrim(@variety)+') and qq.price_date>=dateadd(year,-1,'''+@timens+''') and qq.price_date<=dateadd(year,-1,''' +@timene+''') and q.variety_id=qq.variety_id and q.grade_id=qq.grade_id and qq.state=1 group by qq.variety_id, qq.grade_id
) as lastyear ) AS lastw, --上年同旬的到这结束--上旬的平均价格 ( select mavgt from ( select qq.variety_id, qq.grade_id, avg(qq.price) as mavgt
from p_Quote qq
where qq.variety_id in ('+rtrim(@variety)+') and qq.price_date>='''+@timels+''' and qq.price_date<=''' +@timele+''' and q.variety_id=qq.variety_id and q.grade_id=qq.grade_id and qq.state=1 group by qq.variety_id, qq.grade_id
) as lastyear ) AS lastq --上旬的到这结束from p_Quote q inner join p_variety v on v.pkid = q.variety_id --品种inner join p_grade pg on pg.variety_id=q.variety_id and pg.grade_id = q.grade_id --等级where q.variety_id in ('+rtrim(@variety)+') and q.price_date>='''+@timens+''' and q.price_date<=''' + @timene +''' and q.state=1 group by
我写了一个,太长,高手给指正一下,或给个思路也行呀
ALTER PROCEDURE DBO.asp_GetQueryAvgPriceAsTendays
(
@timens char(10),
@timene char(10),
@timels char(10),
@timele char(10),
@variety char(2000)
)
ASDECLARE @select char(8000)
set @select = '
select rtrim(vname) as variety_name, --品种名称
rtrim(pgname) as grade_name, --等级名称
isnull(case
when (((mavg-lastw)/lastw*100)*100+0.5)/100=0 then ''平''
when (((mavg-lastw)/lastw*100)*100+0.5)/100>0 then ''+''+rtrim( cast(
cast((((mavg-lastw)/lastw*100)*100+0.5)/100 as decimal(8,2))
as char(10) ) )+''%''
when (((mavg-lastw)/lastw*100)*100+0.5)/100<0 then rtrim( cast(
cast((((mavg-lastw)/lastw*100)*100+0.5)/100 as decimal(8,2))
as char(10) ) )+''%''
end ,''----'')as compare,--本期与上期相比年% cast( mavg as decimal(8,1))as nowavg, --本期平均
isnull(case
when (((mavg-lastq)/lastq*100)*100+0.5)/100=0 then ''平''
when (((mavg-lastq)/lastq*100)*100+0.5)/100>0 then ''+''+rtrim( cast(
cast((((mavg-lastq)/lastq*100)*100+0.5)/100 as decimal(8,2))
as char(10) ) )+''%''
when (((mavg-lastq)/lastq*100)*100+0.5)/100<0 then rtrim( cast(
cast((((mavg-lastq)/lastq*100)*100+0.5)/100 as decimal(8,2))
as char(10) ) )+''%''
end ,''----'')as lastcompare
--本期与上期比
from
(--本年的平均
select
q.variety_id,
v.[name] as vname,
pg.[name] pgname,
q.grade_id,
avg(q.price) as mavg,
--上年同旬的平均价格 (
select mavgt
from
(
select qq.variety_id,
qq.grade_id,
avg(qq.price) as mavgt
from p_Quote qq
where qq.variety_id in ('+rtrim(@variety)+') and
qq.price_date>=dateadd(year,-1,'''+@timens+''') and
qq.price_date<=dateadd(year,-1,''' +@timene+''') and
q.variety_id=qq.variety_id and
q.grade_id=qq.grade_id and
qq.state=1
group by
qq.variety_id,
qq.grade_id
) as lastyear )
AS lastw, --上年同旬的到这结束--上旬的平均价格 (
select mavgt
from
(
select qq.variety_id,
qq.grade_id,
avg(qq.price) as mavgt
from p_Quote qq
where qq.variety_id in ('+rtrim(@variety)+') and
qq.price_date>='''+@timels+''' and
qq.price_date<=''' +@timele+''' and
q.variety_id=qq.variety_id and
q.grade_id=qq.grade_id and
qq.state=1
group by
qq.variety_id,
qq.grade_id
) as lastyear )
AS lastq --上旬的到这结束from p_Quote q inner join p_variety v on v.pkid = q.variety_id --品种inner join p_grade pg on pg.variety_id=q.variety_id and pg.grade_id = q.grade_id --等级where q.variety_id in ('+rtrim(@variety)+') and
q.price_date>='''+@timens+''' and
q.price_date<=''' + @timene +''' and
q.state=1
group by
q.variety_id,
q.grade_id,
v.[name],
pg.[name])as cc
'
exec(@select)
return