case statement returning NULL values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

case statement returning NULL values

Author

Topic

Markde101
Starting Member

1 Post

Posted - 2009-07-01 : 03:42:30

Hi Guys

Please see query below

im trying to get the end amount for the "barclays eur" account on a monthhlt basis summed up and then per yr basis, so u should only have 12 entries(months) per yr,etc.Yet when i run the query it returns 1000+ rows all with Null values including multiple rows of month,year
any ideas what im doing wrong
any help would be awesome

SELECT
CASE
WHEN (SY_Bank.ShortName + VT_RefundReceipt.currency) = 'Barclays EUR'
THEN ((dbo.ConvertToCurrencyAsSpot(VT_RefundReceipt.Currency,'GBP',VT_RefundReceipt.amountReceived))
-(sum(dbo.ConvertToCurrencyAsSpot(VT_RefundadviceReceipt.Currency,'GBP',VT_refundAdviceReceipt.expectedamount))))
* RP_CurrencyRate.Rate
END AS [BARCLAYS EUR],

datepart(mm,VT_RefundReceipt.valuedate) as [month],
datepart(yyyy,VT_RefundReceipt.valuedate) as [year]
FROM VT_RefundReceipt /*-- Cash --*/
LEFT JOIN VT_allocatedRefunds /*-- Link between cash and docs --*/
ON VT_RefundReceipt.refundID = VT_allocatedRefunds.refundId
LEFT JOIN VT_refundAdviceReceipt /*-- Docs --*/
ON VT_refundAdviceReceipt.RefundAdviceReceiptID = VT_allocatedRefunds.RefundAdviceReceiptID
LEFT JOIN RP_CurrencyRate
ON RP_CurrencyRate.currency#from = VT_RefundReceipt.currency
AND currency#to = 'GBP'
LEFT JOIN SY_BankAccount
ON VT_RefundReceipt.BankAccntID = SY_BankAccount.BankAccntID
LEFT JOIN SY_bankbranch
ON SY_bankAccount.bankbranchID = SY_bankbranch.bankbranchID
LEFT JOIN SY_Bank
ON SY_BankBranch.BankID = SY_Bank.BankID
WHERE VT_refundReceipt.JournalBatchID is not Null
AND (VT_refundreceipt.utilised = 'f'
OR VT_refundreceipt.utilised is null)
AND VT_refundreceipt.deletingComment is null
GROUP BY (SY_Bank.ShortName + VT_RefundReceipt.currency),VT_RefundReceipt.currency,VT_RefundReceipt.amountReceived
,RP_CurrencyRate.Rate,VT_RefundReceipt.valuedate
ORDER BY (SY_Bank.ShortName + VT_RefundReceipt.currency),CONVERT(datetime,VT_RefundReceipt.valuedate,106)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-01 : 04:15:56

You need an extra space

SELECT		CASE 
			WHEN SY_Bank.ShortName + ' ' + VT_RefundReceipt.currency = 'Barclays EUR' THEN dbo.ConvertToCurrencyAsSpot(VT_RefundReceipt.Currency, 'GBP', VT_RefundReceipt.amountReceived) 
													- SUM(dbo.ConvertToCurrencyAsSpot(VT_RefundadviceReceipt.Currency, 'GBP', VT_refundAdviceReceipt.expectedamount))
		END * RP_CurrencyRate.Rate AS [BARCLAYS EUR],
		datepart(mm,VT_RefundReceipt.valuedate) as [month],
		datepart(yyyy,VT_RefundReceipt.valuedate) as [year]
FROM		VT_RefundReceipt
LEFT JOIN	VT_allocatedRefunds ON VT_RefundReceipt.refundID = VT_allocatedRefunds.refundId
LEFT JOIN	VT_refundAdviceReceipt ON VT_refundAdviceReceipt.RefundAdviceReceiptID = VT_allocatedRefunds.RefundAdviceReceiptID 
LEFT JOIN	RP_CurrencyRate ON RP_CurrencyRate.currency#from = VT_RefundReceipt.currency
			AND currency#to = 'GBP'
LEFT JOIN	SY_BankAccount ON VT_RefundReceipt.BankAccntID = SY_BankAccount.BankAccntID
LEFT JOIN	SY_bankbranch ON SY_bankAccount.bankbranchID = SY_bankbranch.bankbranchID
LEFT JOIN	SY_Bank  ON SY_BankBranch.BankID = SY_Bank.BankID
WHERE		VT_refundReceipt.JournalBatchID is not Null
		AND (VT_refundreceipt.utilised = 'f' OR VT_refundreceipt.utilised is null)
		AND VT_refundreceipt.deletingComment is null
GROUP BY	SY_Bank.ShortName + ' ' + VT_RefundReceipt.currency,
		VT_RefundReceipt.currency,
		VT_RefundReceipt.amountReceived,
		RP_CurrencyRate.Rate,
		VT_RefundReceipt.valuedate
ORDER BY	SY_Bank.ShortName + ' ' + VT_RefundReceipt.currency,
		CONVERT(datetime, VT_RefundReceipt.valuedate, 106)

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-01 : 12:44:58

and if by any chance , fields are of nullable type then make sure you account for that also


SELECT		CASE 
			WHEN COALESCE(SY_Bank.ShortName + ' ','') + COALESCE(VT_RefundReceipt.currency,'') = 'Barclays EUR' THEN dbo.ConvertToCurrencyAsSpot(VT_RefundReceipt.Currency, 'GBP', VT_RefundReceipt.amountReceived) 
													- SUM(dbo.ConvertToCurrencyAsSpot(VT_RefundadviceReceipt.Currency, 'GBP', VT_refundAdviceReceipt.expectedamount))
		END * RP_CurrencyRate.Rate AS [BARCLAYS EUR],
		datepart(mm,VT_RefundReceipt.valuedate) as [month],
		datepart(yyyy,VT_RefundReceipt.valuedate) as [year]
FROM		VT_RefundReceipt
LEFT JOIN	VT_allocatedRefunds ON VT_RefundReceipt.refundID = VT_allocatedRefunds.refundId
LEFT JOIN	VT_refundAdviceReceipt ON VT_refundAdviceReceipt.RefundAdviceReceiptID = VT_allocatedRefunds.RefundAdviceReceiptID 
LEFT JOIN	RP_CurrencyRate ON RP_CurrencyRate.currency#from = VT_RefundReceipt.currency
			AND currency#to = 'GBP'
LEFT JOIN	SY_BankAccount ON VT_RefundReceipt.BankAccntID = SY_BankAccount.BankAccntID
LEFT JOIN	SY_bankbranch ON SY_bankAccount.bankbranchID = SY_bankbranch.bankbranchID
LEFT JOIN	SY_Bank  ON SY_BankBranch.BankID = SY_Bank.BankID
WHERE		VT_refundReceipt.JournalBatchID is not Null
		AND (VT_refundreceipt.utilised = 'f' OR VT_refundreceipt.utilised is null)
		AND VT_refundreceipt.deletingComment is null
GROUP BY	SY_Bank.ShortName + ' ' + VT_RefundReceipt.currency,
		VT_RefundReceipt.currency,
		VT_RefundReceipt.amountReceived,
		RP_CurrencyRate.Rate,
		VT_RefundReceipt.valuedate
ORDER BY	COALESCE(SY_Bank.ShortName + ' ','') + COALESCE(VT_RefundReceipt.currency,''),
		CONVERT(datetime, VT_RefundReceipt.valuedate, 106)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources