SQL Complex Query

Question

question

Adam Owen asked Jul 1, '10

SQL Complex Query

Hello

Just wondering if anyone can help me with this Query im trying to write.

I have Visitor logging Database and would like to return all Visitors who visited 5 times or more Since May 2009, showing their name, address and postcode.

So Far i have Written

SELECT DISTINCT TOP (100) PERCENT dbo.Visit.VisitDate, dbo.Members.Forename, dbo.Members.Surname, dbo.Members.PostCode, dbo.Members.StreetAddress FROM dbo.Visit INNER JOIN dbo.Members ON dbo.Visit.MemberID = dbo.Members.MemberID WHERE (dbo.Visit.VisitDate >= CONVERT(DATETIME, '2009-05-01 00:00:00', 102)) GROUP BY dbo.Members.Forename, dbo.Members.Surname, dbo.Members.MemberID, dbo.Members.StreetAddress, dbo.Members.PostCode, dbo.Visit.VisitDate ORDER BY dbo.Visit.VisitDate

Which returns nearly what i need however shows singular visits made by the users

any help would be appreciated

Thanks

query

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-07-01T20:18:36Z

Scot Hauder answered Jul 1, '10

            
SELECT d.Forename,             
d.Surname,             
d.PostCode,             
d.StreetAddress            
FROM            
(SELECT v.VisitDate,             
        m.Forename,             
        m.Surname,             
        m.PostCode,             
        m.StreetAddress,            
        rn = ROW_NUMBER() OVER (PARTITION BY m.MemberID ORDER BY v.VisitDate)            
FROM dbo.Visit v            
JOIN dbo.Members m ON (v.MemberID = m.MemberID)             
WHERE v.VisitDate >= '2009-05-01')d            
WHERE rn = 5

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question