Current Position:Home > Naming the evaluated case statement field with concatenating input variable value

Naming the evaluated case statement field with concatenating input variable value

Update:11-30Source: network consolidation
Advertisement
Hi,
I am trying to add the input variable value to case statement evaluated field like below query:
declare @Year varchar(4)
select ProductCode,datepart(mm,Date) MonthValue,
  case when datepart(mm,Date) = 1 then sum(Price) else 0 END AS [email protected],
  case when datepart(mm,Date) = 2 then sum(Price) else 0 END AS [email protected],
  case when datepart(mm,Date) = 3 then sum(Price) else 0 END AS [email protected],
  case when datepart(mm,Date) = 4 then sum(Price) else 0 END AS [email protected],
  case when datepart(mm,Date) = 5 then sum(Price) else 0 END AS [email protected],
  case when datepart(mm,Date) = 6 then sum(Price) else 0 END AS [email protected],
  case when datepart(mm,Date) = 7 then sum(Price) else 0 END AS [email protected],
  case when datepart(mm,Date) = 8 then sum(Price) else 0 END AS [email protected],
  case when datepart(mm,Date) = 9 then sum(Price) else 0 END AS [email protected],
  case when datepart(mm,Date) = 10 then sum(Price) else 0 END AS [email protected],
  case when datepart(mm,Date) = 11 then sum(Price) else 0 END AS [email protected],
  case when datepart(mm,Date) = 12 then sum(Price) else 0 END AS [email protected]
from Sales_Imports where datepart(yyyy,Date) = @Year
group by  ProductCode, datepart(mm,Date)
Please help me any other way as this is not working...
Thanks,
Srini

The Best Answer

Advertisement
create table Sales_Imports (ProductCode int, Price int, [date] date)
insert into Sales_Imports values
(1,10,'1/1/2014'),(1,109,'2/1/2014'),(1,10,'1/1/2014'),(1,109,'12/1/2014'),
(2,10,'1/1/2014'),(2,109,'2/1/2014'),(2,10,'10/1/2014'),(2,109,'12/1/2014')
declare @Year varchar(4)='2014'
DECLARE @MaxCount INT, @SQL NVARCHAR(max) ,@i INT;
Set @MaxCount = 12 
SET @i = 0;
SET @SQL = '';
WHILE @i < @MaxCount
BEGIN
    SET @i = @i + 1;
    SELECT @SQL = @Sql + ', SUM(CASE WHEN datepart(mm,[Date]) = ' + cast(@i AS NVARCHAR(10)) + ' THEN  Price Else 0 END) AS ' +Left(DateName(month,Dateadd(month,@i-1,0)) ,3)[email protected]
END
SET @SQL = N' SELECT  ProductCode, datepart(mm,Date) MonthValue ' + @SQL 
+ N' FROM  Sales_Imports
 where datepart(yyyy,Date) ='+ @Year + N'group by  ProductCode, datepart(mm,Date)
 Order by ProductCode, datepart(mm,Date)';
--PRINT @SQL;
execute (@SQL);
--Or your original one
declare @Year varchar(4)
Set @Year ='2014'
declare @SQL  Nvarchar(4000)
SET @SQL=
N' select ProductCode,datepart(mm,Date) MonthValue,
  case when datepart(mm,Date) = 1 then sum(Price) else 0 END AS [jan' [email protected]+'],
  case when datepart(mm,Date) = 2 then sum(Price) else 0 END AS [feb' [email protected]+'],
  case when datepart(mm,Date) = 3 then sum(Price) else 0 END AS [Mar'[email protected]+'],
  case when datepart(mm,Date) = 4 then sum(Price) else 0 END AS [Apr'[email protected]+'],
  case when datepart(mm,Date) = 5 then sum(Price) else 0 END AS [May'[email protected]+'],
  case when datepart(mm,Date) = 6 then sum(Price) else 0 END AS [june'[email protected]+'],
  case when datepart(mm,Date) = 7 then sum(Price) else 0 END AS [july'[email protected]+'],
  case when datepart(mm,Date) = 8 then sum(Price) else 0 END AS [Aug'[email protected]+'],
  case when datepart(mm,Date) = 9 then sum(Price) else 0 END AS [Sep'[email protected]+'],
  case when datepart(mm,Date) = 10 then sum(Price) else 0 END AS [Oct'[email protected]+'],
  case when datepart(mm,Date) = 11 then sum(Price) else 0 END AS [Nov'[email protected]+'],
  case when datepart(mm,Date) = 12 then sum(Price) else 0 END AS [Dec'[email protected]+']
from Sales_Imports where datepart(yyyy,Date) = ' + @Year +N' group by  ProductCode, datepart(mm,Date)'
--print @Query
Execute (@SQL)
drop table Sales_Imports
  • Naming the evaluated case statement field with concatenating input variable value Update:11-30

    Hi, I am trying to add the input variable value to case statement evaluated field like below query: declare @Year varchar(4) select ProductCode,datepart(mm,Date) MonthValue,   case when datepart(mm,Date) = 1 then sum(Price) else 0 END AS [email protected],  

  • Case Structure Ranges Determined From Input Variables Update:11-30

    Hello,           I am trying to write a vi that will read in a random number, and depending on how close or far away from the intial given value it is, it will do something different to the value using a case structure.(Not positive this is the best

  • Case sensitive field in to_date function Update:11-30

    update "Ab_Order" set "OrderDate" = to_date("Order Date String", 'yyyy/mm/dd) Here the OrderDate column is of type Date "Order Date String" is varchar2. Iam trying to copy the data from "Order Date String"

  • Dispute case Amount Fields in Local Currency Update:11-30

    Is it possible to have dispute case amount fields available in local currency instead of document currency?You can add new fields to the Dispute case (attributes) - therefore it is possible. It is not there as standard - however you can add the field

  • How can I pass a value field to a global variable? Update:10-11

    I have a doubt, I wnat to know if is possible to pass a value field to a global variable inside of DataFlow? That a try to do is at the time to execute a JOB on line (real time), depending of the value field that i receive, only certain DATAFLOWS wil

  • Bind input field value to a variable fails Update:10-11

    Business need : The user wants to re-evaluate lines by a percentage value which he fills via his BI-IP screen. I created an INPUT FIELD in WAD and I am trying to link a variable value (initially created in BI-IP through transaction RSPLAN) to this IN

  • Javascript to copy input fields to another input field.. Update:11-30

    Hi, I have Permanent address fields(input text fields) (address line 1,2,city, state , country,zip code)  and present address fields with same input fields as premanent address and i have a check box is present address different with value Y when che

  • How to Assign a Field value to a Variable?? Update:10-11

    Dear All, Kindly let me know, if <b>"HOW TO ASSIGN A FIELD VALUE TO A VARIABLE????"</b> Situation is, we have a Field (OBJK-TASER) in which 2 tables (SER01 & SER03) are stored. And we want to access names of the Tables which are

  • Using table field name as a variable Update:11-30

    Hi , Is it possible to map some values to a field in a Ztable , if I have the field name of the Ztable in a variable. For ex: I have a ZTABLE -- which has some fields - ZF1 and ZF2. In my code, I determine the field in which I want to put data at run

  • How to keep the field open for input with error message in report program Update:11-30

    Hi,   Need a help in solving the below issue. "How to keep the field open for input with error message in report program" Regards, C.Shasiraj.Hi... you have to use the event: <b>AT SELECTION-SCREEN ON <FIELD> EVENT.</b> u have