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 Date1Date2Date3StatusUsername case Status when 'Clear' then datediff(DAYDate2Date1) else datediff(DAYDate3Date2) 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'NULLNULL) (106'pqr'NULL'2013-04-01') select * from #temp where case when (Date1 is not null and Date2 is not null) then datediff(DAYDate1Date2) else 0 end >=0

Pradip Pandey · Answer

Hi Use this select statement Select IDcase when status='Clear' Then DateDiff(dDate2Date1) when status='Rejected' Then DateDiff(ddate3date2) end as TotalDaysstatus 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 iduseernamedate1date2DATEDIFF(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(DDate1Date2)end) as TotalDays from table hope this will help you