Using Count function on a uniqueidentifier field - SQL

SELECT COUNT(D.DOCUMENTID) AS DocCount, P.TITLE
FROM DOCUMENT AS D inner join
PROJECT AS P ON D.PROJECTID = P.PROJECTID
GROUP BY P.PROJECTID, P.TITLE

When running this query I encountered the following error:

Msg 409, Level 16, State 2, Line 1
The count aggregate operation cannot take a uniqueidentifier data type as an argument.

   
To avoid this error when trying to Count a uniqueidetifier field you have to CAST it into a char :
 
SELECT COUNT(CAST(D.DOCUMENTID AS char(36))) AS DocCount, P.TITLE
FROM DOCUMENT AS D inner join
PROJECT AS P ON D.PROJECTID = P.PROJECTID
GROUP BY P.PROJECTID, P.TITLE

Auxiliary Calendar table - SQL

This code is derived from a useful article by Todd Fifield at SQLServerCentral.com and has been modified to include columns based on the ISO8601 date and times standard (here and here).

IF OBJECT_ID('dbo.CalendarDay') IS NOT NULL
DROP TABLE dbo.CalendarDay;
GO

CREATE TABLE CalendarDay
( DayID INT IDENTITY(1, 1)
, DayDate SMALLDATETIME
, WeekNumber INT
, OrdinalDate VARCHAR(8)
, DateWeekNumber VARCHAR(10) 
, DayNumber INT
, NameOfDay VARCHAR(10) 
)
GO
SET NOCOUNT ON;
DECLARE
@Date SMALLDATETIME;
SET DATEFIRST 1; --assuming start of week is monday
SET @Date = '2010-01-01';
WHILE @Date < '2011-01-01'
BEGIN
INSERT INTO CalendarDay
( DayDate, WeekNumber, OrdinalDate, DateWeekNumber, DayNumber, NameOfDay )
SELECT @Date
, (DATEPART(WK, @Date))
, CAST(DATEPART(YEAR, @Date) as VARCHAR) + '-' + CAST(DATEPART(DY, @Date) as VARCHAR)
, CAST(DATEPART(YEAR, @Date) as VARCHAR) + '-'
+ 'W' + CAST(DATEPART(WK, @Date) as VARCHAR) + '-'
+ CAST(DATEPART(WEEKDAY, @Date) as VARCHAR)
, DATEPART(WEEKDAY, @Date)
, CASE DATEPART(WEEKDAY, @Date)
WHEN 1 THEN 'Monday'
WHEN 2 THEN 'Tuesday'
WHEN 3 THEN 'Wednesday'
WHEN 4 THEN 'Thursday'
WHEN 5 THEN 'Friday'
WHEN 6 THEN 'Saturday'
ELSE 'Sunday' END;
SET @Date = DATEADD(day, 1, @Date);
END
GO