SQL QUERY REQUIRED

Question

Post reply

SQL QUERY REQUIRED

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

nicklibee SSC Enthusiast Points: 134 More actions · Answer 1

nicklibee

SSC Enthusiast

Points: 134

June 20, 2013 at 10:26 am

#1626022

OPPS Y WHAT IS THE REASON !!!!!!!!!

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

nicklibee (6/20/2013)
OPPS Y WHAT IS THE REASON !!!!!!!!!

err...huh???

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

nicklibee SSC Enthusiast Points: 134 More actions · Answer 3

here is the answer!!!!!!!!,

Alter FUNCTION FN_Count

(

@ENTERPRISE_MAIN_CODE int,

@Date varchar(50),

@ConditionNo int

)

RETURNS int

AS

BEGIN

Declare @Count int

Declare @PreYear varchar(50)

Declare @CurrentYear varchar(50)

if @ConditionNo=1

begin

Select @Count=count(A.ENTERPRISE_MAIN_CODE)

from TableA A inner join TableB B

on A.ENTERPRISE_MAIN_CODE =B.ENTERPRISE_MAIN_CODE

inner join TableC C on B.ID_NO=C.ID_NO

where SubString(Cast(C.ENTERPRISE_DATE as varchar),0,7)=@Date

and A.ENTERPRISE_MAIN_CODE=@ENTERPRISE_MAIN_CODE

group by A.ENTERPRISE_DESCRIPTION

End

if @ConditionNo=2

begin

Set @PreYear=Cast(SubString(@Date,0,5) as int)-1

Select @Count=count(A.ENTERPRISE_MAIN_CODE)

from TableA A inner join TableB B

on A.ENTERPRISE_MAIN_CODE =B.ENTERPRISE_MAIN_CODE

inner join TableC C on B.ID_NO=C.ID_NO

where SubString(Cast(C.ENTERPRISE_DATE as varchar),0,7)=Cast(@PreYear as varchar)+SubString(@Date,5,2)

and A.ENTERPRISE_MAIN_CODE=@ENTERPRISE_MAIN_CODE

group by A.ENTERPRISE_DESCRIPTION

End

if @ConditionNo=3

begin

Set @CurrentYear=SubString(@Date,0,5)

Select @Count=count(A.ENTERPRISE_MAIN_CODE)

from TableA A inner join TableB B

on A.ENTERPRISE_MAIN_CODE =B.ENTERPRISE_MAIN_CODE

inner join TableC C on B.ID_NO=C.ID_NO

where SubString(Cast(C.ENTERPRISE_DATE as varchar),0,7)>=(@CurrentYear+'01')

and SubString(Cast(C.ENTERPRISE_DATE as varchar),0,7)<=@Date

and A.ENTERPRISE_MAIN_CODE=@ENTERPRISE_MAIN_CODE

group by A.ENTERPRISE_DESCRIPTION

End

if @ConditionNo=4

begin

Set @PreYear=Cast(SubString(@Date,0,5) as int)-1

Select @Count=count(A.ENTERPRISE_MAIN_CODE)

from TableA A inner join TableB B

on A.ENTERPRISE_MAIN_CODE =B.ENTERPRISE_MAIN_CODE

inner join TableC C on B.ID_NO=C.ID_NO

where SubString(Cast(C.ENTERPRISE_DATE as varchar),0,7)>=(Cast(@PreYear as varchar)+'01')

and SubString(Cast(C.ENTERPRISE_DATE as varchar),0,7)<=@Date

and A.ENTERPRISE_MAIN_CODE=@ENTERPRISE_MAIN_CODE

group by A.ENTERPRISE_DESCRIPTION

End

if @Count IS Null

begin

Set @Count=0

End

return @Count

END

Alter FUNCTION FN_Amount

(

@ENTERPRISE_MAIN_CODE int,

@Date varchar(50),

@ConditionNo int

)

RETURNS int

AS

BEGIN

Declare @Count int

