T SQL query for Report

Question

Hello,

I have below sample code, and I am trying to write report, the new report will add New column saying PossibleDups,

If EID has possible matches for DateVisited or Datevisited is (Plus or Minus 2) when compared with other records for that EID say A1, then PossibleDups field become Y,if not N

similarly Datetested matches or Datetested is (plus or mInus 2) then PossibleDups field become Y, if not become N

DECLARE @TEMP Table (EID VARCHAR(5), DateVisited Date, DateTested Date)
INSERT INTO @TEMP VALUES ('A1','1/1/2012','1/8/2012')
INSERT INTO @TEMP VALUES ('A1','1/3/2012','1/21/2012')
INSERT INTO @TEMP VALUES ('A1','1/9/2012','1/28/2012')
INSERT INTO @TEMP VALUES ('A1','1/30/2012','1/29/2012')
INSERT INTO @TEMP VALUES ('B1','3/1/2012','7/8/2012')
INSERT INTO @TEMP VALUES ('B1','5/2/2012','7/9/2012')
INSERT INTO @TEMP VALUES ('B1','9/1/2012','9/8/2012')
INSERT INTO @TEMP VALUES ('C1','2/1/2012','2/2/2012')
INSERT INTO @TEMP VALUES ('C1','2/3/2012','11/8/2012')
INSERT INTO @TEMP VALUES ('D1','1/10/2012','4/8/2012')
INSERT INTO @TEMP VALUES ('D1','1/15/2012','4/8/2012')
INSERT INTO @TEMP VALUES ('D1','1/17/2012','8/8/2012')
INSERT INTO @TEMP VALUES ('D1','1/11/2012','1/8/2012')
SELECT * FROM @TEMP

sample output for A1 :

EID, DateVisited ,DateTested,   PossibleDups
A1 ,2012-01-01 ,2012-01-08 ,Y
A1 ,2012-01-03 ,2012-01-21, N
A1 ,2012-01-09, 2012-01-28 ,Y
A1 ,2012-01-30 ,2012-01-29 ,N

hope I have put my req clear,

Appreciate any help on this.

Thanks,

John

---

John

Answer 1

Here you go:

DECLARE @TEMP Table (EID VARCHAR(5), DateVisited Date, DateTested Date)
INSERT INTO @TEMP VALUES ('A1','1/1/2012','1/8/2012')
INSERT INTO @TEMP VALUES ('A1','1/3/2012','1/21/2012')
INSERT INTO @TEMP VALUES ('A1','1/9/2012','1/28/2012')
INSERT INTO @TEMP VALUES ('A1','1/30/2012','1/29/2012')
INSERT INTO @TEMP VALUES ('B1','3/1/2012','7/8/2012')
INSERT INTO @TEMP VALUES ('B1','5/2/2012','7/9/2012')
INSERT INTO @TEMP VALUES ('B1','9/1/2012','9/8/2012')
INSERT INTO @TEMP VALUES ('C1','2/1/2012','2/2/2012')
INSERT INTO @TEMP VALUES ('C1','2/3/2012','11/8/2012')
INSERT INTO @TEMP VALUES ('D1','1/10/2012','4/8/2012')
INSERT INTO @TEMP VALUES ('D1','1/15/2012','4/8/2012')
INSERT INTO @TEMP VALUES ('D1','1/17/2012','8/8/2012')
INSERT INTO @TEMP VALUES ('D1','1/11/2012','1/8/2012')


SELECT O.EID, O.DateVisited, O.DateTested, ISNULL(PossibleDupes, 'N')

FROM @Temp O
OUTER APPLY 
(SELECT 'Y' PossibleDupes, O.DateVisited DtOuter, DateVisited DtInner FROM @Temp 
WHERE EID = O.EID AND ((DATEDIFF(DD, O.DateVisited, DateVisited) > = -2 AND DATEDIFF(DD, O.DateVisited, DateVisited) < = 2) AND O.DateVisited <> DateVisited AND DateVisited > O.DateVisited) 
OR 
((DATEDIFF(DD, O.DateTested, DateTested) > = -2 AND DATEDIFF(DD, O.DateTested, DateTested) < = 2) AND O.DateTested <> DateTested AND DateTested > O.DateTested))R
ORDER BY EID

