Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.
Create procedure
@fromDate DATETIME = NULL
,@toDate DATETIME = NULL
,@paymentType nVARCHAR(10) = NULL
,@paymentStatus nVARCHAR(10) = NULL
,@Order_Code nVARCHAR(50) = NULL
as
Select
S.ID,
S.CourseTitle ,
P.Title,
Coalesce(P.FirstName,'')+' '+Coalesce(P.MiddleName,'')+' '+ Coalesce(P.MiddleName,'')+ ', '+ Coalesce(P.FamilyName,'')+ ', '+ Coalesce(C.EMail,'') as [User Name],
C.Country as Country,
C.City,
Coalesce(C.AddressLine1,'') +', '+ Coalesce(C.AddressLine2,'') +', '+ Coalesce(C.POBox,'') +', '+ Coalesce(C.zipCode,'') as [Full Address],
'M:'+Coalesce(C.Mobile,'') +', W:'+Coalesce(C.Telephone,'') +', FX:'+ Coalesce(C.Fax,'') as [Contact Details],
CASE WHEN PD.WithEK = 1 THEN 'Yes' ELSE 'No' END AS [Parents/Guardian with EK?],
PD.StaffNumber,
S.CreationDate as [Registration Date],
Coalesce(S.Currency,'') +' '+ Coalesce(cast(CO.Amount as nvarchar(30)),'') [Payment Amount],
S.PaymentMode as [Payment Mode],
S.PaymentProof as [Payment Proof],
S.OrderCode as [Order ID],
CO.OrderReference as [Order Reference],
CO.orderStatus as [Order Status],
CASE WHEN CO.MaskedCardNumber IS NULL THEN 'NA' ELSE CO.MaskedCardNumber END AS [Masked Card Number],
CASE WHEN CO.CardHolderName IS NULL THEN 'NA' ELSE CO.CardHolderName END AS [Card Holder Name],
CASE WHEN CO.CardBilliingAddress IS NULL THEN 'NA' ELSE CO.CardBilliingAddress END AS [Card Billing Address],
CO.Comments,
CO.CPGAuthCode as [Brass Number],
CO.ModifiedDate,
S.Creationdate
from EACR_Students S
inner join EACR_PersonalDetails P on S.PersonalDetailID = P.ID --and s.CreationDate > convert(datetime,'2013-04-30 00:00:00')
inner join EACR_ParentDetails PD on S.ParentDetailID = PD.ID
inner join EACR_ContactDetails C on S.ContactDetailID=C.ID
inner join EACR_ImportantDetails I on S.ImportantDetailID=I.ID
left join EAC_ConferenceOrder CO on S.OrderCode = CO.OrderCode
where
-- Need to add criteria
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
where (@fromDate is null or s.CreationDate > convert(datetime,@fromdate
(@toDate is null or s.CreationDate < convert(datetime, @toDate)) and
(@Order_Code is null or s.Order_Code = @OrderCode) and ...
.... the same with the other parameters