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...
Loading
SenthilkumarPosted Apr 7, 2012, 10:44 AM
Can you provide some more information like where you are facing the issue.
1) Are you facing any syntax issue with case statement
2) Are you facing any problem with date difference
If you get any error then post here.. You need any solution for logic?