April 13, 2010 at 1:19 pm
If (OBJECT_ID('dbo.FIREReportsData', 'Table') Is Not Null)
Begin;
Drop Table dbo.FIREReportsData;
End;
Go
If (OBJECT_ID('dbo.IncidentTypes', 'Table') Is Not Null)
Begin;
Drop Table dbo.IncidentTypes;
End;
Go
Create Table [dbo].[FIREReportsData]
(
[ReportQID] Int Identity(1,1) Not Null,
[StructuralFires] Int Null,
[Non_StructuralFires] Int Null,
[Non_Fire_Emergencies] Int Null,
[Fire_Alarms] Int Null,
[Hazardous_MaterialsRespIncids] Int Null,
[AvgResponseTime] NVarChar(50) Null,
[ALS_EngMedSvcsResp] Int Null,
[BLS_EngMedSvcsResp] Int Null,
[ALS_AMBSvcsResp] Int Null,
[BLS_AMBSvcsResp] Int Null,
[AvgRespTimeALSEngResp] NVarChar(50) Null,
[AvgRespTimeBLSEngResp] NVarChar(50) Null,
[AvgRespTimeALSAMBgResp] NVarChar(50) Null,
[AvgRespTimeBLSAMBResp] NVarChar(50) Null,
[NoStructInspectEd] Int Null,
[NoStructInspectInspected] Int Null,
[NoStructInspectInvestigated] Int Null,
[NoHazardCodesViolIssued] Int Null,
[NumInspected] Int Null,
[NumIssued] Int Null,
[Comments] NVarChar(150) Null,
[StartDate] Date Null,
[EndDate] Date Null,
CONSTRAINT [PK_FIREReportsData] PRIMARY KEY CLUSTERED
(
ReportQID Asc
) On [Primary]
) On [Primary];
Go
Create Table IncidentTypes
(
IncidentTypeId TinyInt Not Null Identity(1, 1),
IncidentType NVarChar(30) Not Null,
SortOrder Int Not Null
Constraint [Idx_IncidentTypes_SortOrder] Unique,
Constraint [Pk_IncidentTypes_IncidentTypeId] Primary Key Clustered
(
IncidentTypeId Asc
) On [Primary]
) On [Primary];
Go
Insert Into dbo.FIREReportsData (StructuralFires, Non_StructuralFires, Non_Fire_Emergencies,
Fire_Alarms, Hazardous_MaterialsRespIncids, AvgResponseTime, ALS_EngMedSvcsResp,
BLS_EngMedSvcsResp, ALS_AMBSvcsResp, BLS_AMBSvcsResp, AvgRespTimeALSEngResp,
AvgRespTimeBLSEngResp, AvgRespTimeALSAMBgResp, AvgRespTimeBLSAMBResp, NoStructInspectEd,
NoStructInspectInspected, NoStructInspectInvestigated, NoHazardCodesViolIssued,
NumInspected, NumIssued, Comments, StartDate, EndDate)
Values (1, 1, 0, 6, 4, '6:17', 3, 1, 0, 7, 'NA', '6:16', '6:13', '12:17', 3, 10, 15, 6,
4, 9, 'First data dump', '9/24/2009', '10/1/2009'),
(7, 1, 2, 9, 4, '3:17', 1, 1, 0, 4, 'NA', '3:16', '6:13', '12:17', 3, 10, 15, 6, 4, 9,
'Second data dump', '9/25/2009', '10/2/2009'),
(7, 33, 2, 19, 4, '3:17', 11, 4, 8, 4, 'NA', '3:16', '6:13', '12:17', 3, 10, 15, 6, 4,
9, 'Third data dump', '9/26/2009', '10/3/2009');
Insert Into dbo.IncidentTypes (IncidentType, SortOrder)
Values ('StructuralFires', 1),
('Non_StructuralFires', 2),
('Non_Fire_Emergencies', 3),
('Fire_Alarms', 4),
('Hazardous_MaterialsRespIncids', 5),
('Total Fire Incidents', 6),
('AvgResponseTime', 7),
('ALS_EngMedSvcsResp', 8),
('BLS_EngMedSvcsResp', 9),
('ALS_AMBSvcsResp', 10),
('BLS_AMBSvcsResp', 11),
('AvgRespTimeALSEngResp', 12),
('AvgRespTimeBLSEngResp', 13),
('AvgRespTimeALSAMBgResp', 14),
('AvgRespTimeBLSAMBResp', 15),
('NoStructInspectEd', 16),
('NoStructInspectInspected', 17),
('NoStructInspectInvestigated', 18),
('NoHazardCodesViolIssued', 19),
('NumInspected', 20),
('NumIssued', 21),
('Comments', 22);
Go
Declare @StartDate DateTime;
Declare @EndDate DateTime;
Declare @ReportYear Int;
set @StartDate = '9/26/2009';
set @EndDate = '10/3/2009';
-- Determine the year you are working with.
Set @ReportYear = YEAR(@StartDate);
-- Build the CTEs to hold your unpivoted data.
With WeeklyReport (IncidentType, Incidents)
As
(
Select IncidentType, Incidents
From
(
Select ReportQID, CONVERT(NVarChar(150), StructuralFires) As [StructuralFires],
CONVERT(NVarChar(150), Non_StructuralFires) As [Non_StructuralFires],
CONVERT(NVarChar(150), Non_Fire_Emergencies) As [Non_Fire_Emergencies],
CONVERT(NVarChar(150), Fire_Alarms) As [Fire_Alarms],
CONVERT(NVarChar(150), Hazardous_MaterialsRespIncids) As [Hazardous_MaterialsRespIncids],
CONVERT(NVarChar(150), (StructuralFires + Non_StructuralFires + Non_Fire_Emergencies
+ Fire_Alarms + Hazardous_MaterialsRespIncids)) As [Total Fire Incidents],
CONVERT(NVarChar(150), AvgResponseTime) As [AvgResponseTime],
CONVERT(NVarChar(150), ALS_EngMedSvcsResp) As [ALS_EngMedSvcsResp],
CONVERT(NVarChar(150), BLS_EngMedSvcsResp) As [BLS_EngMedSvcsResp],
CONVERT(NVarChar(150), ALS_AMBSvcsResp) As [ALS_AMBSvcsResp],
CONVERT(NVarChar(150), BLS_AMBSvcsResp) As [BLS_AMBSvcsResp],
CONVERT(NVarChar(150), AvgRespTimeALSEngResp) As [AvgRespTimeALSEngResp],
CONVERT(NVarChar(150), AvgRespTimeBLSEngResp) As [AvgRespTimeBLSEngResp],
CONVERT(NVarChar(150), AvgRespTimeALSAMBgResp) As [AvgRespTimeALSAMBgResp],
CONVERT(NVarChar(150), AvgRespTimeBLSAMBResp) As [AvgRespTimeBLSAMBResp],
CONVERT(NVarChar(150), NoStructInspectEd) As [NoStructInspectEd],
CONVERT(NVarChar(150), NoStructInspectInspected) As [NoStructInspectInspected],
CONVERT(NVarChar(150), NoStructInspectInvestigated) As [NoStructInspectInvestigated],
CONVERT(NVarChar(150), NoHazardCodesViolIssued) As [NoHazardCodesViolIssued],
CONVERT(NVarChar(150), NumInspected) As [NumInspected],
CONVERT(NVarChar(150), NumIssued) As [NumIssued],
CONVERT(NVarChar(150), Comments) As [Comments]
From FireReportsData
Where (startDate = @StartDate and EndDate = @EndDate)
) As FireData
UnPivot
(
Incidents For IncidentType In ([StructuralFires], [Non_StructuralFires],
[Non_Fire_Emergencies], [Fire_Alarms],[Hazardous_MaterialsRespIncids],
[AvgResponseTime], [ALS_EngMedSvcsResp], [BLS_EngMedSvcsResp], [ALS_AMBSvcsResp],
[BLS_AMBSvcsResp], [AvgRespTimeALSEngResp], [AvgRespTimeBLSEngResp],
[AvgRespTimeALSAMBgResp], [AvgRespTimeBLSAMBResp], [NoStructInspectEd],
[NoStructInspectInspected], [NoStructInspectInvestigated],
[NoHazardCodesViolIssued], [NumInspected], [NumIssued], [Comments],
[Total Fire Incidents])
) As FireDataUnpivot
),
YtdTimeAverages (AvgResponseTime, AvgRespTimeALSEngResp, AvgRespTimeBLSEngResp,
AvgRespTimeALSAMBgResp, AvgRespTimeBLSAMBResp)
As
(
Select ISNULL(CONVERT(NVarChar(10), (AvgResponseTime / 60)) + N':'
+ RIGHT('0' + CONVERT(NVarChar(2), (AvgResponseTime % 60)), 2), N'NA'),
ISNULL(CONVERT(NVarChar(10), (AvgRespTimeALSEngResp / 60)) + N':'
+ RIGHT('0' + CONVERT(NVarChar(2), (AvgRespTimeALSEngResp % 60)), 2), N'NA'),
ISNULL(CONVERT(NVarChar(10), (AvgRespTimeBLSEngResp / 60)) + N':'
+ RIGHT('0' + CONVERT(NVarChar(2), (AvgRespTimeBLSEngResp % 60)), 2), N'NA'),
ISNULL(CONVERT(NVarChar(10), (AvgRespTimeALSAMBgResp / 60)) + N':'
+ RIGHT('0' + CONVERT(NVarChar(2), (AvgRespTimeALSAMBgResp % 60)), 2), N'NA'),
ISNULL(CONVERT(NVarChar(10), (AvgRespTimeBLSAMBResp / 60)) + N':'
+ RIGHT('0' + CONVERT(NVarChar(2), (AvgRespTimeBLSAMBResp % 60)), 2), N'NA')
From
(
Select AVG(CAST(Left(NULLIF(AvgResponseTime, N'NA'), LEN(AvgResponseTime) - 3) As Int) * 60
+ CAST(Right(NULLIF(AvgResponseTime, N'NA'), 2) As Int)) [AvgResponseTime],
AVG(CAST(Left(NULLIF(AvgRespTimeALSEngResp, N'NA'), LEN(AvgRespTimeALSEngResp) - 3) As Int) * 60
+ CAST(Right(NULLIF(AvgRespTimeALSEngResp, N'NA'), 2) As Int)) [AvgRespTimeALSEngResp],
AVG(CAST(Left(NULLIF(AvgRespTimeBLSEngResp, N'NA'), LEN(AvgRespTimeBLSEngResp) - 3) As Int) * 60
+ CAST(Right(NULLIF(AvgRespTimeBLSEngResp, N'NA'), 2) As Int)) [AvgRespTimeBLSEngResp],
AVG(CAST(Left(NULLIF(AvgRespTimeALSAMBgResp, N'NA'), LEN(AvgRespTimeALSAMBgResp) - 3) As Int) * 60
+ CAST(Right(NULLIF(AvgRespTimeALSAMBgResp, N'NA'), 2) As Int)) [AvgRespTimeALSAMBgResp],
AVG(CAST(Left(NULLIF(AvgRespTimeBLSAMBResp, N'NA'), LEN(AvgRespTimeBLSAMBResp) - 3) As Int) * 60
+ CAST(Right(NULLIF(AvgRespTimeBLSAMBResp, N'NA'), 2) As Int)) [AvgRespTimeBLSAMBResp]
From FireReportsData
Where (YEAR(StartDate) = @ReportYear) And (StartDate <= @StartDate)
) As TimeInSeconds
),
YtdReport (IncidentType, YtdIncidents)
As
(
Select IncidentType, Incidents
From
(
Select CONVERT(NVarChar(150), SUM(StructuralFires)) As [StructuralFires],
CONVERT(NVarChar(150), SUM(Non_StructuralFires)) As [Non_StructuralFires],
CONVERT(NVarChar(150), SUM(Non_Fire_Emergencies)) As [Non_Fire_Emergencies],
CONVERT(NVarChar(150), SUM(Fire_Alarms)) As [Fire_Alarms] ,
CONVERT(NVarChar(150), SUM(Hazardous_MaterialsRespIncids)) As [Hazardous_MaterialsRespIncids],
CONVERT(NVarChar(150), SUM(StructuralFires + Non_StructuralFires + Non_Fire_Emergencies
+ Fire_Alarms + Hazardous_MaterialsRespIncids)) As [Total Fire Incidents],
CONVERT(NVarChar(150), A.AvgResponseTime) As [AvgResponseTime],
CONVERT(NVarChar(150), SUM(ALS_EngMedSvcsResp)) As [ALS_EngMedSvcsResp],
CONVERT(NVarChar(150), SUM(BLS_EngMedSvcsResp)) As [BLS_EngMedSvcsResp],
CONVERT(NVarChar(150), SUM(ALS_AMBSvcsResp)) As [ALS_AMBSvcsResp],
CONVERT(NVarChar(150), SUM(BLS_AMBSvcsResp)) As [BLS_AMBSvcsResp],
CONVERT(NVarChar(150), A.AvgRespTimeALSEngResp) As [AvgRespTimeALSEngResp],
CONVERT(NVarChar(150), A.AvgRespTimeBLSEngResp) As [AvgRespTimeBLSEngResp],
CONVERT(NVarChar(150), A.AvgRespTimeALSAMBgResp) As [AvgRespTimeALSAMBgResp],
CONVERT(NVarChar(150), A.AvgRespTimeBLSAMBResp) As [AvgRespTimeBLSAMBResp],
CONVERT(NVarChar(150), SUM(NoStructInspectEd)) As [NoStructInspectEd],
CONVERT(NVarChar(150), SUM(NoStructInspectInspected)) As [NoStructInspectInspected],
CONVERT(NVarChar(150), SUM(NoStructInspectInvestigated)) As [NoStructInspectInvestigated],
CONVERT(NVarChar(150), SUM(NoHazardCodesViolIssued)) As [NoHazardCodesViolIssued],
CONVERT(NVarChar(150), SUM(NumInspected)) As [NumInspected],
CONVERT(NVarChar(150), SUM(NumIssued)) As [NumIssued],
CONVERT(NVarChar(150), '') As [Comments]
From FireReportsData As F
Cross Apply YtdTimeAverages As A
Where (YEAR(StartDate) = @ReportYear) And (StartDate <= @StartDate)
Group By A.AvgResponseTime, A.AvgRespTimeALSEngResp, A.AvgRespTimeBLSEngResp,
A.AvgRespTimeALSAMBgResp, A.AvgRespTimeBLSAMBResp
) As FireData
UnPivot
(
Incidents For IncidentType In ([StructuralFires], [Non_StructuralFires],
[Non_Fire_Emergencies], [Fire_Alarms],[Hazardous_MaterialsRespIncids],
[AvgResponseTime], [ALS_EngMedSvcsResp], [BLS_EngMedSvcsResp], [ALS_AMBSvcsResp],
[BLS_AMBSvcsResp], [AvgRespTimeALSEngResp], [AvgRespTimeBLSEngResp],
[AvgRespTimeALSAMBgResp], [AvgRespTimeBLSAMBResp], [NoStructInspectEd],
[NoStructInspectInspected], [NoStructInspectInvestigated], [NoHazardCodesViolIssued],
[NumInspected], [NumIssued], [Comments], [Total Fire Incidents])
) As FireDataUnpivot
)
-- Join the weekly and monthly results based off of incident type to produce
-- your result set.
Select W.IncidentType, W.Incidents, Ytd.YtdIncidents
From WeeklyReport As W
Inner Join YtdReport As Ytd On W.IncidentType = Ytd.IncidentType
Inner Join dbo.IncidentTypes As It On It.IncidentType = W.IncidentType
Order By It.SortOrder;
Go
If (OBJECT_ID('dbo.FIREReportsData', 'Table') Is Not Null)
Begin;
Drop Table dbo.FIREReportsData;
End;
Go
If (OBJECT_ID('dbo.IncidentTypes', 'Table') Is Not Null)
Begin;
Drop Table dbo.IncidentTypes;
End;
Go
April 13, 2010 at 1:56 pm
It is WORKING sir!!!!!!
I will NEVER, EVER forget this, EVER!
Thank you, very, very much.
Thank you K Cline. THANK YOU SIR!
So, I can just perform the following action just ONCE, right?
Create Table IncidentTypes
(
IncidentTypeId TinyInt Not Null Identity(1, 1),
IncidentType NVarChar(30) Not Null,
SortOrder Int Not Null
Constraint [Idx_IncidentTypes_SortOrder] Unique,
Constraint [Pk_IncidentTypes_IncidentTypeId] Primary Key Clustered
(
IncidentTypeId Asc
) On [Primary]
) On [Primary];
Go
Insert Into dbo.FIREReportsData (StructuralFires, Non_StructuralFires, Non_Fire_Emergencies,
Fire_Alarms, Hazardous_MaterialsRespIncids, AvgResponseTime, ALS_EngMedSvcsResp,
BLS_EngMedSvcsResp, ALS_AMBSvcsResp, BLS_AMBSvcsResp, AvgRespTimeALSEngResp,
AvgRespTimeBLSEngResp, AvgRespTimeALSAMBgResp, AvgRespTimeBLSAMBResp, NoStructInspectEd,
NoStructInspectInspected, NoStructInspectInvestigated, NoHazardCodesViolIssued,
NumInspected, NumIssued, Comments, StartDate, EndDate)
Values (1, 1, 0, 6, 4, '6:17', 3, 1, 0, 7, 'NA', '6:16', '6:13', '12:17', 3, 10, 15, 6,
4, 9, 'First data dump', '9/24/2009', '10/1/2009'),
(7, 1, 2, 9, 4, '3:17', 1, 1, 0, 4, 'NA', '3:16', '6:13', '12:17', 3, 10, 15, 6, 4, 9,
'Second data dump', '9/25/2009', '10/2/2009'),
(7, 33, 2, 19, 4, '3:17', 11, 4, 8, 4, 'NA', '3:16', '6:13', '12:17', 3, 10, 15, 6, 4,
9, 'Third data dump', '9/26/2009', '10/3/2009');
Insert Into dbo.IncidentTypes (IncidentType, SortOrder)
Values ('StructuralFires', 1),
('Non_StructuralFires', 2),
('Non_Fire_Emergencies', 3),
('Fire_Alarms', 4),
('Hazardous_MaterialsRespIncids', 5),
('Total Fire Incidents', 6),
('AvgResponseTime', 7),
('ALS_EngMedSvcsResp', 8),
('BLS_EngMedSvcsResp', 9),
('ALS_AMBSvcsResp', 10),
('BLS_AMBSvcsResp', 11),
('AvgRespTimeALSEngResp', 12),
('AvgRespTimeBLSEngResp', 13),
('AvgRespTimeALSAMBgResp', 14),
('AvgRespTimeBLSAMBResp', 15),
('NoStructInspectEd', 16),
('NoStructInspectInspected', 17),
('NoStructInspectInvestigated', 18),
('NoHazardCodesViolIssued', 19),
('NumInspected', 20),
('NumIssued', 21),
('Comments', 22);
Go
I ask before we will be using .net to create the <FORM> for inserting records into the db and I have already gotten that working.
I just want to make sure that the new table you created and the INSERT statement can be done once on the backend and my .net will be inserting new values into FIREReportsDate only.
and will it be too difficult add more Sub Totals should the need to do so arises?
It is very hard to express my most sincere appreciation.
This is definitely not easy at all.
As you said, it turned into way too much fun in the wrong way due to all the headache I created for you.
GOD WILL REWARD SIR.
Many, many thanks.
You are truly God sent.
You are the nicest individual I have never met.
Viewing 2 posts - 31 through 31 (of 31 total)
You must be logged in to reply to this topic. Login to reply