22,209
社区成员
发帖
与我相关
我的任务
分享看看这代码,脑壳痛!
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
--查询前两年的-五年之内的
-- =============================================
ALTER PROCEDURE [dbo].[proce_Find_GetBienniaMFG_cost]
--传入参数
@year int,
--上一年的
@toOneavgvalue DECIMAL(10, 1) output,
--当前年的
@nonceAvgvalue DECIMAL(10, 1) output
AS
BEGIN
select @toOneavgvalue= CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1)) from dbo.MFG_cost where Vintage=@year-1 and [Firm-empty]='Budget'
select @nonceAvgvalue=CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1)) from dbo.MFG_cost where Vintage=@year and [Firm-empty]='Budget'
select * into #SumMFG_cost from
(
select
colname = LTRIM(b.Vintage) + N'',
[value] = CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1)),
[Color] = case when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@nonceAvgvalue then 'bice'
when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@nonceAvgvalue then 'yellow'
when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@nonceAvgvalue then 'red'
end
from(
select @year-2 Vintage,'Actual' [Firm-empty] union all
select @year-3,'Actual' )b
left join MFG_cost a
on a.[Vintage]=b.[Vintage] and a.[Firm-empty]=b.[Firm-empty]
group by b.Vintage
UNION ALL
--上一年每季的
select
colname = LTRIM(b.Quarter) + N' '+LTRIM(b.Vintage) + N'',
[value] = CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1)),
[Color] = case when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@nonceAvgvalue then 'bice'
when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@nonceAvgvalue then 'yellow'
when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@nonceAvgvalue then 'red'
end
from(
select @year-1 Vintage ,'Q1' Quarter,'Actual' [Firm-empty] union all
select @year-1 ,'Q2','Actual' union all
select @year-1 ,'Q3','Actual' union all
select @year-1 ,'Q4','Actual' )b
left join MFG_cost a
on a.[Vintage]=b.[Vintage] and a.Quarter=b.Quarter and a.[Firm-empty]=b.[Firm-empty]
group by b.Vintage,b.Quarter
UNION ALL
--上一年的
select
colname = LTRIM(Vintage) + N'',
[value] =CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1)),
[Color] = case when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@nonceAvgvalue then 'bice'
when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@nonceAvgvalue then 'yellow'
when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@nonceAvgvalue then 'red'
end
from MFG_cost
where [Vintage]=@year-1 and [Firm-empty]='Actual'
group by Vintage
UNION ALL
--本年度每月的
select
colname = LTRIM(b.Months) + N'',
[value] =CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1)),
[Color] = case when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@nonceAvgvalue then 'bice'
when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@nonceAvgvalue then 'yellow'
when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@nonceAvgvalue then 'red'
end
from(
select @year Vintage ,'January' Months,'Actual' [Firm-empty] union all
select @year ,'February','Actual' union all
select @year ,'March','Actual' union all
select @year ,'April','Actual' union all
select @year ,'May','Actual' union all
select @year ,'June','Actual' union all
select @year ,'July','Actual' union all
select @year ,'August','Actual' union all
select @year ,'September','Actual' union all
select @year ,'October','Actual' union all
select @year ,'November','Actual' union all
select @year ,'December','Actual')b
left join MFG_cost a
on a.[Vintage]=b.[Vintage] and a.Months=b.Months and a.[Firm-empty]=b.[Firm-empty]
GROUP BY b.Vintage,b.Months
UNION ALL
--本年度每季的
select
colname = LTRIM(b.Quarter) + N' '+LTRIM(b.Vintage) + N'',
[value] = CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1)),
[Color] = case when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@nonceAvgvalue then 'bice'
when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@nonceAvgvalue then 'yellow'
when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@nonceAvgvalue then 'red'
end
from(
select @year Vintage ,'Q1' Quarter,'Actual' [Firm-empty] union all
select @year ,'Q2','Actual'union all
select @year ,'Q3','Actual' union all
select @year ,'Q4','Actual' )b
left join MFG_cost a
on a.[Vintage]=b.[Vintage] and a.Quarter=b.Quarter and a.[Firm-empty]=b.[Firm-empty]
group by b.Vintage,b.Quarter
UNION ALL
--今年的
select
colname = LTRIM(Vintage) + N'',
[value] = CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1)),
[Color] = case when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@nonceAvgvalue then 'bice'
when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))>@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@nonceAvgvalue then 'yellow'
when CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@toOneavgvalue and CAST(100.0*sum(Scrap)/sum(RM) AS DECIMAL(10, 1))<@nonceAvgvalue then 'red'
end
from MFG_cost
where [Vintage]=@year and [Firm-empty]='Actual'
group by Vintage
)t
select * from #SumMFG_cost
-- declare @sql varchar(max)
-- select @sql = isnull(@sql + '],[' , '') + colname from #SumMFG_cost group by colname
-- set @sql = '[' + @sql + ']'
-- exec ('select * from (select * from #SumMFG_cost) a pivot (max([value]) for colname in (' + @sql + ')) b')
END
colname value Color
2008 NULL NULL
2009 20.0 NULL
Q1 2010 13.3 NULL
Q2 2010 13.3 NULL
Q3 2010 13.3 NULL
Q4 2010 13.3 NULL
2010 13.3 NULL
January 10.0 NULL
February 10.0 NULL
March 10.0 NULL
April 10.0 NULL
May 10.0 NULL
June 10.0 NULL
July 10.0 NULL
August 10.0 NULL
September 10.0 NULL
October NULL NULL
November NULL NULL
December NULL NULL
Q1 2011 10.0 NULL
Q2 2011 10.0 NULL
Q3 2011 10.0 NULL
Q4 2011 NULL NULL
2011 10.0 NULL
为什么打印出的结果Color为null,
看看我case 是不是判断有总是!
...全文
请发表友善的回复…
发表回复
andy_liucj 2011-04-12
- 打赏
- 举报
哈哈，看到这个应该没人不痛
fanzhouqi 2011-04-12
- 打赏
- 举报
蛋蛋很疼
-晴天 2011-04-12
- 打赏
- 举报
或者,把里面针对某一段时间的直接在企业管理器里运行看看.
-晴天 2011-04-12
- 打赏
- 举报
在过程里只留去年一年或本年一年的那句,把别的都去掉试试.
--小F-- 2011-04-12
- 打赏
- 举报
PRINT出来看看
AcHerat 2011-04-12
- 打赏
- 举报
我也痛！
王向飞 2011-04-12
- 打赏
- 举报
你可以只拿一段来看
-晴天 2011-04-12
- 打赏
- 举报
虽然脑壳痛,但还是想看,求楼主行行好给点表结构和数据吧...
快溜 2011-04-12
- 打赏
- 举报
脑壳痛 two too.
-晴天 2011-04-12
- 打赏
- 举报
脑壳痛 too.
Mr_Nice 2011-04-12
- 打赏
- 举报
