Changing a returned value depending on date? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Changing a returned value depending on date?

Author

Topic

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-08-17 : 11:56:41

Hi all,

I am returning the value of a cost centre. However, for reporting purposes, the value needs to be converted to '9999' if the date of the record is pre-2012.

Here's the relevant bit of code:


'P9-'+CAST(RIGHT('0000'+CASE WHEN NL.DET_COSTCENTRE IS NULL THEN '0' ELSE NL.DET_COSTCENTRE END,4) as VARCHAR) as CC_Co,RIGHT('0000'+NL.DET_COSTCENTRE,4) as Cost_Centre,

So I need something incorporated into this bit of code that checks the record date and if it's pre-2012 it simply returns 9999.

Any advice greatly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-17 : 12:11:32

which is the date field involved here?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-08-17 : 13:20:16

The date isn't included in this bit of the code.

Assume the date field is called TRANSACTION_DATE.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-17 : 13:22:33

and is it in same table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-08-17 : 13:22:50

[code]CASE
WHEN DateColumn < '20120101' THEN '9999'
ELSE <forumal here>
END AS Cost_Centre[/code]

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-08-17 : 13:42:17

quote:
Originally posted by visakh16

and is it in same table?

Yes, it's in the Nominal Ledger (NL) table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-17 : 13:59:16

[code]
...
CASE WHEN Datefield >= '20120101'
THEN 'P9-'+CAST(RIGHT('0000'+CASE WHEN NL.DET_COSTCENTRE IS NULL THEN '0' ELSE NL.DET_COSTCENTRE END,4) as VARCHAR) as CC_Co,RIGHT('0000'+NL.DET_COSTCENTRE,4)
ELSE '9999'
END as Cost_Centre
...
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2012-08-17 : 14:15:27

Thank you my friend, I will give it a whirl in work on the morrow.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-17 : 14:18:56

ok no probs..
lets us know if you face any issues and need more help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources