Month with Zero Value Using Case

• farooq.hbs

  SSCrazy

  Points: 2083

  More actions

  July 2, 2012 at 5:26 am

  #258737

  Hi guys!!!

  Create Table MetricValues ( Metric_Id Int NOt Null Identity(1,1) ,

  Item_name Varchar(500),

  ItemDate Datetime,

  Value DEcimal(20,2)

  )

  Insert Into MetricValues ( 'CI Annualized Cost Avoidance','01-01-2012',249245.66)

  Insert Into MetricValues ( 'CI Annualized Hard Savings','01-01-2012',1258)

  Insert Into MetricValues ( 'CI Current Year Impact Hard Savings','01-01-2012',482741.42)

  Insert Into MetricValues ( 'CI Annualized Cost Avoidance','01-02-2012',252121.2)

  Insert Into MetricValues ( 'CI Annualized Hard Savings','01-02-2012',2524.36)

  Insert Into MetricValues ( 'CI Current Year Impact Hard Savings','01-02-2012',0)

  Insert Into MetricValues ( 'CI Annualized Cost Avoidance','01-03-2012',583.21)

  Select Value,Item_name,DateName(Month,(ItemDate) )as Months

  From MetricValues

  Order by ItemDate

  If i run the above query I will get the result till March Month But i want to display till December Month Value as 0 Because there is no data in the table after march Month

  WIth Same Item Name for all month

  Like

  CI Annualized Cost Avoidance April 0

  Any help on this? this month should be dynamic but not static

• vinu512

  SSCoach

  Points: 15729

  More actions

  July 3, 2012 at 1:19 am

  #1508034

  Sorry, for the late reply.

  You can create a Calender Table and JOIN the Calender Table with the Table "MetricValues" to get the Desired Output as follows:

  --Creating Table

  Create Table MetricValues ( Metric_Id Int NOt Null Identity(1,1) ,

  Item_name Varchar(500),

  ItemDate Datetime,

  Value DEcimal(20,2)

  )

  --Inserting Sample Data

  Insert Into MetricValues Values( 'CI Annualized Cost Avoidance','01-01-2012',249245.66);

  Insert Into MetricValues Values( 'CI Annualized Hard Savings','01-01-2012',1258);

  Insert Into MetricValues Values( 'CI Current Year Impact Hard Savings','01-01-2012',482741.42);

  Insert Into MetricValues Values( 'CI Annualized Cost Avoidance','01-02-2012',252121.2);

  Insert Into MetricValues Values( 'CI Annualized Hard Savings','01-02-2012',2524.36);

  Insert Into MetricValues Values( 'CI Current Year Impact Hard Savings','01-02-2012',0);

  Insert Into MetricValues Values( 'CI Annualized Cost Avoidance','01-03-2012',583.21);

  --Creating Calender Table

  Create Table Calender

  (MonthId int Identity(1,1),

  MonthName Varchar(20) )

  --Insert Data

  Declare @temp Varchar(2) = 1

  While(@temp <= 12)

  Begin

  Insert Into Calender Values(DateName(MM, Convert(Date, '2012/' + @temp + '/03') ))

  Set @temp = @temp + 1

  End

  --Query For Your Requirement

  Select ISNULL(a.Value, 0), ISNULL(a.Item_name, ''), b.MonthName

  From MetricValues As a Right JOIN Calender As b On DateName(MM, a.ItemDate) = b.MonthName

  Order by b.MonthId

  Hope this helps.

  Vinu Vijayan

  For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