| Author |
 Topic  |
|
|
shanmugaraj
Posting Yak Master
212 Posts |
 Posted - 04/03/2013 : 07:35:48
|
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
52249 Posts |
Posted - 04/03/2013 : 07:41:21
|
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
|
 |
|
|
MIK_2008
Flowing Fount of Yak Knowledge
Pakistan
1050 Posts |
Posted - 04/03/2013 : 07:46:21
|
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 |
|
|
|
shanmugaraj
Posting Yak Master
212 Posts |
Posted - 04/03/2013 : 08:11:18
|
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 |
|
|
|
MIK_2008
Flowing Fount of Yak Knowledge
Pakistan
1050 Posts |
Posted - 04/03/2013 : 08:22:22
|
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 |
|
|
|
shanmugaraj
Posting Yak Master
212 Posts |
Posted - 04/04/2013 : 00:29:48
|
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 |
|
|
| |
Topic |
|
|
|
Dynamic Query
