Case Statement 1

[newmediaguy]

Hi Guys

I have a problem with some SQL I have inherited....am better with ASP than SQL...

Anyway I need to add to this statement to compare two columns and if they match then display 0.00.

SELECT 
			  AgentCode
			, BranchName
			, ParkCode
			, TACode
			, BookingNumber
			, LeadName
			, dbo.Fn_FormatDate(BookDate,'dd/mm/yyyy') as BookDate
			, dbo.Fn_FormatDate(ArrivalDate,'dd/mm/yyyy') as ArrivalDate
			, dbo.Fn_FormatDate(PaymentDueDate,'dd/mm/yyyy') as PaymentDueDate
			, Deposit
			, Commission
			, GrossValue
			, NetBillings
			, PaidToDate
			, OutstandingAmount
			, CASE 	WHEN PaymentDueDate < ReportDate
					THEN GrossValue - Commission - PaidToDate
					ELSE DEPOSIT - PaidToDate
				END AS AmountDue
			, Season
			, ReportDate
			, dbo.Fn_FormatDate(PaymentDueDate,'dd/mm/yyyy') as PDATE
			, dbo.Fn_FormatDate(ArrivalDate,'dd/mm/yyyy') as ArrDate
			, AgentRef
			,CASE WHEN PaymentDueDate < GETDATE() THEN GrossValue - Commission - PaidToDate

/* This is where I need to comapare : 
Deposit and PaidToDate if they match display 0.00 in NewAmountDue
*/

 ELSE ISNULL(deposit,0) END AS NewAmountDue

Any pointers would be helpful.......in the mean time will keep beating google up...

Thanks

Glen

Glen
Conception | Execution

 

[jby1]

try this ..

CASE WHEN PaymentDueDate < GETDATE() THEN GrossValue - Commission - PaidToDate

 WHEN Deposit = PaidToDate THEN 0

 ELSE ISNULL(deposit,0) END AS NewAmountDue
end as NewAmountDue

 

[newmediaguy]

Thanks

That worked perfectly, was trying with nested case statements that werent working as intended.

Many thanks

Glen

Glen
Conception | Execution