How best to count occurrences of values for column

[James]

We have a TimeCard table like so: (stripped down example)

CREATE TABLE [dbo].[TimeCard](
[pk] [int] IDENTITY(1,1) NOT NULL,
[employee] [int] NOT NULL,
[punchIn] [datetime] NOT NULL,
[punchOut] [datetime] NULL,
[TardyType] [int] NOT NULL,
)

And the last column 'TardyType' can be one of these (0 = Not Tardy, 1
= Tardy, 2 = Tardy but Approved in Advance, 3 = Other)

I am trying to create a View to query the data, By Employee, For a
given time period (punchIn date range) and get the number of
occurrences of each value for each employee over the time period sort
of like this

Employee Type0 Type1 Type2 Type3
-------------- ------- ------- ------- -------
1 10 2 3 0
2 8 3 4 0
etc...

**** NOTE: This is an SQL Server 2000 database for now


Ideas?

Thanks in advance

JIM

[Erland Sommarskog]

Post by James
We have a TimeCard table like so: (stripped down example)
CREATE TABLE [dbo].[TimeCard](
[pk] [int] IDENTITY(1,1) NOT NULL,
[employee] [int] NOT NULL,
[punchIn] [datetime] NOT NULL,
[punchOut] [datetime] NULL,
[TardyType] [int] NOT NULL,
)
And the last column 'TardyType' can be one of these (0 = Not Tardy, 1
= Tardy, 2 = Tardy but Approved in Advance, 3 = Other)
I am trying to create a View to query the data, By Employee, For a
given time period (punchIn date range) and get the number of
occurrences of each value for each employee over the time period sort
of like this
Employee Type0 Type1 Type2 Type3
-------------- ------- ------- ------- -------
1 10 2 3 0
2 8 3 4 0
etc...

SELECT Employee,
Type0 = SUM(CASE TardyType WHEN 0 THEN 1 ELSE 0 END),
Type1 = SUM(CASE TardyType WHEN 1 THEN 1 ELSE 0 END),
Type2 = SUM(CASE TardyType WHEN 2 THEN 1 ELSE 0 END),
Type3 = SUM(CASE TardyType WHEN 3 THEN 1 ELSE 0 END)
FROM TimeCard
GROUP BY Employee

[James]

Post by Erland Sommarskog

Post by James
We have a TimeCard table like so: (stripped down example)
CREATE TABLE [dbo].[TimeCard](
     [pk] [int] IDENTITY(1,1) NOT NULL,
     [employee] [int] NOT NULL,
     [punchIn] [datetime] NOT NULL,
     [punchOut] [datetime] NULL,
     [TardyType] [int] NOT NULL,
)
And the last column 'TardyType' can be one of these (0 = Not Tardy, 1
= Tardy, 2 = Tardy but Approved in Advance, 3 = Other)
I am trying to create a View to query the data, By Employee, For a
given time period (punchIn date range) and get the number of
occurrences of each value for each employee over the time period sort
of like this
Employee  Type0  Type1 Type2 Type3
--------------   -------    -------   -------  -------
1                10        2        3      0
2                 8         3        4      0
etc...

SELECT Employee,
       Type0 = SUM(CASE TardyType WHEN 0 THEN 1 ELSE 0 END),
       Type1 = SUM(CASE TardyType WHEN 1 THEN 1 ELSE 0 END),
       Type2 = SUM(CASE TardyType WHEN 2 THEN 1 ELSE 0 END),
       Type3 = SUM(CASE TardyType WHEN 3 THEN 1 ELSE 0 END)
FROM   TimeCard
GROUP  BY Employee
--
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Well that was easy !. Thanks. I hadn't thought of the CASE
statement. Doh !

JIM