select Query issue getting based on the DATEDIFF

Question

I have an table EmployeerAudit

CustomerID  columnName   AmendDatetime
1111  Mobilenumber  2012-01-24 12:46:06.680
1111  HomeNumber  2012-05-04 12:51:30.603
2111  Mobilenumber  2012-01-24 12:46:06.680
2111  HomeNumber  2012-05-04 12:51:30.603
3411  Mobilenumber  2012-01-24 12:46:06.680
3411  HomeNumber  2012-01-24 12:51:30.603

Here i need to get the result where customer who have update their Mobilenumber or HomeNumber  whose  AmendDatetime is greater then
100 days.

case1:
if any customer who has updated his Mobilenumber or HomeNumber whose AmendDatetime is greater then
100 days.  then we should not get those  customer hope  my Question is clear

1:
select * from  EmployeerAudit
Where  DATEDIFF(DAY ,CA.AmEndDatetime ,getdate())>100
and (CA.ColumnName ='Mobilenumber' or CA.ColumnName ='HomeNumber')

2:
select * from  EmployeerAudit
Where  DATEDIFF(DAY ,CA.AmEndDatetime ,getdate())>100
and CA.ColumnName in ('Mobilenumber','HomeNumber')

As here CustomerID 1111 has a an Amenddatetime which is less then 100 days,so in that case i should not get the
customerID 1111

 

 

Answer 1

select * from  EmployeerAudit
Where  DATEDIFF(DAY ,CA.AmEndDatetime ,getdate())>100
and CA.ColumnName in ('Mobilenumber','HomeNumber') and CustomerID
not in(select CustomerID from  EmployeerAudit
Where  DATEDIFF(DAY ,CA.AmEndDatetime ,getdate())<=100
and CA.ColumnName in ('Mobilenumber','HomeNumber'));
go

---

Many Thanks & Best Regards, Hua Min

Answer 2

create table #t (custid int,colname varchar(20),dt datetime)

insert into #t values (1111,'Mobilenumber','2012-01-24 12:46:06.680')
insert into #t values (1111,'HomeNumber','2012-05-04 12:51:30.603')
insert into #t values (2111,'Mobilenumber','2012-01-24 12:46:06.680')
insert into #t values (2111,'HomeNumber','2012-05-04 12:46:06.680')
insert into #t values (3111,'Mobilenumber','2012-01-24 12:46:06.680')
insert into #t values (3111,'HomeNumber','2012-01-24 12:46:06.680')


with cte
as
(
select * from  #t ca
Where  DATEDIFF(DAY ,CA.dt ,getdate())>100 
and CA.colname in ('Mobilenumber','HomeNumber')
) select * from cte where not exists (
select * from #t where #t.custid=cte.custid 
and DATEDIFF(DAY ,#t.dt ,getdate())<=100)

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 3

Hi,

You may try following

select * from  EmployeerAudit CA
Where  CustomerID
not in(select CustomerID from  EmployeerAudit CA1
Where  DATEDIFF(DAY ,CA1.AmEndDatetime ,getdate())<=100
and CA1.ColumnName in ('Mobilenumber','HomeNumber'));

Regards

Meer Hassan

Answer 4

Here's one way:  (I think this is what you're asking?)  Here is a way to only list a customer if any of their phone numbers (Mobile or Home) have not changed in the last 100 days.  

Declare @T Table  (custid int,colname varchar(20),dt datetime)

insert into @t values (1111,'Mobilenumber','2012-01-24 12:46:06.680')
insert into @t values (1111,'HomeNumber','2012-05-04 12:51:30.603')
insert into @T values (2111,'Mobilenumber','2012-01-24 12:46:06.680')
insert into @t values (2111,'HomeNumber','2012-05-04 12:46:06.680')
insert into @t values (3111,'Mobilenumber','2012-01-24 12:46:06.680')
insert into @t values (3111,'HomeNumber','2012-01-24 12:46:06.680')

Select * from @T  T
 Inner Join (Select CustID, Max(dt) as MaxDt
               from @t
              Group by CustID
              Having DateDiff(day, max(dt), getdate()) > 100) as DTMax on DTMax.Custid = T.CustID