SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 DATE < > THAN 18 MONTHS IN A CASE STATEMENT
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Blessed1978
Yak Posting Veteran

65 Posts

Posted - 04/24/2014 :  15:07:31  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/24/2014 :  15:26:03  Show Profile  Reply with Quote
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

65 Posts

Posted - 04/24/2014 :  17:11:45  Show Profile  Reply with Quote
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

Singapore
16 Posts

Posted - 04/24/2014 :  23:00:14  Show Profile  Reply with Quote
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)

Singapore
17642 Posts

Posted - 04/24/2014 :  23:52:52  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

Blessed1978
Yak Posting Veteran

65 Posts

Posted - 04/27/2014 :  16:58:20  Show Profile  Reply with Quote
my datatype in one of my columns was off, it worked thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000