Finding records that fall with a date range of eachother 2

[jbradleyharris]

I built a query against a SQL server to pull information about accounts that have multiple records in 2008. Now I need to isolate the accounts where those multiple records fall within five days of eachother. I'm no database programmer. I think I should be able to do something with DateAdd to figure this out but so far I'm drawing a blank as to just how to go about it. I would greatly appreciate it if someone could help me out with this. Here is my original query:

[b][blue]
 SELECT IMEV_ACS.dbo.IMAGERECORDS.RECID, 
	   IMEV_ACS.dbo.IMAGERECORDS.DATEADDED, 
	   IMEV_ACS.dbo.IMAGERECORDS.ACCOUNT_NUMBER, 
	  IMEV_MASTER.dbo.IMAGEPATHS.FolderName 
FROM IMEV_MASTER.dbo.IMAGEPATHS INNER JOIN 
	    IMEV_ACS.dbo.IMAGERECORDS ON IMEV_MASTER.dbo.IMAGEPATHS.PATHID = IMEV_ACS.dbo.IMAGERECORDS.PATHID 
WHERE (LEN(IMEV_ACS.dbo.IMAGERECORDS.ACCOUNT_NUMBER) = 16) 
	AND (IMEV_ACS.dbo.IMAGERECORDS.DATEADDED BETWEEN '2008-01-01' AND '2009-01-01') 
	AND (IMEV_ACS.dbo.IMAGERECORDS.UNIT LIKE 'Fee Disput%' 
		OR IMEV_ACS.dbo.IMAGERECORDS.UNIT LIKE 'OHIO%' 
		OR IMEV_ACS.dbo.IMAGERECORDS.UNIT = 'EXE CORR' 
		OR IMEV_ACS.dbo.IMAGERECORDS.UNIT = 'EXE CORR COMPLTS') 
	AND IMEV_ACS.dbo.IMAGERECORDS.ACCOUNT_NUMBER IN 
		(SELECT IMEV_ACS.dbo.IMAGERECORDS.ACCOUNT_NUMBER 
		FROM IMEV_ACS.dbo.IMAGERECORDS 
		WHERE (LEN(IMEV_ACS.dbo.IMAGERECORDS.ACCOUNT_NUMBER) = 16) 
			AND (IMEV_ACS.dbo.IMAGERECORDS.DATEADDED BETWEEN '2008-01-01' AND '2009-01-01') 
			AND (IMEV_ACS.dbo.IMAGERECORDS.UNIT LIKE 'Fee Disput%' 
				OR IMEV_ACS.dbo.IMAGERECORDS.UNIT LIKE 'OHIO%' 
				OR IMEV_ACS.dbo.IMAGERECORDS.UNIT = 'EXE CORR' 
				OR IMEV_ACS.dbo.IMAGERECORDS.UNIT = 'EXE CORR COMPLTS') 
		GROUP BY IMEV_ACS.dbo.IMAGERECORDS.ACCOUNT_NUMBER 
		HAVING Count(IMEV_ACS.dbo.IMAGERECORDS.ACCOUNT_NUMBER)>1)
[/blue][/b]

Where do I go from here?

 

[gmmastros]

I would probably use a with block for your original query, and then do a self join, like this:

;With Original As
(
 SELECT IMEV_ACS.dbo.IMAGERECORDS.RECID, 
       IMEV_ACS.dbo.IMAGERECORDS.DATEADDED, 
       IMEV_ACS.dbo.IMAGERECORDS.ACCOUNT_NUMBER, 
      IMEV_MASTER.dbo.IMAGEPATHS.FolderName 
FROM IMEV_MASTER.dbo.IMAGEPATHS INNER JOIN 
        IMEV_ACS.dbo.IMAGERECORDS ON IMEV_MASTER.dbo.IMAGEPATHS.PATHID = IMEV_ACS.dbo.IMAGERECORDS.PATHID 
WHERE (LEN(IMEV_ACS.dbo.IMAGERECORDS.ACCOUNT_NUMBER) = 16) 
    AND (IMEV_ACS.dbo.IMAGERECORDS.DATEADDED BETWEEN '2008-01-01' AND '2009-01-01') 
    AND (IMEV_ACS.dbo.IMAGERECORDS.UNIT LIKE 'Fee Disput%' 
        OR IMEV_ACS.dbo.IMAGERECORDS.UNIT LIKE 'OHIO%' 
        OR IMEV_ACS.dbo.IMAGERECORDS.UNIT = 'EXE CORR' 
        OR IMEV_ACS.dbo.IMAGERECORDS.UNIT = 'EXE CORR COMPLTS') 
    AND IMEV_ACS.dbo.IMAGERECORDS.ACCOUNT_NUMBER IN 
        (SELECT IMEV_ACS.dbo.IMAGERECORDS.ACCOUNT_NUMBER 
        FROM IMEV_ACS.dbo.IMAGERECORDS 
        WHERE (LEN(IMEV_ACS.dbo.IMAGERECORDS.ACCOUNT_NUMBER) = 16) 
            AND (IMEV_ACS.dbo.IMAGERECORDS.DATEADDED BETWEEN '2008-01-01' AND '2009-01-01') 
            AND (IMEV_ACS.dbo.IMAGERECORDS.UNIT LIKE 'Fee Disput%' 
                OR IMEV_ACS.dbo.IMAGERECORDS.UNIT LIKE 'OHIO%' 
                OR IMEV_ACS.dbo.IMAGERECORDS.UNIT = 'EXE CORR' 
                OR IMEV_ACS.dbo.IMAGERECORDS.UNIT = 'EXE CORR COMPLTS') 
        GROUP BY IMEV_ACS.dbo.IMAGERECORDS.ACCOUNT_NUMBER 
        HAVING Count(IMEV_ACS.dbo.IMAGERECORDS.ACCOUNT_NUMBER)>1)
)
Select A.ACCOUNT_NUMBER
From   Original As A
       Inner Join Original As B
         On  A.ACCOUNT_NUMBER = B.ACCOUNT_NUMBER
         And A.DATEADDED < B.DATEADDED
         And DateDiff(Day, A.DATEADDED, B.DATEADDED) < 5

I haven't tested this, but it should be close to what you are looking for. This will only work if you are using SQL2005 or newer.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

Something small, but sometimes it is the simple things that get us in trouble.

You specified "within five days of eachother". And George's example is specifying "DateDiff(Day, A.DATEADDED, B.DATEADDED)[red] < [/red]5", you may want to change this to <= 5.

BTW, excellent example George. I have only used With clauses twice and both times found examples of yours that solved the issue.

Thanks

John Fuhrman

 

[jbradleyharris]

George and John,

Thank you both very much for the help. Stars to you both

Brad