Count of unique values based on growing list

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 2008 Forums

Transact-SQL (2008)

Count of unique values based on growing list

Author

Topic

telynch25
Starting Member

9 Posts

Posted - 2010-05-04 : 14:38:14

Hello, I'm new to this forum, but could really use some input. I've been trying to wrap my head around this one for a few days...and still banging my head on the desk. It shouldn't be so hard...

I have a sample table below, and I need to query for Server "A" the count of the new unique ServerVulnId for each ListDate, but as the ListDate is changed, the Count needs to then look at the prior month as well.


SAMPLE DATA
ServerName  ListDate     ServerVulnId  EntryDate
a           2010-03-01   222           2010-03-01
a           2010-03-01   333           2010-03-01
a           2010-04-01   222           2010-03-01
a           2010-04-01   444           2010-03-01
a           2010-04-01   555           2010-03-01
a           2010-05-01   333           2010-03-01
a           2010-05-01   444           2010-03-01
a           2010-05-01   666           2010-03-01

So, 3/1/2010 has 0 since it's EntryDate is the same as ListDate, so nothing new...the 4/1/2010 has 2 new (444, 555) where not in the 3/1 listing and then 5/1/2010 has 1 new (666) which wasn't present in the 3/1 or 4/1 listings.


SAMPLE OUTPUT
ServerName  ListDate     NewActivities
a           2010-03-01   0
a           2010-04-01   2
a           2010-05-01   1

Any ideas? TIA for the thoughts. :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 14:55:24

[code]
SELECT ServerName,
ListDate,
CASE WHEN ListDate=EntryDate THEN 0 ELSE t1.Cnt END AS NewActivities
FROM Table t
OUTER APPLY (SELECT COUNT(DISTINCT ServerVulnId) AS Cnt
FROM (SELECT MIN(ListDate) OVER (PARTITION BY ServerVulnId) AS MinOccur,ServerVulnId,ListDate
FROM Table
WHERE ServerName = t.ServerName
AND ListDate = t.ListDate
)r
WHERE MinOccur = ListDate
)t1

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

telynch25
Starting Member

9 Posts

Posted - 2010-05-04 : 16:44:19

Vishak,

Thanks for the quick reply. I'm putting it in, but it's not returning the results expected.

Using what you created as a basis, the below is what I created and its working. So, THANK YOU for pointing me in the right direction! :)


SELECT
 ServerName
,ListDate
,CASE WHEN ListDate <= DateAdd(MONTH, -1, EntryDate) THEN 0
	  ELSE Cnt
 END AS 'New Activities'
FROM #tempData al
OUTER APPLY (
		SELECT COUNT(DISTINCT DistinctServerVulnId) AS Cnt
		FROM
		(
			SELECT
			 MinOccur
			,DistinctServerVulnId
			FROM
			(
				SELECT 
				 MIN(ListDate) OVER (PARTITION BY DistinctServerVulnId) AS MinOccur
				,ListDate
				,DistinctServerVulnId
				FROM #tempData
				WHERE ServerName = al.ServerName
			)r
			WHERE MinOccur = al.ListDate
			GROUP BY MinOccur, DistinctServerVulnId
		)x
	) y
GROUP BY ServerName, ListDate, EntryDate, Cnt
ORDER BY ServerName, ListDate

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-04 : 16:58:27

Try this too.

select ServerName,ListDate,SUM(case when ListDate = EntryDate then 0 else 1 end)
from 
(
select row_number() over(partition by ServerVulnId order by ListDate) as rn,* from @t
) a
where a.rn = 1
group by ServerName,ListDate

Here's Sample data and Result

declare @t table 
(ServerName varchar(1), ListDate datetime,     ServerVulnId int,  EntryDate datetime)
insert @t
select 'a' ,          '2010-03-01',   222,           '2010-03-01'
union all select 'a',           '2010-03-01',   333 ,          '2010-03-01'
union all select 'a',          '2010-04-01',   222,           '2010-03-01'
union all select 'a',           '2010-04-01',   444,           '2010-03-01'
union all select 'a' ,          '2010-04-01',   555,           '2010-03-01'
union all select 'a',           '2010-05-01',   333,           '2010-03-01'
union all select 'a',           '2010-05-01',   444,           '2010-03-01'
union all select 'a' ,          '2010-05-01',   666,           '2010-03-01'

Result

ServerName ListDate                Cnt
---------- ----------------------- -----------
a          2010-03-01 00:00:00.000 0
a          2010-04-01 00:00:00.000 2
a          2010-05-01 00:00:00.000 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources