If condition in Where Clause in SQL Query(Please help)

Question

Hi all...
I have a table like below in the database.

ID	Username	Date1	Date2
101	abc	2012-11-01	NULL
102	def	2012-12-03	2013-01-05
103	ghi	2011-02-04	2012-03-04
104	jkl	2012-02-03	2013-01-01
105	mno	NULL	NULL
106	pqr	NULL	2013-04-01

Now, I want to use the "IF-Condition" in my sql query such that -

select * from table where Date1<Date2 and Date1 is not null and Date2 is not null, and Total Days between Date1 and Date2.

The result i want is -

ID	Username	Date1	Date2	Total Datys
101	abc	2012-11-01	NULL	Null
102	def	2012-12-03	2013-01-05	10
103	ghi	2011-02-04	2012-03-04	10
104	jkl	2012-02-03	2013-01-01	0 (Date1> Date2)
105	mno	NULL	NULL	Null
106	pqr	NULL	2013-04-01	Null

How do I get this?? Please help...

Thanks a ton in advance....

Jignesh Trivedi · Answer

hi, try create table #temp ( ID int, Username varchar(20), Status varchar(10), Date1 date, Date2 date, date3 date ) insert into #temp values(101,'abc','Clear','2012-11-01','2013-01-01','2013-01-01'), (102,'def','Rejected','2012-12-03','2013-01-05','2013-01-01'), (103,'ghi','Clear','2011-02-04','2012-03-04','2013-01-01'), (104,'jkl','Rejected','2012-02-03','2013-01-01','2013-01-01'), (105,'mno','Rejected','2013-01-01','2013-01-01','2013-01-01'), (106,'pqr','Clear','2013-01-01','2013-04-01','2013-01-01') drop table #temp select Date1,Date2,Date3,Status,Username, case Status when 'Clear' then datediff(DAY,Date2,Date1) else datediff(DAY,Date3,Date2) end as TotalDays from #temp hope this will help you.

Riddhi Valecha · Answer

Hi... I have 3 dates columns (Date1, Date2, Date3) , 1 status column , 1 ID and 1 username column. THere are 2 status - Clear and Reject. ID Date1 Date2 Date3 Status User 1 1-1-2013 1-1-2013 1-1-2013 Clear A 2 1-1-2013 1-1-2013 1-1-2013 Rejected B 3 1-1-2013 1-1-2013 1-1-2013 Clear C 4 1-1-2013 1-1-2013 1-1-2013 Rejected D 5 1-1-2013 1-1-2013 1-1-2013 Clear E my result - ID Date1 Date2 Date3 Status User TotalDays Clear Date2-date1 Rejected Date3-date2 If status is Clear, then Total number of days between Date2-Date1. If status is Rejected, then Total number of days between Date3-Date2.

Jignesh Trivedi · Answer

hi, I am not much clear about the quesition but using case when you can write If condition in where clause like create table #temp ( ID int, Username varchar(20), Date1 date, Date2 date ) insert into #temp values(101,'abc','2012-11-01',NULL), (102,'def','2012-12-03','2013-01-05'), (103,'ghi','2011-02-04','2012-03-04'), (104,'jkl','2012-02-03','2013-01-01'), (105,'mno',NULL,NULL), (106,'pqr',NULL,'2013-04-01') select * from #temp where case when (Date1 is not null and Date2 is not null) then datediff(DAY,Date1,Date2) else 0 end >=0

Pradip Pandey · Answer

Hi Use this select statement Select ID,case when status='Clear' Then DateDiff(d,Date2,Date1) when status='Rejected' Then DateDiff(d,date3,date2) end as TotalDays,status From Table1 Hope it will help you.

venkata kumar · Answer

select DATEDIFF(day, Date1, date1) from tablename

Riddhi Valecha · Answer

Hi.... I want this date difference on basis of a third column - Status. My requirement is - ID Date1 Date2 Date3 Status TotalDays 1 1-2-2013 1-2-2013 1-2-2013 Clear Date2-Date1 2 2-3-2013 1-2-2013 1-2-2013 Rejected Date3-Date2 3 4-5-2013 1-2-2013 1-2-2013 Clear Date2-Date1 4 5-6-2013 1-2-2013 1-2-2013 Rejected Date3-Date2 5 1-2-2013 1-2-2013 1-2-2013 Clear Date2-Date1 Here in "TotalDays", I want total number of days between 2 dates. When status is Clear, then Date2-Date1== say xdays. When status is Rejected, then Date3-Date3-Date2==say ydays. Please help

venkata kumar · Answer

hi try the fallowing select id,useername,date1,date2,DATEDIFF(day, Date1, date1) as total from tablename

Girish Sapariya · Answer

Hi Riddhi, as per the result you want, try this select *, (case when Date1 > Date2 then 0 else DATEDIFF(D,Date1,Date2)end) as TotalDays from table hope this will help you

If condition in Where Clause in SQL Query(Please help)

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (8)