Help required on Complex Query in SQL

Kumar AU

10y1.2kSQL

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)

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Kumar AU10y ago
Dear Sir, Thank you so much for your valuable time,
This query is scheduled to run on every day Night at 12AM - Monday to Friday. If the query Runs on Monday night than I should get the data for the Friday, Saturday and Sunday dates, however other wise just get the data for the prious date like, If the query runs on Tuesday night than I should get the Monday data and If the Query runs on Wednesday night than i should get the Tuesday data.. similar way it goes on till Friday.
Main Where Clause -
WHERE CONVERT(VARCHAR,CreateDate,103)
BETWEEN (CASE WHEN DATENAME(weekday,GETDATE()) = 'Monday' THEN CONVERT(VARCHAR,DATEADD(DAY,-3,GETDATE()),103) ELSE CONVERT(VARCHAR,DATEADD(DAY,-1,GETDATE()),103) END)
AND CONVERT(VARCHAR,DATEADD(DAY,-1,GETDATE()),103)
So keeping that mind in above condition, I need to get the below details.
1) Report should contain all users added, updated (if user update their FName, LName etc) or deleted since the report run on last time
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 the same day, then report should contain most recent information
4) If the user is deleted and readded on the same day than report should contain the user information - How I can check this condition, I am not sure about what I have written my Condition.
SELECT
u.FirstName,u.MiddleName,u.LastName
FROM
Users u INNER Join Location l
ON l.userId = u.UserId
AND u.CreateTime >= dateadd(d,-1,Convert(Date,GETDATE())) AND u.CreateTime < Convert(Date,GETDATE())
OR u.UpdateTime > = dateadd(d,-1,Convert(Date,GETDATE())) AND u.UpdateTime < Convert(Date,GETDATE())
OR u.DeleteTime > = dateadd(d,-1,Convert(Date,GETDATE())) AND u.DeleteTime < Convert(Date,GETDATE()) AND DeletedStatus =1
AND u.CreateTime ! = u.DeleteTime AND DeletedStatus =1
WHERE
CONVERT(VARCHAR,CreateDate,103)
BETWEEN (CASE WHEN DATENAME(weekday,GETDATE()) = 'Monday' THEN CONVERT(VARCHAR,DATEADD(DAY,-3,GETDATE()),103) ELSE CONVERT(VARCHAR,DATEADD(DAY,-1,GETDATE()),103) END)
AND CONVERT(VARCHAR,DATEADD(DAY,-1,GETDATE()),103)
Can you please correct my query , If am doing anything wrong, because the way 'DeletedStatus' I am using, I am having doubt with that.
0
Syed Shanu10y ago
Iam not sure what exactly you needed .
Here is your updated query
SELECT FName,LName FROM #Users1 u
WHERE CONVERT(VARCHAR,u.CreateDate,103) > = CONVERT(VARCHAR,GETDATE()-1,103) AND CONVERT(VARCHAR,u.CreateDate,103) < CONVERT(VARCHAR,GETDATE(),103)
OR CONVERT(VARCHAR,u.UpdateTime ,103) > = CONVERT(VARCHAR,GETDATE()-1,103) AND CONVERT(VARCHAR,u.UpdateTime,103) < CONVERT(VARCHAR,GETDATE(),103)
OR CONVERT(VARCHAR,u.DeleteTime ,103) > = CONVERT(VARCHAR,GETDATE()-1,103) AND CONVERT(VARCHAR,u.DeleteTime,103) < CONVERT(VARCHAR,GETDATE(),103)
AND CONVERT(VARCHAR,u.CreateDate,103) ! = CONVERT(VARCHAR,u.DeleteTime,103)
0