CREATE PROCEDURE [dbo].[usp_get_pricing_matrix_0413] 
	(@start_date datetime,@no_of_week int,@no_of_frames int,@price_code varchar(10))
AS
BEGIN
	SET NOCOUNT ON;
    Declare @start_month varchar(12),@ST_month INT
    DECLARE @frame_value int
    DECLARE @week_value int
    DECLARE @diff_month int
    Declare @month_value int
    
    -----------------------------------------------------------------------------------------
   SET @frame_value=(select DISTINCT CASE WHEN  @no_of_frames between 0 and 4 then [0_4]
							 WHEN  @no_of_frames between 5 and 9 then [5_9]
							 WHEN  @no_of_frames between 10 and 19 then [10_19]
							 WHEN  @no_of_frames  >= 25 then [25+]
	 end from pricing_matrix )
    -----------------------------------------------------------------------------------------

        SET @week_value=(SELECT TOP 1 CASE WHEN  @no_of_week=1  then [1_week]
							 WHEN  @no_of_week=2 then [2_weeks]
							 WHEN  @no_of_week =3 then [3_weeks]
							 WHEN  @no_of_week =4 then [4_weeks]
							 WHEN  @no_of_week between 5 and 24 then [5_24_weeks]
							end
							 FROM pricing_matrix)
    ------------------------------------------------------------------------------------------
    SET @ST_month=datepart(mm,@start_date)
-----------------------------------------------------------------
        
  if (@St_month=1)
  Begin
   set @month_value=( SELECT DISTINCT  CASE WHEN @St_month between 1 and 1 then [january]
		  when  @St_month between 1 and 2 then ([january]+[february])/2
		  when  @St_month between 1 and 3 then ([january]+[february]+[march])/3
		 when  @St_month between 1 and 4 then ([january]+[february]+[march]+[april])/4
		 when   @St_month between 1 and 5 then ([january]+[february]+[march]+[april]+[may])/5
		 when   @St_month between 1 and 6  then ([january]+[february]+[march]+[april]+[may]+[jun])/6
		 when   @St_month between 1 and 7 then ([january]+[february]+[march]+[april]+[may]+[jun]+[july])/7
		 when   @St_month between 1 and 8 then ([january]+[february]+[march]+[april]+[may]+[jun]+[july]+[august])/8
		 when   @St_month between 1 and 9 then ([january]+[february]+[march]+[april]+[may]+[jun]+[july]+[august]+[septempber])/9
		 when    @St_month between 1 and 10 then ([january]+[february]+[march]+[april]+[may]+[jun]+[july]+[august]+[septempber]+[october])/10
		 when   @St_month between 1 and 11 then ([january]+[february]+[march]+[april]+[may]+[jun]+[july]+[august]+[septempber]+[october]+[november])/11
		 when  @St_month between 1 and 12 then ([january]+[february]+[march]+[april]+[may]+[jun]+[july]+[august]+[septempber]+[october]+[november]+[december])/12
	   end  from pricing_matrix )
   end  
  if (@st_month=2)
  Begin  
 set @month_value=(SELECT DISTINCT  CASE WHEN  @St_month between 2 and 2 then [february]
	  when @St_month between 2 and 3  then ([february]+[march])/2
	  when @St_month between 2 and 4  then ([february]+[march]+[april])/3
	  when @St_month between 2 and 5  then ([february]+[march]+[april]+[may])/4
	 when  @St_month between 2 and 6  then ([february]+[march]+[april]+[may]+[jun])/5
	 when  @St_month between 2 and 7  then ([february]+[march]+[april]+[may]+[jun]+[july])/6
	 when  @St_month between 2 and 8  then ([february]+[march]+[april]+[may]+[jun]+[july]+[august])/7
	 when  @St_month between 2 and 9  then ([february]+[march]+[april]+[may]+[jun]+[july]+[august]+[septempber])/8
	 when  @St_month between 2 and 10  then ([february]+[march]+[april]+[may]+[jun]+[july]+[august]+[septempber]+[october])/9
	 when  @St_month between 2 and 11  then ([february]+[march]+[april]+[may]+[jun]+[july]+[august]+[septempber]+[october] +[november])/10
	 when  @St_month between 2 and 12  then ([february]+[march]+[april]+[may]+[jun]+[july]+[august]+[septempber]+[october]+[november]+[december])/11
	 when  @St_month between 2 and 1  then ([february]+[march]+[april]+[may]+[jun]+[july]+[august]+[septempber]+[october]+[november]+[december]+[january])/12
	   end from pricing_matrix)
  End  
     -- For all the months same format --  

          if (@st_month=12)
  Begin  
 set @month_value=
 (SELECT DISTINCT  
	 CASE	WHEN  @St_month between 12 and 12 then [december]
			WHEN  @St_month between 12 and 1 then ([december]+[january])/2
			WHEN  @St_month between 12 and 2 then ([december]+[january]+[february])/3
			WHEN  @St_month between 12 and 3 then ([december]+[january]+[february]+[march])/4
			WHEN  @St_month between 12 and 4 then ([december]+[january]+[february]+[march]+[april])/5
			WHEN  @St_month between 12 and 5 then ([december]+[january]+[february]+[march]+[april]+[may])/6
			WHEN  @St_month between 12 and 6 then ([december]+[january]+[february]+[march]+[april]+[may]+[jun])/7
			WHEN  @St_month between 12 and 7 then ([december]+[january]+[february]+[march]+[april]+[may]+[jun]+[july])/8
			WHEN  @St_month between 12 and 8 then ([december]+[january]+[february]+[march]+[april]+[may]+[jun]+[july]+[august])/9
			WHEN  @St_month between 12 and 9 then ([december]+[january]+[february]+[march]+[april]+[may]+[jun]+[july]+[august]+[septempber])/10
			WHEN  @St_month between 12 and 10 then  ([december]+[january]+[february]+[march]+[april]+[may]+[jun]+[july]+[august]+[septempber]+[october])/10
			WHEN  @St_month between 12 and 11 then ([december]+[january]+[february]+[march]+[april]+[may]+[jun]+[july]+[august]+[septempber]+[october]+[november])/11
	   END 
   from pricing_matrix)
  End 
   
 SELECT		CONVERT(NUMERIC(8,2),(@month_value + @frame_value + @week_value))
		 /  (CONVERT(NUMERIC(8,2),3))
		 *  average_cost_pp_pw AS PRICE_COST 
		 FROM pricing_matrix 
		 WHERE pricing_code=@price_code
 
 END

quote:

---

Originally posted by MIK_2008

Are there any columns with the names of
Jan, Feb, March etc
And 0_4,5_9 etc
And 1_week, 2_Week

in the table (pricing_matrix ) and having numeric data?

Cheers
MIK

---