SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to give month input in my procedure


How to give month input in my procedure

Author
Message
raghuldrag
raghuldrag
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1278 Visits: 573
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
John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)

Group: General Forum Members
Points: 79738 Visits: 17895
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
HappyGeek
HappyGeek
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6793 Visits: 4387
I suspect this is linked with your previous post and may be part of a project.

...
twin.devil
twin.devil
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12238 Visits: 2897
HappyGeek - Monday, December 18, 2017 4:11 AM
I suspect this is linked with your previous post and may be part of a project.
+1

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search