COUNT on dates - SQL Server Forums

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

Transact-SQL (2000)

COUNT on dates

Author

Topic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-16 : 19:27:41

I've got two solutions for my problem, but not sure if there's a better way. I need to count up the number of dates there are for a CardId. But I only want to count one, so if there's more than one row for a CardId and a date, I only want one. Here's the sample and my two solutions:



SET NOCOUNT ON

DECLARE @Event TABLE(CardId int NOT NULL, TimeDate datetime NOT NULL)

INSERT INTO @Event
SELECT 475, '12-01-2004 05:00' UNION ALL
SELECT 475, '12-01-2004 07:00' UNION ALL
SELECT 475, '12-01-2004 08:00' UNION ALL
SELECT 475, '12-02-2004 12:00' UNION ALL
SELECT 475, '12-02-2004 15:00' UNION ALL
SELECT 475, '12-02-2004 21:00' UNION ALL
SELECT 475, '12-03-2004 05:00' UNION ALL
SELECT 475, '12-04-2004 05:00' UNION ALL
SELECT 475, '12-05-2004 05:15' UNION ALL
SELECT 475, '12-06-2004 05:21' UNION ALL
SELECT 476, '12-01-2004 05:00' UNION ALL
SELECT 476, '12-06-2004 07:33' UNION ALL
SELECT 477, '11-30-2004 18:22' 

SELECT CardId, COUNT(TimeDate) AS DateCount
FROM 
(
	SELECT CardId, CONVERT(varchar(10), TimeDate, 101) AS TimeDate
	FROM @Event
	GROUP BY CardId, CONVERT(varchar(10), TimeDate, 101)
) t
GROUP BY CardId

SELECT CardId, COUNT(TimeDate) AS DateCount
FROM 
(
	SELECT DISTINCT CardId, CONVERT(varchar(10), TimeDate, 101) AS TimeDate
	FROM @Event
) t
GROUP BY CardId

I don't like the GROUP BY solution as it's not using an aggregate function. I don't like the DISTINCT way either, not sure why though it just doesn't seem right. Is there a better solution for this? Here's the expected result set:



CardId      DateCount   
----------- ----------- 
475         6
476         2
477         1

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-16 : 19:58:33

select CardId, DateCount = count(distinct convert(varchar(8),TimeDate,112))
FROM @Event
GROUP BY CardId

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-16 : 20:08:03

Thanks Nigel. I believe it's equivalent to my second solution, but yours is shorter so it wins!

Tara

Kristen
Test

22859 Posts

Posted - 2004-12-17 : 02:47:37

DATEDIFF(Day, 0, TimeDate) may be faster than convert(varchar(8),TimeDate,112)) - if you have truckloads to do / often.

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-17 : 12:09:43

Isn't that just in the WHERE clause though?

Tara

Kristen
Test

22859 Posts

Posted - 2004-12-18 : 02:59:22

In a SELECT it may be marginal because it has little to process, because normally you wouldn't return shedloads of rows, but I would guess [dangerous that!] that in COUNT(DISTINCT MyThingie) some sort of temporary storage needs to be made of "MyThingie"s to sort/count them. Thus speeding the conversion of data should be a benefit [assuming there are plenty of rows in the underlying data].

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources