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 2012 Forums
 Transact-SQL (2012)
 DATE < > THAN 18 MONTHS IN A CASE STATEMENT

Author  Topic 

Blessed1978
Yak Posting Veteran

97 Posts

Posted - 2014-04-24 : 15:07:31
i have a sales table for my employees. i would like a case statement to check to see if the most recent activity date is less that 18 months i would flag it as 'Not current' if the most recent activity date for the rep is greater than 18 months i would flag it as 'Current'. Please asvise


here is my pseudo
SELECT CASE WHEN (most recent)ACTIVITYDATE < 18 months then 'Not current'
when (most recent)ACTIVITYDATE > 18 months 'current' else 'N/A' END 'STATUS'

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-04-24 : 15:26:03
Something like this maybe:
SELECT 
Employee,
CASE
WHEN MAX(ACTIVITYDATE) < DATEADD(MONTH, -18, CAST(SYSDATETIME() AS DATE)) THEN 'Not current'
ELSE 'current'
END AS STATUS
FROM
TableName
GROUP BY
Employee
Go to Top of Page

Blessed1978
Yak Posting Veteran

97 Posts

Posted - 2014-04-24 : 17:11:45
Thanks for your help . when i run the select statement with the new case it works, however when i do my inserts i keep on getting
Msg 241, Level 16, State 1, Line 7
Conversion failed when converting date and/or time from character string.

i checked my date coluns and they are date and datime datatypes.
Go to Top of Page

WAmin
Starting Member

16 Posts

Posted - 2014-04-24 : 23:00:14
Are you inserting new line or updating?
Can you please show structure of table and your insert statement?
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-04-24 : 23:52:52
quote:
Originally posted by Blessed1978

Thanks for your help . when i run the select statement with the new case it works, however when i do my inserts i keep on getting
Msg 241, Level 16, State 1, Line 7
Conversion failed when converting date and/or time from character string.

i checked my date coluns and they are date and datime datatypes.



Please show us your INSERT query


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

Blessed1978
Yak Posting Veteran

97 Posts

Posted - 2014-04-27 : 16:58:20
my datatype in one of my columns was off, it worked thanks
Go to Top of Page
   

- Advertisement -