Lawrence raja

Lawrence raja

  • NA
  • 131
  • 0

How to put the between and operatorsin Case condition...

Apr 7 2012 3:04 AM
Hi Everyone,
                 I used Sp for getting the date... The date i want from one date to another date... but i got some problem... How to do that... Here is my SP...


alter Procedure [dbo].[Fin_SP_RSForTheMonth]        
(       
@FaciltyType Varchar(25),        
@AsFromMth Varchar(6),
@AsToMth Varchar(6),    
@CurrencyType Varchar(6),       
@Date Varchar(10)       
)             
As             
Begin             
Declare @CurVar Numeric(18,2),@Qry Varchar(Max)

   
Set @CurVar = Case When IsNull(@CurrencyType,'')='Rupees' Then 1 Else       
 Case When IsNull(@CurrencyType,'')='Lacs' Then 100000 Else       
 Case When IsNull(@CurrencyType,'')='Crores' Then 10000000 End End End       
Set @Qry = 'Select Convert(Varchar(10),A.ToDate,103) As To_Due_Date,E.BankName  As FinancierName,C.FacilityTypeDescr As FacilityType,             
  Round(A.PrincipalDue/'+Ltrim(Rtrim(Str(@CurVar)))+',2) AS PrincipalDue,Round(A.IntDue/'+Ltrim(Rtrim(Str(@CurVar)))+',2) As IntDue,       
  Round(A.TotalDue/'+Ltrim(Rtrim(Str(@CurVar)))+',2) As TotalDue,Convert(Varchar(12),GetDate(),103) As ReportDate             
  From WithdrawalRepayment A (NoLock) Join FacilityDetail B (Nolock) On (A.FacilityDetail_Fk = B.FacilityMaster_Pk)             
  Join FacilityType C (Nolock) ON (B.FacilityType_Fk = C.FacilityType_Pk)              
  Join SanctionLetterDetail D(Nolock) On (B.SanctionLetterCode=D.SanctionLetterCode)             
  Join BankMaster E (Nolock) On (D.BankNameShrtDescr_FK = E.BankNameShrtDescr)  Where '+       
  Case When IsNull(@AsFromMth,'')='' Then ' Convert(Varchar(10),A.FromDate,103) = '+''''+@Date+''''
  Else Case When ISNULL(@AsToMth,'')='' Then 'Convert Convert(Varchar(10),A.ToDate,103) = '+''''+@Date+''''   
  --Else Case When IsNull(@AsFromMth,'')<>'' Then 'Convert(Varchar(10),A.ToDate,103) between Convert(Varchar(6),@AsFromMth,112) and Convert(Varchar(6),@AsToMth,112)'
  Else Case When IsNull(@AsFromMth,'')<>'' Then 'Convert(Varchar(10),A.ToDate,103) between @AsFromMth and @AsToMth'
  --Else Case When IsNull(@AsFromMt,'')<>'' Then 'Convert(Varchar(10),A.ToDate,103) between @AsFromMt and @AsToMth ='+''''+@AsFromMt+''''
  Else '' End End End+Case When IsNull(@FaciltyType,'')<>'ALL' Then ' And IsNull(C.FacilityTypeDescr,'+''''+''''+')='+       
  ''''+@FaciltyType+'''' Else ' order by A.ToDate,E.BankName'  End        
Print @Qry      
Exec  (@Qry)     
End

Just tell the solution...

Thanks In Advanced...

Answers (1)