Avg count of cases per day

USE [GKHELPDESK]
GO

/****** Object: Table [dbo].[CaseData1] Script Date: 10/13/2012 10:53:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[CaseData1](
[Id] [int] IDENTITY(100,1) NOT NULL,
[CaseDate] [date] NOT NULL,
[Customer] [varchar](50) NOT NULL,
[Category] [int] NULL,
[CaseDescription] [varchar](30) NOT NULL,
[CaseStatus] [bit] NOT NULL,
[ClosedDate] [date] NULL,
[Resolution] [varchar](400) NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT INTO [GKHELPDESK].[dbo].[CaseData1]
([CaseDate]
,[Customer]
,[Category]
,[CaseDescription]
,[CaseStatus]
,[ClosedDate]
,[Resolution])
VALUES
('20120101', N'Joe Smith', 1, N'Outlook Error', 1, '20120101', N'Close outlook Reopen Outlook')
,('20120102', N'Jim Smith', 1, N'Windows Error', 1, '20120102', N'Restart computer')
,('20120103', N'James Smith', 1, N'ERP Error', 1, '20120103', N'Close application reopen app')
,('20120103', N'Jessie Smith', 1, N'Printer Error', 1, '20120103', N'Power cycle printer');
GO

CELKO (10/13/2012)

In particular, do you know how to properly create a “problem_category” encoding scheme? It is usually a hierarchy (a Dewey Decimal of Disaster, as we call them)? Do you know how to properly create a “problem_status” encoding scheme? How were you going to get “resolved by ticket withdrawnal”, “resolved by replacement”, and about 50 other status codes in one BIT?

Another hint; in the old days we put commas in the front of the punch cards with one field name on it so we could re-arrange the program deck or save the cards for re-use. You can stop now; a line of program text should be related data elements (city, state, ZIP) that are read and understood by humans as a unit of data or logic.

No. Your mindset is still back in magnetic tapes and procedural programming. Use a Report Period Table. Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML. The report period table gives a name to a range of dates that is common to the entire enterprise.

CREATE TABLE Report_Periods
(report_name VARCHAR(30) NOT NULL PRIMARY KEY,
report_start_date DATE NOT NULL,
report_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (report_start_date <= report_end_date),
etc);

I would also build a calendar table with a ISO-8601 week-within-year format column in addition to the usual Common Era date. This format is 'yyyyWww-d' where yyyy is the year, W is as separator token, ww is (01-53) week number and d is (1-7) day of the week.

You input any calendar date, find the week-within-year column and return the dates that match on a LIKE predicate.
WHERE sale_day LIKE '2012W26-[67]'

CREATE FUNCTION [dbo].[LocalCalendar]
(@pStartDate DATE, @pEndDate DATE)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN

WITH numbers AS (
SELECT TOP(10000)
n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1
FROM
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) g (n))
SELECT
cal_date,
week_in_year_date = [Year] + 'W' + [Week] + '-' + [weekday],
period_name = [Year] + 'Q' + [Quarter]
FROM (
SELECT
cal_date,
[Year] = CAST(YEAR(cal_date) AS CHAR(4)),
[Quarter] = CAST(DATEPART(quarter,cal_date) AS CHAR(1)),
[Week] = RIGHT('0'+CAST(DATEPART(WEEK,cal_date) AS VARCHAR(2)),2),
[weekday] = CAST(DATEPART(weekday,cal_date) AS CHAR(1))
FROM (SELECT cal_date = CAST(DATEADD(DAY,N,'2000/01/01') AS DATE) FROM numbers) d
) f
WHERE (@pStartDate IS NULL OR cal_date >= @pStartDate)
AND (@pEndDate IS NULL OR cal_date <= @pEndDate)

DROP TABLE JoesCalendar
CREATE TABLE JoesCalendar (
cal_date DATE PRIMARY KEY CLUSTERED,
week_in_year_date CHAR(9) NOT NULL
CHECK (week_in_year_date
LIKE '[12][0-9][0-9][0-9]W[0-5][0-9]-[1-7]'),
period_name CHAR(6) NOT NULL)

INSERT INTO JoesCalendar (cal_date, week_in_year_date, period_name)
SELECT * FROM [dbo].[LocalCalendar] ('20100101','20141231')

ALTER FUNCTION [dbo].[LocalCalendar]
(@pStartDate DATE, @pEndDate DATE)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN

WITH numbers AS (
SELECT TOP(10000)
n = ISNULL(ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,0)
FROM
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) g (n))
SELECT
cal_date,
week_in_year_date = [Year] + 'W' + [Week] + '-' + [weekday],
period_name = [Year] + 'Q' + [Quarter]
FROM (
SELECT
cal_date,
[Year] = CAST(YEAR(cal_date) AS CHAR(4)),
[Quarter] = CAST(DATEPART(quarter,cal_date) AS CHAR(1)),
[Week] = RIGHT('0'+CAST(DATEPART(WEEK,cal_date) AS VARCHAR(2)),2),
[weekday] = CAST(DATEPART(weekday,cal_date) AS CHAR(1))
FROM (SELECT TOP (ISNULL(1+DATEDIFF(day,@pStartDate,@pEndDate),0)) cal_date = CAST(DATEADD(DAY,n,@pStartDate) AS DATE) FROM numbers) d
) f
WHERE (@pStartDate IS NULL OR cal_date >= @pStartDate)
AND (@pEndDate IS NULL OR cal_date <= @pEndDate)

PRINT ''
PRINT '== iTVF calendar function ========='
SET STATISTICS TIME ON
SELECT *
FROM [dbo].[LocalCalendar] ('20110101','20111231')
SET STATISTICS TIME OFF
PRINT '==================================='

PRINT ''
PRINT '== calendar table ================='
SET STATISTICS TIME ON
SELECT *
FROM JoesCalendar
WHERE cal_date BETWEEN CAST('20110101' AS DATE) AND CAST('20111231' AS DATE)
SET STATISTICS TIME OFF
PRINT '==================================='

== iTVF calendar function =========

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 3 ms.
===================================

== calendar table =================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 109 ms.
===================================

WITH numbers AS (
SELECT TOP(10000)
n = ISNULL(ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1,0)
FROM
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) g (n))

SELECT
cal_date,
[Year] = CAST(YEAR(cal_date) AS CHAR(4)),
[Quarter] = CAST(DATEPART(quarter,cal_date) AS CHAR(1)),
[Week] = RIGHT('0'+CAST(DATEPART(WEEK,cal_date) AS VARCHAR(2)),2),
[weekday] = CAST(DATEPART(weekday,cal_date) AS CHAR(1))