Need to Insert Two Case Statement

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 2000 Forums

Transact-SQL (2000)

Need to Insert Two Case Statement

Author

Topic

daipayan
Posting Yak Master

181 Posts

Posted - 2010-11-16 : 03:11:33

Hello there,

I had a table named as: Email_List
Columns are: Name, Email, Designation, Location, Date_Time
Now I had written following T-SQL:

SELECT	Name, Email, Designation, Location, Date_Time,
		CASE DATEPART(month, Date_Time)
			when DATEPART(month, GETDATE()) then 'New Joinees'
			else 'Existing'
		end as Joining
FROM	dbo.Email_List

This T-SQL will show current month data as "New Joinee" and other month data as "Existing". But I want CURRENT YEAR to be considered in the T-SQL, but I don't know how to do that as if I take only month, then in next year data for current month will also show as "New Joinee".
Let me give you an example:

----------------------------------------------------------------------
Name | Email         | Designation | Location | Date_Time | Joining
----------------------------------------------------------------------
ABC  | abc@gmail.com | Sr. Analyst | India    | 16/11/2010| New Joinee
XYZ  | xyz@gmail.com | Jr. Analyst | China    | 01/10/2010| Existing
PQR  | pqr@gmail.com | Programmer  | China    | 14/11/2009| New Joinee
----------------------------------------------------------------------

See in the table for "PQR", joining status showing New Joinee, whereas it should show "Existing" as date_time for the particular data is 14/11/2009.

Hope I can make you understand my problem.
Please give me a solution.

Daipayan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-16 : 04:37:42

[code]SELECT Name, Email, Designation, Location, Date_Time,
CASE when year(Date_Time)=year(getdate()) then 'New Joinees'
else 'Existing'
end as Joining
FROM dbo.Email_List
[/code]

Madhivanan

Failing to plan is Planning to fail

daipayan
Posting Yak Master

181 Posts

Posted - 2010-11-16 : 06:22:16

Dear Sir,

Thanks for replying.
In your code you are considering only year, but am asking to consider both year and month for checking the JOINING STATUS.

Hope I can make you understand.

quote:
Originally posted by madhivanan

SELECT Name, Email, Designation, Location, Date_Time,
CASE when year(Date_Time)=year(getdate()) then 'New Joinees'
else 'Existing'
end as Joining
FROM dbo.Email_List

Madhivanan

Failing to plan is Planning to fail

Daipayan

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-11-16 : 06:30:45

Add one more condition

SELECT Name, Email, Designation, Location, Date_Time,
CASE when year(Date_Time)=year(getdate()) and month(Date_Time)=month(getdate()) then 'New Joinees'
else 'Existing'
end as Joining
FROM dbo.Email_List

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

daipayan
Posting Yak Master

181 Posts

Posted - 2010-11-16 : 07:04:50

Thank You Sir, it's working...

quote:
Originally posted by senthil_nagore

Add one more condition

SELECT Name, Email, Designation, Location, Date_Time,
CASE when year(Date_Time)=year(getdate()) and month(Date_Time)=month(getdate()) then 'New Joinees'
else 'Existing'
end as Joining
FROM dbo.Email_List

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Regards,
Daipayan
Software Programmer

Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-11-16 : 07:07:29

You are Welcome

quote:
Originally posted by daipayan

Thank You Sir, it's working...

quote:
Originally posted by senthil_nagore

Add one more condition

SELECT Name, Email, Designation, Location, Date_Time,
CASE when year(Date_Time)=year(getdate()) and month(Date_Time)=month(getdate()) then 'New Joinees'
else 'Existing'
end as Joining
FROM dbo.Email_List

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Regards,
Daipayan
Software Programmer

Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources