Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Dynamic Query		  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shanmugaraj
Posting Yak Master

219 Posts
Posted - 04/03/2013 :  07:35:48  Show Profile  Send shanmugaraj a Yahoo! Message  Reply with Quote
In the Below SP, I need to have the @month_value Select case dynamic for all the months of the year.


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


THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts
Posted - 04/03/2013 :  07:41:21  Show Profile  Reply with Quote
you dont need those CASE WHEN constructs if you already break logic by means of IF conditions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts
Posted - 04/03/2013 :  07:46:21  Show Profile  Reply with Quote
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
Go to Top of Page

shanmugaraj
Posting Yak Master

219 Posts
Posted - 04/03/2013 :  08:11:18  Show Profile  Send shanmugaraj a Yahoo! Message  Reply with Quote
the names within [] are column names

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


THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts
Posted - 04/03/2013 :  08:22:22  Show Profile  Reply with Quote
And they should have Numeric Data as well ... however requirements are still not clear

taking your first sample


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


this snip says that when @St_month=1 then check
1) if @St_month is betwen 1 and 1 (which is true)
2) if @St_month is betwen 1 and 2 (which is true)
N) if @St_month is betwen 1 and N (which is true)

so which one is desired? as all are getting true. hope you are getting my point.

Perhaps it would be better if you come up with your requirements in light of some sample data/records (provided in the form of Insert into statments) and desired output(explanation of the logic behind output). May be anyone from the team could come up with a better solution.

Cheers
MIK
Edited by - MIK_2008 on 04/03/2013 08:22:45
Go to Top of Page

shanmugaraj
Posting Yak Master

219 Posts
Posted - 04/04/2013 :  00:29:48  Show Profile  Send shanmugaraj a Yahoo! Message  Reply with Quote
I need to set @month_value= dynamically based on month.
Since the query is big, i need to do it best optimzed way
my create table script is given below..
CREATE TABLE [dbo].[pricing_matrix](
[pricing_id] [int] IDENTITY(1,1) NOT NULL,
[category] [int] NULL,
[regionality] [int] NULL,
[environment_id] [int] NULL,
[dimension_code_id] [int] NULL,
[format_type_id] [int] NULL,
[media_owner_id] [int] NULL,
[pricing_code] [varchar](10) NULL,
[average_cost_pp_pw] [numeric](8, 2) NULL,
[average_cost_pp_pd] [numeric](8, 2) NULL,
[january] [numeric](8, 2) NULL,
[february] [numeric](8, 2) NULL,
[march] [numeric](8, 2) NULL,
[april] [numeric](8, 2) NULL,
[may] [numeric](8, 2) NULL,
[jun] [numeric](8, 2) NULL,
[july] [numeric](8, 2) NULL,
[august] [numeric](8, 2) NULL,
[septempber] [numeric](8, 2) NULL,
[october] [numeric](8, 2) NULL,
[november] [numeric](8, 2) NULL,
[december] [numeric](8, 2) NULL,
[0_4] [numeric](8, 2) NULL,
[5_9] [numeric](8, 2) NULL,
[10_19] [numeric](8, 2) NULL,
[20_49] [numeric](8, 2) NULL,
[50_99] [numeric](8, 2) NULL,
[100_199] [numeric](8, 2) NULL,
[200_499] [numeric](8, 2) NULL,
[500_999] [numeric](8, 2) NULL,
[1000_1999] [numeric](8, 2) NULL,
[2000_2999] [numeric](8, 2) NULL,
[3000_4999] [numeric](8, 2) NULL,
[5000+] [numeric](8, 2) NULL,
[1_week] [numeric](8, 2) NULL,
[2_weeks] [numeric](8, 2) NULL,
[3_weeks] [numeric](8, 2) NULL,
[4_weeks] [numeric](8, 2) NULL,
[5_24_weeks] [numeric](8, 2) NULL,
[25_52_weeks] [numeric](8, 2) NULL,
[isactive] [bit] NULL,
CONSTRAINT [PK_pricing_matrix] PRIMARY KEY CLUSTERED
(
[pricing_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000