Declare @PreYear varchar(50)

Declare @CurrentYear varchar(50)

if @ConditionNo=1

begin

Select @Count=Isnull(sum(C.ENTERPRISE_AMT),0)

from TableA A inner join TableB B

on A.ENTERPRISE_MAIN_CODE =B.ENTERPRISE_MAIN_CODE

inner join TableC C on B.ID_NO=C.ID_NO

where SubString(Cast(C.ENTERPRISE_DATE as varchar),0,7)=@Date

and A.ENTERPRISE_MAIN_CODE=@ENTERPRISE_MAIN_CODE

group by A.ENTERPRISE_DESCRIPTION

End

if @ConditionNo=2

begin

Set @PreYear=Cast(SubString(@Date,0,5) as int)-1

Select @Count=Isnull(sum(C.ENTERPRISE_AMT),0)

from TableA A inner join TableB B

on A.ENTERPRISE_MAIN_CODE =B.ENTERPRISE_MAIN_CODE

inner join TableC C on B.ID_NO=C.ID_NO

where SubString(Cast(C.ENTERPRISE_DATE as varchar),0,7)=Cast(@PreYear as varchar)+SubString(@Date,5,2)

and A.ENTERPRISE_MAIN_CODE=@ENTERPRISE_MAIN_CODE

group by A.ENTERPRISE_DESCRIPTION

End

if @ConditionNo=3

begin

Set @CurrentYear=SubString(@Date,0,5)

Select @Count=Isnull(sum(C.ENTERPRISE_AMT),0)

from TableA A inner join TableB B

on A.ENTERPRISE_MAIN_CODE =B.ENTERPRISE_MAIN_CODE

inner join TableC C on B.ID_NO=C.ID_NO

where SubString(Cast(C.ENTERPRISE_DATE as varchar),0,7)>=(@CurrentYear+'01')

and SubString(Cast(C.ENTERPRISE_DATE as varchar),0,7)<=@Date

and A.ENTERPRISE_MAIN_CODE=@ENTERPRISE_MAIN_CODE

group by A.ENTERPRISE_DESCRIPTION

End

if @ConditionNo=4

begin

Set @PreYear=Cast(SubString(@Date,0,5) as int)-1

Select @Count=Isnull(sum(C.ENTERPRISE_AMT),0)

from TableA A inner join TableB B

on A.ENTERPRISE_MAIN_CODE =B.ENTERPRISE_MAIN_CODE

inner join TableC C on B.ID_NO=C.ID_NO

where SubString(Cast(C.ENTERPRISE_DATE as varchar),0,7)>=(Cast(@PreYear as varchar)+'01')

and SubString(Cast(C.ENTERPRISE_DATE as varchar),0,7)<=@Date

and A.ENTERPRISE_MAIN_CODE=@ENTERPRISE_MAIN_CODE

group by A.ENTERPRISE_DESCRIPTION

End

if @Count IS Null

begin

Set @Count=0

End

return @Count

END

Declare @Date varchar(50) set @Date ='201303'

Select A.ENTERPRISE_DESCRIPTION,dbo.FN_Count(A.ENTERPRISE_MAIN_CODE,@Date,1) as count1,dbo.FN_Amount(A.ENTERPRISE_MAIN_CODE,@Date,1) as Amount1 ,dbo.FN_Count(A.ENTERPRISE_MAIN_CODE,@Date,2) as count2,dbo.FN_Amount(A.ENTERPRISE_MAIN_CODE,@Date,2) as Amount2, dbo.FN_Count(A.ENTERPRISE_MAIN_CODE,@Date,3) as count3,dbo.FN_Amount(A.ENTERPRISE_MAIN_CODE,@Date,3) as Amount3, dbo.FN_Count(A.ENTERPRISE_MAIN_CODE,@Date,3) as count4,dbo.FN_Amount(A.ENTERPRISE_MAIN_CODE,@Date,4) as Amount4 from TableA A

now run