TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
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...
Reply
Answers (
1
)
Simple one for a DBA
Database Programming in WPF