---

Please mark as answer if this answers your question. Please mark as helpful if you found this post was helpful.

Answer 2

SELECT *,CASE WHEN DATEDIFF(d,DateVisited,DateTested)<=2 THEN 'Y' ELSE 'N' END PossibleDups
FROM @TEMP

---

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

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 3

looks like not working fine... almost close but not giving correct Results for Possibledups.. Ihave tested for below data

INSERT INTO @TEMP VALUES ('F1','1/1/2012','2/8/2012')
INSERT INTO @TEMP VALUES ('F1','12/31/2011','2/8/2012')
INSERT INTO @TEMP VALUES ('F1','9/9/2012','7/19/2012')
INSERT INTO @TEMP VALUES ('F1','9/8/2012','7/24/2012')

output is -

           EID  DateVisited   DateTested    (Possible Dups)
row 1  F1   2012-01-01   2012-02-08      N
row 2  F1   2011-12-31   2012-02-08      Y
row 3  F1   2012-09-09   2012-07-19     N
row 4  F1   2012-09-08   2012-07-24     Y

from above F1 employee - row 1

Datevisited is -2 between row 1 and row 2 --so it should populate Y or DateTested for Row 1 is same as Row 2 still has to be Y

similarly

for row 3 -- DateVisited is -1 between Row 4 --this should come as Y and

row 4 should be N since Datevisited is takencare in row 3 as Y

hope I am clear,

Thanks

---

John

Answer 4

Well, that can be changed, however, if you want both rows to show a 'Y', then how is it that in your first post, you mentioned a Y for 

INSERT INTO @TEMP VALUES ('A1','1/1/2012','1/8/2012') and an 'N' for: INSERT INTO @TEMP VALUES ('A1','1/3/2012','1/21/2012') Shouldn't they both say 'Y' as per your most recent post?

---

Please mark as answer if this answers your question. Please mark as helpful if you found this post was helpful.

Answer 5

Dear SQL Novice 01 ,

Bacause  1/1/2012 has 1/3/2012 below+2, so A1 ,2012-01-01 ,2012-01-08 ,Y

A1 ,2012-01-01 ,2012-01-08 ,Y
A1 ,2012-01-03 ,2012-01-21, N

and

A1 ,2012-01-03 ,2012-01-21, ----- is taken care in above A1 ,2012-01-01 ,2012-01-08 ,----  so this still stays N

and

A1 ,2012-01-09, 2012-01-28 ,Y has Datetested in next record to A1 ,2012-01-30 ,2012-01-29  --so this will be Y

and

A1 ,2012-01-30 ,2012-01-29  since Datetested is taken care in last record A1 ,2012-01-09, 2012-01-28 ,Y, this will stay N

hope I am clear

---

John

Answer 6

John,

  Whats confusing here is this:

A1 2012-01-01  2012-01-08  should be 'Y'

A1 2012-01-03  2012-01-21  should be 'N' because, Datevisited has already been considered. So, in essence, we are moving forward.

Applying the same logic here for F1:

row 1  F1   2012-01-01   2012-02-08      N
row 2  F1   2011-12-31   2012-02-08      Y

forward The 1st Date i.e, DateVisited for F1 is 2011-12-31, though, according to you it is row 2, there is no field supplied for the order and so, SQL has to go in an (some/any) order if a Row_number is to be assigned, in which case, your Row2 would come first in the order of ascending. And hence, Row 2 is taken care of with a 'Y' as the date diff is just one day for datevisited. However, you can also, argue the case that DateTested has been checked against DateTested of the next row (since both are same and hence the 'Y'), And in the next row, since, this row has already been checked (As you mentioned above), this will be an 'N'. What I'm trying to get at, is to know what happens, when this occurs:

G1  2012-03-24   2012-08-21

G1 2012-03-23 2012-08-22

Would you say 'Y' AND 'Y'? But each of them once considered, cannot be considered again right? And gets more confusing when its more rows like this. I apologize, if this has confused you more, that is certainly not the intention here.

---

Please mark as answer if this answers your question. Please mark as helpful if you found this post was helpful.

Answer 7

G1  2012-03-24   2012-08-21

G1 2012-03-23    2012-08-22

Yes True, So we will say Just Y one time.

Thanks Novice!

---

John