Miles

CREATE FUNCTION dbo.GetAccidentMonthly(@Location varchar,@StartDate integer)  RETURNS (view) AS  BEGIN SELECT     Accidents AS Type, DCount(ID, tab_PERSONNEL ACCIDENTS,                       Month([DATE OF ACCIDENT]) = 1 AND  Year([DATE OF ACCIDENT]) = @StartDate                      AND [LOCATION OF ACCIDENT] = @Location) AS 1, DCount(ID,                       tab_PERSONNEL ACCIDENTS, Month([DATE OF ACCIDENT]) = 2 AND  Year([DATE OF ACCIDENT]) =  @StartDate                       AND [LOCATION OF ACCIDENT]  = @Location) AS 2, DCount(ID,                       tab_PERSONNEL ACCIDENTS, Month([DATE OF ACCIDENT]) = 3 AND  Year([DATE OF ACCIDENT]) =  @StartDate                       AND [LOCATION OF ACCIDENT]  = @Location) AS 3, DCount(ID,                       tab_PERSONNEL ACCIDENTS, Month([DATE OF ACCIDENT]) = 4 AND  Year([DATE OF ACCIDENT]) =  @StartDate                       AND [LOCATION OF ACCIDENT]  = @Location) AS 4, DCount(ID,                       tab_PERSONNEL ACCIDENTS, Month([DATE OF ACCIDENT]) = 5 AND  Year([DATE OF ACCIDENT]) =  @StartDate                       AND [LOCATION OF ACCIDENT]  = @Location) AS 5, DCount(ID,                       tab_PERSONNEL ACCIDENTS, Month([DATE OF ACCIDENT]) = 6 AND  Year([DATE OF ACCIDENT]) =  @StartDate                       AND [LOCATION OF ACCIDENT]  = @Location) AS 6, DCount(ID,                       tab_PERSONNEL ACCIDENTS, Month([DATE OF ACCIDENT]) = 7 AND  Year([DATE OF ACCIDENT]) =  @StartDate                       AND [LOCATION OF ACCIDENT]  = @Location) AS 7, DCount(ID,                       tab_PERSONNEL ACCIDENTS, Month([DATE OF ACCIDENT]) = 8 AND  Year([DATE OF ACCIDENT]) =  @StartDate                       AND [LOCATION OF ACCIDENT]  = @Location) AS 8, DCount(ID,                       tab_PERSONNEL ACCIDENTS, Month([DATE OF ACCIDENT]) = 9 AND  Year([DATE OF ACCIDENT]) =  @StartDate                       AND [LOCATION OF ACCIDENT]  = @Location) AS 9, DCount(ID,                       tab_PERSONNEL ACCIDENTS, Month([DATE OF ACCIDENT]) = 10 AND  Year([DATE OF ACCIDENT]) =  @StartDate                       AND [LOCATION OF ACCIDENT]  = @Location) AS 10, DCount(ID,                       tab_PERSONNEL ACCIDENTS, Month([DATE OF ACCIDENT]) = 11 AND  Year([DATE OF ACCIDENT]) =  @StartDate                       AND [LOCATION OF ACCIDENT]  = @Location) AS 11, DCount(ID,                       tab_PERSONNEL ACCIDENTS, Month([DATE OF ACCIDENT]) = 12 AND  Year([DATE OF ACCIDENT]) =  @StartDate                       AND [LOCATION OF ACCIDENT]  = @Location) AS 12FROM         tab_PERSONNEL ACCIDENTSUNIONSELECT     Targets AS Type, DSum(TARGET, tab_ACCIDENT TARGETS, MONTH_ID = 1) AS 1, DSum(TARGET, tab_ACCIDENT TARGETS,                       MONTH_ID = 2) AS 2, DSum(TARGET, tab_ACCIDENT TARGETS, MONTH_ID = 3) AS 3, DSum(TARGET, tab_ACCIDENT TARGETS,                       MONTH_ID = 4) AS 4, DSum(TARGET, tab_ACCIDENT TARGETS, MONTH_ID = 5) AS 5, DSum(TARGET, tab_ACCIDENT TARGETS,                       MONTH_ID = 6) AS 6, DSum(TARGET, tab_ACCIDENT TARGETS, MONTH_ID = 7) AS 7, DSum(TARGET, tab_ACCIDENT TARGETS,                       MONTH_ID = 8) AS 8, DSum(TARGET, tab_ACCIDENT TARGETS, MONTH_ID = 9) AS 9, DSum(TARGET, tab_ACCIDENT TARGETS,                       MONTH_ID = 10) AS 10, DSum(TARGET, tab_ACCIDENT TARGETS, MONTH_ID = 11) AS 11, DSum(TARGET, tab_ACCIDENT TARGETS,                       MONTH_ID = 12) AS 12FROM         tab_ACCIDENT TARGETSGROUP BY Targets, DSum(TARGET, tab_ACCIDENT TARGETS, MONTH_ID = 1), DSum(TARGET, tab_ACCIDENT TARGETS, MONTH_ID = 2),                       DSum(TARGET, tab_ACCIDENT TARGETS, MONTH_ID = 3), DSum(TARGET, tab_ACCIDENT TARGETS, MONTH_ID = 4),                       DSum(TARGET, tab_ACCIDENT TARGETS, MONTH_ID = 5), DSum(TARGET, tab_ACCIDENT TARGETS, MONTH_ID = 6),                       DSum(TARGET, tab_ACCIDENT TARGETS, MONTH_ID = 7), DSum(TARGET, tab_ACCIDENT TARGETS, MONTH_ID = 8),                       DSum(TARGET, tab_ACCIDENT TARGETS, MONTH_ID = 9), DSum(TARGET, tab_ACCIDENT TARGETS, MONTH_ID = 10),                       DSum(TARGET, tab_ACCIDENT TARGETS, MONTH_ID = 11), DSum(TARGET, tab_ACCIDENT TARGETS, MONTH_ID = 12)END