Case syntax help

Transact-SQL
1

Hi all

Can someone help me build in a CASE element into this query.

SYSAccountingPeriod returns a value of 1 or 2, so I would like to build into the below syntax that if SYSAccountingPeriod returns a 1 put "FY13" or if a 2 put "FY14" or ELSE put "FY16"

Can someone help me with this please

Cheers all

SELECT NLHistoricalNominalTran.NLHistoricalNominalTranID, NLHistoricalNominalTran.NLDeletedNominalAccountID, NLHistoricalNominalTran.NLNominalTranTypeID, NLHistoricalNominalTran.TransactionDate, NLHistoricalNominalTran.GoodsValueInBaseCurrency, NLHistoricalNominalTran.GoodsValueInDocumentCurrency, NLHistoricalNominalTran.DocumentCurrencyID, NLHistoricalNominalTran.ExchangeRate, NLHistoricalNominalTran.Reference, NLHistoricalNominalTran.Narrative, NLHistoricalNominalTran.UserName, NLHistoricalNominalTran.UserNumber, NLHistoricalNominalTran.Source, NLHistoricalNominalTran.UniqueReferenceNumber, NLHistoricalNominalTran.PostedDate, NLHistoricalNominalTran.TransactionAnalysisCode, NLHistoricalNominalTran.OpLock, NLHistoricalNominalTran.DateTimeCreated, NLHistoricalNominalTran.SYSAccountingPeriodID, NLNominalAccount.NLNominalAccountID, NLNominalAccount.AccountNumber, NLNominalAccount.AccountCostCentre, NLNominalAccount.AccountDepartment, NLNominalAccount.AccountName, SYSAccountingPeriod.PeriodNumber, SYSAccountingPeriod.SYSFinancialYearID
FROM Sage6DGMDLive.dbo.NLHistoricalNominalTran NLHistoricalNominalTran, Sage6DGMDLive.dbo.NLNominalAccount NLNominalAccount, Sage6DGMDLive.dbo.SYSAccountingPeriod SYSAccountingPeriod
WHERE NLHistoricalNominalTran.NLDeletedNominalAccountID = NLNominalAccount.NLNominalAccountID AND SYSAccountingPeriod.SYSAccountingPeriodID = NLHistoricalNominalTran.SYSAccountingPeriodID

2

CASE
WHEN SYSAccountingPeriod=1 THEN 'FY13'
WHEN SYSAccountingPeriod=2 THEN 'FY14'
ELSE 'FY16'
END

3

Thanks for responding tara

Where do I insert that into my statement?

Cheers for your help
Mark

4

Depend where you want it. If you want it as the last column in the returned result set, then put it after SYSAccountingPeriod.SYSFinancialYearID (before the FROM). Add an alias to it so that it's a named column in the result set.

2 Likes
5

Thanks tara thats perfect
Really appreciate your help
All the best

1 Like