Dear Sir,
Could you please tell me what is wrong with my query,
Here is my Query, Query should return all the column which are present in both the table :-
SELECT FirstName,MiddleName,LastName FROM Users u
WHERE CONVERT(VARCHAR,u.CreateTime,103) > = GETDATE()-1 AND CONVERT(VARCHAR,u.CreateTime,103) < GETDATE()
OR CONVERT(VARCHAR,u.UpdateTime ,103) > = GETDATE()-1 AND CONVERT(VARCHAR,u.UpdateTime,103) < GETDATE()
OR CONVERT(VARCHAR,u.DeleteTime ,103) > = GETDATE()-1 AND CONVERT(VARCHAR,u.DeleteTime,103) < GETDATE()
AND CONVERT(VARCHAR,u.CreateTime,103) ! = CONVERT(VARCHAR,u.DeleteTime,103)
Here are the conditions
1. Report should contain all users Added,Updated (If user update thr FName,LName etc) or deleted since yesterday
2. If the user is added and Deleted in the same Day than I should not see the user information in the report
3. If the user is added and Updated on same day , than report shd contain most recent information
4. If the user is Deleted and readded on the same day than report shd contain the user information
Create Table #Users1
(
UniQueNumber Varchar(50),
FName varchar (50),
LName Varchar (50),
CreateDate Datetime,
DeleteStatus bool,
UpdateTime DateTime,
DeleteTime Datetime
)
INSERT INTO #Users1 Values ('44354','Kumar','Shruthi-hh','06-21-2016 00:10:000',1,getdate()-1,'06-29-2016 00:10:000')
INSERT INTO #Users1 Values ('44254','Shankar','Raj','06-22-2016 01:18:000',0,getdate(),null)
INSERT INTO #Users1 Values ('34154','Babu','Swamy-k','06-23-2016 06:10:000',0,getdate(),null)
INSERT INTO #Users1 Values ('35054','Mani','Kanta','06-24-2016 06:10:000',0,getdate(),null)
INSERT INTO #Users1 Values ('35055','dsfdf','ett','06-25-2016 06:10:000',0,getdate(),null)
INSERT INTO #Users1 Values ('35056','sss','sfd','06-26-2016 06:10:000',0,getdate(),null)
INSERT INTO #Users1 Values ('35057','jkhk','sdd','06-27-2016 06:10:000',0,getdate(),null)
INSERT INTO #Users1 Values ('35058','eer','s','06-30-2016 06:10:000',0,getdate(),null)
INSERT INTO #Users1 Values ('35059','er','j','07-01-2016 06:10:000',0,getdate(),null)