SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE DATABASE Calendar
go
USE Calendar
go
CREATE TABLE Numbers (Num int identity(1, 1) CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED)
SET NOCOUNT ON
INSERT Numbers DEFAULT VALUES
WHILE Scope_Identity() < 10000 INSERT Numbers DEFAULT VALUES
CREATE TABLE ClientCal (
Client varchar(100),
Start datetime,
Title varchar(100),
BCount int,
CONSTRAINT PK_ClientCal PRIMARY KEY CLUSTERED (Client, Start, Title)
)
INSERT ClientCal VALUES ('Bob','20040201','Send Reports' ,1)
INSERT ClientCal VALUES ('Bob','20040212','Chase Reports',1)
INSERT ClientCal VALUES ('Sid','20040203','Send Reports' ,1)
INSERT ClientCal VALUES ('Sid','20040212','Chase Reports',1)
INSERT ClientCal VALUES ('Joe','20040303','Send Reports' ,1)
INSERT ClientCal VALUES ('Joe','20040312','Chase Reports',1)
INSERT ClientCal VALUES ('Sally','20040302','Send Reports' ,1)
INSERT ClientCal VALUES ('Sally','20040313','Chase Reports',1)
INSERT ClientCal VALUES ('Jack','20040405','Send Reports' ,1)
INSERT ClientCal VALUES ('Jack','20040416','Chase Reports',1)
INSERT ClientCal VALUES ('Victoria','20040404','Send Reports' ,1)
INSERT ClientCal VALUES ('Victoria','20040415','Chase Reports',1)
INSERT ClientCal SELECT Client, Start + 1, Title, BCount FROM ClientCal
INSERT ClientCal SELECT Client, Start + 2, Title, BCount FROM ClientCal
INSERT ClientCal SELECT Client, Start + 4, Title, BCount FROM ClientCal
INSERT ClientCal SELECT Client, Start + 8, Title, BCount FROM ClientCal
INSERT ClientCal SELECT Client, Start + 16, Title, BCount FROM ClientCal
INSERT ClientCal SELECT Client, Start + 32, Title, BCount FROM ClientCal
INSERT ClientCal SELECT Client, Start + 64, Title, BCount FROM ClientCal
INSERT ClientCal SELECT Client, Start + 128, Title, BCount FROM ClientCal
INSERT ClientCal SELECT Client, Start + 256, Title, BCount FROM ClientCal
CREATE TABLE Dept (
DeptName varchar(100) CONSTRAINT PK_Dept PRIMARY KEY CLUSTERED
)
INSERT Dept VALUES ('Finance')
INSERT Dept VALUES ('Marketing')
CREATE TABLE ClientDept (
Client varchar(200),
DeptName varchar(100),
CONSTRAINT PK_ClientDept PRIMARY KEY CLUSTERED (Client, DeptName)
)
INSERT ClientDept VALUES ('Bob', 'Finance')
INSERT ClientDept VALUES ('Sid', 'Marketing')
SET NOCOUNT OFF
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BuildCalendarProc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[BuildCalendarProc]
GO
CREATE PROCEDURE BuildCalendarProc
@StartMonth datetime,
@EndMonth datetime,
@Dept varchar(100) = NULL
AS
SET @StartMonth = DateAdd(mm, DateDiff(mm, 0, @StartMonth), 0)
SET @EndMonth = DateAdd(mm, DateDiff(mm, 0, @EndMonth) + 1, 0)
SELECT
CalendarDate = @StartMonth + Num - 1,
C.Client,
C.Title,
C.BCount
FROM
Numbers N
LEFT JOIN (
ClientCal C
INNER JOIN ClientDept D ON C.Client = D.Client AND (D.DeptName = @Dept OR @Dept IS NULL)
) ON N.Num = C.Start - @StartMonth + 1
WHERE
N.Num <= @EndMonth - @StartMonth
RETURN @@Error
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BuildCalendarFunc]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[BuildCalendarFunc]
GO
CREATE FUNCTION BuildCalendarFunc (
@StartMonth datetime,
@EndMonth datetime,
@Dept varchar(100)
)
RETURNS @Calendar TABLE (
CalendarDate datetime,
Client varchar(100),
Title varchar(100),
BCount int
)
AS
BEGIN
SET @StartMonth = DateAdd(mm, DateDiff(mm, 0, @StartMonth), 0)
SET @EndMonth = DateAdd(mm, DateDiff(mm, 0, @EndMonth) + 1, 0)
INSERT @Calendar
SELECT
@StartMonth + Num - 1,
C.Client,
C.Title,
C.BCount
FROM
Numbers N
LEFT JOIN (
ClientCal C
INNER JOIN ClientDept D ON C.Client = D.Client AND (D.DeptName = @Dept OR @Dept IS NULL)
) ON N.Num = C.Start - @StartMonth + 1
WHERE
N.Num <= @EndMonth - @StartMonth
RETURN
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BuildCalendarView]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[BuildCalendarView]
GO
CREATE VIEW BuildCalendarView
AS
SELECT
CalendarDate = MinDate + Num - 1,
C.Client,
C.Title,
C.BCount,
D.DeptName
FROM
Numbers N
INNER JOIN (
SELECT
MinDate = DateAdd(yy, Year((Min(Start))) - 1900, 0),
MaxDate = DateAdd(yy, Year((Max(Start))) - 1899, 0)
FROM ClientCal
) X ON N.Num <= MaxDate - MinDate
CROSS JOIN Dept D
LEFT JOIN (
ClientCal C
INNER JOIN ClientDept CD ON C.Client = CD.Client
) ON N.Num = C.Start - MinDate AND D.DeptName = CD.DeptName
WHERE
N.Num <= MaxDate - MinDate
GO
EXEC BuildCalendarProc '20040115', '20040602'
EXEC BuildCalendarProc '20040115', '20041227', 'Finance'
SELECT * FROM BuildCalendarFunc ('20040115', '20040602', NULL)
SELECT * FROM BuildCalendarFunc ('20040115', '20041227', 'Finance')
SELECT DISTINCT CalendarDate, Client, Title FROM BuildCalendarView WHERE CalendarDate BETWEEN '20040101' AND '20040630'
SELECT CalendarDate, Client, Title FROM BuildCalendarView WHERE CalendarDate BETWEEN '20040101' AND '20041231' AND DeptName = 'Finance'
GO
/*
USE Master
*/
GO
/*
DROP DATABASE Calendar
*/
GO
