| Author |
 Topic  |
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
133 Posts |
 Posted - 12/21/2012 : 08:44:21
|
I have some code that works perfectly except it doesn't pick up one record that it should (due to something someone stupidly did to it....don't ask!) So I thought I'd hardcode it.
I thought I'd cracked it with a UNION to the following code
UNION ALL
SELECT CASE WHEN (CONVERT(CHAR(6), DATEADD(M, -13, GETDATE()), 112) <= '201111') THEN
'201111' END AS MONTH, 'FC57000' AS NCODE, '350.7' AS PL
which works but in January I don't want this record to appear as November 2011 will be more than 13 months prior.
I tried to simulate this by changing DATEADD(M, -13 to DATEADD(M, -12
but the record still appears, albeit with a NULL in the MONTH column.
What's the correct syntax?
TIA. |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/21/2012 : 08:49:29
|
What do you want to see in January - can you describe the rule you want to use given a date? Perhaps this?UNION ALL
SELECT CASE WHEN (CONVERT(CHAR(6), DATEADD(M, -13, GETDATE()), 112) <= '201111') THEN
'201111' ELSE '201211' END AS MONTH, 'FC57000' AS NCODE, '350.7' AS PL |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 12/21/2012 : 08:49:50
|
just add a condition like
...
UNION ALL
SELECT CASE WHEN (CONVERT(CHAR(6), DATEADD(M, -13, GETDATE()), 112) <= '201111') THEN
'201111' END AS MONTH, 'FC57000' AS NCODE, '350.7' AS PL
WHERE DATEPART(mm,GETDATE()) > 1
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
133 Posts |
Posted - 12/21/2012 : 08:50:53
|
quote:
Originally posted by sunitabeck
What do you want to see in January - can you describe the rule you want to use given a date? Perhaps this?UNION ALL
SELECT CASE WHEN (CONVERT(CHAR(6), DATEADD(M, -13, GETDATE()), 112) <= '201111') THEN
'201111' ELSE '201211' END AS MONTH, 'FC57000' AS NCODE, '350.7' AS PL
I simply want the record to be ignored after that time so it shouldn't appear at all. |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/21/2012 : 08:52:20
|
| Then what Visakh suggested |
|
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
133 Posts |
Posted - 12/21/2012 : 08:56:31
|
| Thanks both, much appreciated. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 12/21/2012 : 08:56:56
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Hard-coding a value but only for a limited time?
