Count duplicate values in daterange

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Count duplicate values in daterange

Author

Topic

Humate
Posting Yak Master

101 Posts

Posted - 2007-12-07 : 16:26:06

Hi,

I have a table I would like to query, but I can't get my head around the code required.

In the first column I have date values, in the second I have a phone number. There are many phone numbers for each date in the table, and it is listed as follows:

Date-----------------Number
12/07/2007----------43214526
12/07/2007----------65677480
12/07/2007----------43214526
12/08/2007----------13537894
12/08/2007----------43214526

I would like my query to show each date in the table, with a count of phone number in the second column. Now the tricky part. In the third column I want to count unique numbers, that are not just unique for that day, but not duplicated in the past 7 day date range (for each day). Below is a very small example, mine is with hundreds of rows.

Date--------------Count---------Unique
12/07/2007----------3-------------2
12/08/2007----------2-------------1

Is this possible? Any help much appreciated.

Thanks in advance
Humate

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-07 : 18:39:37

[code]-- Prepare sample data
DECLARE @Sample TABLE ([Date] DATETIME, Number INT)

INSERT @Sample
SELECT '12/07/2007', 43214526 UNION ALL
SELECT '12/07/2007', 65677480 UNION ALL
SELECT '12/07/2007', 43214526 UNION ALL
SELECT '12/08/2007', 13537894 UNION ALL
SELECT '12/08/2007', 43214526

-- Show the expected output
SELECT [Date],
COUNT(*) AS [Count],
COUNT(DISTINCT [Unique]) AS [Unique]
FROM (
SELECT s.[Date],
CASE
WHEN EXISTS (SELECT * FROM @Sample AS t WHERE t.[Date] BETWEEN DATEADD(DAY, -6, s.[Date]) AND DATEADD(DAY, -1, s.[Date]) AND t.Number = s.Number) THEN NULL
ELSE s.Number
END AS [Unique]
FROM @Sample AS s
) AS x
GROUP BY [Date]
ORDER BY [Date][/code]

E 12°55'05.25"
N 56°04'39.16"

Humate
Posting Yak Master

101 Posts

Posted - 2007-12-08 : 16:21:38

Wow! Still trying to understand how it works, but I like it. I'll try with my real data.

Many thanks Peso!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources