December 18, 2017 at 2:03 am
Hi Friends,
I am creating one procedure to get Quarterly data from month input
alter procedure Ram_year_qu_month
(
@Fyear as varchar(20),
@Fmon as varchar(20),
@Tyear as varchar(20),
@Tmon as varchar(20),
@type as varchar(25)
)
as
begin
select
y.M1,
y.M2,
y.Quater,
y.TotalM7,
Type='Quaterly',
Disper=case when y.M2 in ('Apr','May','Jun') then 100
when y.M2 in ('Jul','Aug','Sep')then 100-(0.50 *100)
when y.M2 in ('Oct','Nov','Dec') then 100-(0.33 *100)
when y.M2 in ('Jan','Feb','Mar') then 100-(0.25 * 100)
end
into
#Temp1
from
(
select
x.M1,
x.M2,
x.Quater,
TotalM7=x.ValueM6/x.ValueM5
from
(
select M1,M2,Quater= case when m2 in ('Apr','May','Jun' ) then '1Q'
when m2 in ('Jul','Aug','Sep' ) then '2Q'
when m2 in ('Oct','Nov','Dec' ) then '3Q'
when m2 in ('Jan','Feb','Mar' ) then '4Q'
else CAST(m2 as VARCHAR) end,
ValueM5=sum(case when m2 in ('Apr','May','Jun') and M1=@Fyear and M1=@Tyear then M5
when m2 in ('Jul','Aug','Sep') and M1=@Fyear and M1=@Tyear then M5
when m2 in ('Oct','Nov','Dec') and M1=@Fyear and M1=@Tyear then M5
when m2 in ('Jan','Feb','Mar') and M1=@Fyear and M1=@Tyear then M5
end
) ,
ValueM6=sum(case when m2 in ('Apr','May','Jun') and M1=@Fyear and M1=@Tyear then M6
when m2 in ('Jul','Aug','Sep') and M1=@Fyear and M1=@Tyear then M6
when m2 in ('Oct','Nov','Dec') and M1=@Fyear and M1=@Tyear then M6
when m2 in ('Jan','Feb','Mar') and M1=@Fyear and M1=@Tyear then M6
end
)
From POMDB.erptest.UDSNEW1410065214_M where M1 between @Fyear and @Tyear
group by
M2,
M1
)x
)y
group by
y.M1,
y.M2,
y.Quater,
y.TotalM7
select * from #Temp1
end
/* Exec Ram_year_qu_month '2013-2014','Apr','2013-2014','Nov','Quarterly' */
if i am giving april to nov in month filter i would get the data with in month range so how to include month filter in my procedure Kindly help me guys
December 18, 2017 at 2:51 am
Let's start with the basics before we even look at the logic of your query:
(1) Why are you passing in dates as varchar? You only need two parameters here: one for the start date and one for the end date. Make them both smalldatetime
(2) What is the @type parameter for? It's not used in the stored procedure
(3) Why do you select everything into a temp table and then select straight back out?
(4) Is this stored procedure in the POMDB database?
We're probably going to need some DDL, sample data and expected results to sort this out.
John
December 18, 2017 at 4:11 am
I suspect this is linked with your previous post and may be part of a project.
...
December 18, 2017 at 5:21 am
HappyGeek - Monday, December 18, 2017 4:11 AMI suspect this is linked with your previous post and may be part of a project.
+1
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply