SELECT ID_NR, SUM([#]) AS [#] FROM (

SELECT DISTINCT ID_NR, ID_NR_p, CASE WHEN KZ = 'v' THEN 1 ELSE 0 END AS [#]

FROM Table1) AS d

GROUP BY ID_NR

ORDER BY ID_NR

See this article how to produce simple dates

http://www.sqlservercentral.com/columnists/plarsson/howmanymoremondaysuntiliretire.asp

An INTEGER is 4 bytes. A smalldatetime is also 4 bytes.

There is no change in storage if you decide to convert INT to SMALLDATETIME.

exec

SELECT ID_NR,

SUM(CASE WHEN KZ = 'v' THEN 1 ELSE 0 END) AS [#]

FROM Table1

GROUP BY ID_NR

ORDER BY ID_NR

If you are using SQL Server 2005, using FOR XML PATH is faster

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Use the 64-bit reverse thingy above and then the function I posted in the link earlier.

Also see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=87871

CTE's

SELECT

 YEAR(Date), MONTH(Date),

 SUM(Usage) AS TotalUsage

FROM

 UBAccountHistory 

WHERE  

 RecordType IN (5) AND ServiceCode = ? AND Date >= DATEADD(MONTH, -@X, CURRENT_TIMESTAMP)

GROUP BY 

 YEAR(Date), MONTH(Date)

ORDER BY 

 YEAR(Date), MONTH(Date)

Here is an article about that

http://www.sqlservercentral.com/columnists/plarsson/howmanymoremondaysuntiliretire.asp

Jeff, that would be

... ISDATE(...) = 0

right?

declare

@a datetime,

@b-2 datetime,

@c datetime

select

@a = '20070820'