我如何在临时表中使用案例我想要几个月值，如果0然后设置否则设置是 [英] how i used case in temp table i want to months value as if 0 then set No else Set Yes

问题描述

USE [ddws_imis2008]
GO
/****** Object:  StoredProcedure [ddws_imis].[PMS_GetMyMprStatusMonth]    Script Date: 12/11/2013 09:44:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--PMS_GetMyMprStatusMonth '2013-2014'
ALTER Procedure [ddws_imis].[PMS_GetMyMprStatusMonth] 
@FinYear varchar(40)
as
begin
select 
StateName,
DistrictName,
BlockName,
ISNULL((select COUNT(*) from WSP_ActivityMPR where BlockId=WAM.BlockId And Monthid=1 and FinYear=@FinYear and AchievedStatus>=1),'')Jan,
ISNULL((select COUNT(*) from WSP_ActivityMPR where BlockId=WAM.BlockId And Monthid=2 and FinYear=@FinYear and AchievedStatus>=1),'')Feb,
ISNULL((select COUNT(*) from WSP_ActivityMPR where BlockId=WAM.BlockId And Monthid=3 and FinYear=@FinYear and AchievedStatus>=1),'')Mar,
ISNULL((select COUNT(*) from WSP_ActivityMPR where BlockId=WAM.BlockId And Monthid=4 and FinYear=@FinYear and AchievedStatus>=1),'')Apr,
ISNULL((select COUNT(*) from WSP_ActivityMPR where BlockId=WAM.BlockId And Monthid=5 and FinYear=@FinYear and AchievedStatus>=1),'')May,
ISNULL((select COUNT(*) from WSP_ActivityMPR where BlockId=WAM.BlockId And Monthid=6 and FinYear=@FinYear and AchievedStatus>=1),'')Jun,
ISNULL((select COUNT(*) from WSP_ActivityMPR where BlockId=WAM.BlockId And Monthid=7 and FinYear=@FinYear and AchievedStatus>=1),'')Jul,
ISNULL((select COUNT(*) from WSP_ActivityMPR where BlockId=WAM.BlockId And Monthid=8 and FinYear=@FinYear and AchievedStatus>=1),'')Aug,
ISNULL((select COUNT(*) from WSP_ActivityMPR where BlockId=WAM.BlockId And Monthid=9 and FinYear=@FinYear and AchievedStatus>=1),'')Sep,
ISNULL((select COUNT(*) from WSP_ActivityMPR where BlockId=WAM.BlockId And Monthid=10 and FinYear=@FinYear and AchievedStatus>=1),'')Oct,
ISNULL((select COUNT(*) from WSP_ActivityMPR where BlockId=WAM.BlockId And Monthid=11 and FinYear=@FinYear and AchievedStatus>=1),'')Nov,
ISNULL((select COUNT(*) from WSP_ActivityMPR where BlockId=WAM.BlockId And Monthid=12 and FinYear=@FinYear and AchievedStatus>=1),'')Dec
into #t
from PMS_StateDistrict WAM

select StateName, DistrictName,BlockName,
 Jan, 
 Feb,
 Mar,
 Apr,
 May,
 Jun,
 Jul,
 Aug,
 Sep,
 Oct,
 Nov,
 Dec 
 From #t 

drop table #t

end

推荐答案

我猜你想要显示否，如果值为0，否则为是，否则为月份字段。如果假设是正确的，那么检查查询是否相同。我已经完成了它只对一个字段做同样的其他字段。

I am guessing that you want to show "No" if the value is 0 and "Yes" otherwise for months field. If the assumption is correct then check the query for the same. I have done it for only one field do to same for other fields as well.

select StateName, DistrictName,BlockName,
 case when Jan > 0 Then 'Yes' else 'No' end JAN,
 Feb,
 Mar,
 Apr,
 May,
 Jun,
 Jul,
 Aug,
 Sep,
 Oct,
 Nov,
 Dec
 From #t

这篇关于我如何在临时表中使用案例我想要几个月值，如果0然后设置否则设置是的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！