April 2, 2010 at 11:34 pm
Sure. Just change the parameter type.
Declare @StartDate Date;
....
-- Build the CTEs to hold your unpivoted data.
With WeeklyReport (IncidentType, Incidents)
As
(
Select IncidentType, Incidents
From
(
Select ReportQID, StructuralFires, Non_StructuralFires, Non_Fire_Emergencies,
Fire_Alarms
From @FireReportsData
Where (StartDate = @StartDate)
) As FireData
UnPivot
(
Incidents For IncidentType In ([StructuralFires], [Non_StructuralFires],
[Non_Fire_Emergencies], [Fire_Alarms])
) As FireDataUnpivot
),
...
There may be a couple of other places you need to update to get it to work with dates. But the changes should be minor. Best of luck.
April 5, 2010 at 9:00 am
Thank you very, very much K Cline.
I will give this a go and let you know how it turns out.
April 6, 2010 at 6:20 am
Thank you very, very much K Cline.
I will give this a go and let you know how it turns out.
April 9, 2010 at 8:21 am
hi K Cline:
I must say that you have gotten me closer to solving this problem than anyone that I can think of.
I am very impressed and I thank you very, very much.
I am still dealing some issues with YTD (YTDInciddents) calculation.
For instance, let's assume that the first incident was recorded for the week of 9/25/2009 to 10/2/2009.This is actually the case with the forms I am working with. And let's assume that StructuralFires, for instance, has a total of 5 Incidents. In this case, the YTDIncidents should be 5.
If we record another incident for the week of 9/26/2009 to 10/3/2009, and StructuralFires is 3 Incidents for this date range, then YTD should be 8 (5 for first week, 3 for next week).
In this case, whether you select first week or last week, you get total YTDIncidents for all week.
I hope this makes sense.
I am just using one fieldname - StructuralFires as an example.
Here is what I have so far:
Declare @StartDate DateTime;
Declare @EndDate DateTime;
Declare @ReportYear Int;
set @StartDate = '9/27/2009';
set @EndDate = '10/4/2009';
-- Determine the year you are working with.
Set @ReportYear =
(
Select YEAR(StartDate)
From FireReportsData
Where (startDate = @StartDate and EndDate = @EndDate)
);
-- Build the CTEs to hold your unpivoted data.
With WeeklyReport (IncidentType, Incidents)
As
(
Select IncidentType, Incidents
From
(
Select ReportQID, StructuralFires, Non_StructuralFires, Non_Fire_Emergencies,
Fire_Alarms
From FireReportsData
Where (startDate = @StartDate and EndDate = @EndDate)
) As FireData
UnPivot
(
Incidents For IncidentType In ([StructuralFires], [Non_StructuralFires],
[Non_Fire_Emergencies], [Fire_Alarms])
) As FireDataUnpivot
),
YtdReport (IncidentType, YtdIncidents)
As
(
Select IncidentType, Incidents
From
(
Select SUM(StructuralFires) As [StructuralFires],
SUM(Non_StructuralFires) As [Non_StructuralFires],
SUM(Non_Fire_Emergencies) As [Non_Fire_Emergencies],
SUM(Fire_Alarms) As [Fire_Alarms]
From FireReportsData
Where (YEAR(StartDate) = @ReportYear)
) As FireData
UnPivot
(
Incidents For IncidentType In ([StructuralFires], [Non_StructuralFires],
[Non_Fire_Emergencies], [Fire_Alarms])
) 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
Union All
-- Create a "totals" record.
Select 'Totals' As [IncidentType], SUM(W.Incidents) As [Incidents],
SUM(Ytd.YtdIncidents) As [YtdIncidents]
From WeeklyReport As W
Inner Join YtdReport As Ytd On W.IncidentType = Ytd.IncidentType;
Go
Again, many, many thanks.
April 9, 2010 at 12:20 pm
It is not a problem. Glad to help if I am able.
-- If you already have a variable with the start date, you can avoid hitting the table
-- and just get your @ReportYear value from the variable.
Declare @StartDate DateTime;
Declare @EndDate DateTime;
Declare @ReportYear Int;
set @StartDate = '9/27/2009';
set @EndDate = '10/4/2009';
-- Determine the year you are working with.
Set @ReportYear = YEAR(@StartDate);
Good point on the YTD value. See, the very reason we go through a testing cycle. 🙂 An oversight on my part. My apologies.
YtdReport (IncidentType, YtdIncidents)
As
(
Select IncidentType, Incidents
From
(
Select SUM(StructuralFires) As [StructuralFires],
SUM(Non_StructuralFires) As [Non_StructuralFires],
SUM(Non_Fire_Emergencies) As [Non_Fire_Emergencies],
SUM(Fire_Alarms) As [Fire_Alarms]
From FireReportsData
Where (YEAR(StartDate) = @ReportYear)
-- Here's the part I missed before.
And (StartDate <= @StartDate)
) As FireData
UnPivot
(
Incidents For IncidentType In ([StructuralFires], [Non_StructuralFires],
[Non_Fire_Emergencies], [Fire_Alarms])
) As FireDataUnpivot
April 9, 2010 at 1:57 pm
This is truly, truly incredible.
I have had some truly wonderful here since I discovered this forum and I appreciate every single one of them but this is remarkable.
I honestly didn't think I could this far.
Thanks very, very much.
I just have ONE more issue, I promise this will be completely solved.
I tried but couldn't figure it out.
The fieldnames we have selected so far gives us the subtotals.
For instance, this:
-- If you already have a variable with the start date, you can avoid hitting the table
-- and just get your @ReportYear value from the variable.
Declare @StartDate DateTime;
Declare @EndDate DateTime;
Declare @ReportYear Int;
set @StartDate = '9/27/2009';
set @EndDate = '10/4/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, StructuralFires, Non_StructuralFires, Non_Fire_Emergencies,
Fire_Alarms
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])
) As FireDataUnpivot
),
YtdReport (IncidentType, YtdIncidents)
As
(
Select IncidentType, Incidents
From
(
Select SUM(StructuralFires) As [StructuralFires],
SUM(Non_StructuralFires) As [Non_StructuralFires],
SUM(Non_Fire_Emergencies) As [Non_Fire_Emergencies],
SUM(Fire_Alarms) As [Fire_Alarms] ,
SUM(Hazardous_MaterialsRespIncids) As [Hazardous_MaterialsRespIncids]
From FireReportsData
Where (YEAR(StartDate) = @ReportYear)
-- Here's the part I missed before.
And (StartDate <= @StartDate)
) As FireData
UnPivot
(
Incidents For IncidentType In ([StructuralFires], [Non_StructuralFires],
[Non_Fire_Emergencies], [Fire_Alarms], [Hazardous_MaterialsRespIncids])
) 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
Union All
-- Create a "totals" record.
Select 'Total Fire Incidents' As [IncidentType], SUM(W.Incidents) As [Incidents],
SUM(Ytd.YtdIncidents) As [YtdIncidents]
From WeeklyReport As W
Inner Join YtdReport As Ytd On W.IncidentType = Ytd.IncidentType;
Go
However, immediately after the Total Fire Incidents sub total,
I have more fieldnames
,[AvgResponseTime]
,[ALS_EngMedSvcsResp]
,[BLS_EngMedSvcsResp]
,[ALS_AMBSvcsResp]
,[BLS_AMBSvcsResp]
,[AvgRespTimeALSEngResp]
,[AvgRespTimeBLSEngResp]
,[AvgRespTimeALSAMBgResp]
,[AvgRespTimeBLSAMBResp]
,[NoStructInspectEd]
,[NoStructInspectInspected]
,[NoStructInspectInvestigated]
,[NoHazardCodesViolIssued]
,[NumInspected]
,[NumIssued]
,[Comments]
How do I include the rest of these fieldnames below the 'Total Fire Incidents' sub total?
If it is possible to do, can you please help?
You don't have to enter all fieldnames.
An example of how do it with one or two fieldnames will do.
I have all your recent changes to the code I just pasted.
Many, many thanks for your help and patience.
April 12, 2010 at 11:08 am
Hi K Cline.
I know I am becoming a pain, despite your generosity with your time and assistance but if you could help solve this one last issue, I would really appreciate it.
Please see last post before this.
I have been trying real hard to fix it but no love.
Thanks a lot
April 12, 2010 at 11:50 am
Apologies, I missed Friday's post or I would have gotten back to you sooner.
All you need to do to add new fields it include them in both portions of the unpivot statement.
** Make sure you are updating both weekly and Ytd CTEs. Since they are unioned the table definitions need to match.
-- Example code.
With WeeklyReport (IncidentType, Incidents)
As
(
Select IncidentType, Incidents
From
(
Select ReportQID, StructuralFires, Non_StructuralFires, Non_Fire_Emergencies,
Fire_Alarms, NewField1, NewField2, ...
From FireReportsData
Where (startDate = @StartDate and EndDate = @EndDate)
)
UnPivot
(
Incidents For IncidentType In ([StructuralFires], [Non_StructuralFires],
[Non_Fire_Emergencies], [Fire_Alarms], [NewField1], [NewField2], ...)
) As FireDataUnpivot
) Basically, do the same in the Ytd CTE. The only difference is that you want to SUM the value in the Select portion of the Ytd CTE.
I threw the rest of the fields into the list below. Hopefully it works, but I didn't have a chance to test the code.
-- If you already have a variable with the start date, you can avoid hitting the table
-- and just get your @ReportYear value from the variable.
Declare @StartDate DateTime;
Declare @EndDate DateTime;
Declare @ReportYear Int;
set @StartDate = '9/27/2009';
set @EndDate = '10/4/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, StructuralFires, Non_StructuralFires, Non_Fire_Emergencies,
Fire_Alarms, AvgResponseTime, ALS_EngMedSvcsResp, BLS_EngMedSvcsResp,
ALS_AMBSvcsResp, BLS_AMBSvcsResp, AvgRespTimeALSEngResp, AvgRespTimeBLSEngResp,
AvgRespTimeALSAMBgResp, AvgRespTimeBLSAMBResp, NoStructInspectEd,
NoStructInspectInspected, NoStructInspectInvestigated, NoHazardCodesViolIssued,
NumInspected, NumIssued, 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])
) As FireDataUnpivot
),
YtdReport (IncidentType, YtdIncidents)
As
(
Select IncidentType, Incidents
From
(
Select SUM(StructuralFires) As [StructuralFires],
SUM(Non_StructuralFires) As [Non_StructuralFires],
SUM(Non_Fire_Emergencies) As [Non_Fire_Emergencies],
SUM(Fire_Alarms) As [Fire_Alarms] ,
SUM(Hazardous_MaterialsRespIncids) As [Hazardous_MaterialsRespIncids],
SUM([AvgResponseTime]) As [AvgResponseTime],
SUM([ALS_EngMedSvcsResp]) As [ALS_EngMedSvcsResp],
SUM([BLS_EngMedSvcsResp]) As [BLS_EngMedSvcsResp],
SUM([ALS_AMBSvcsResp]) As [ALS_AMBSvcsResp],
SUM([BLS_AMBSvcsResp]) As [BLS_AMBSvcsResp],
SUM([AvgRespTimeALSEngResp]) As [AvgRespTimeALSEngResp],
SUM([AvgRespTimeBLSEngResp]) As [AvgRespTimeBLSEngResp],
SUM([AvgRespTimeALSAMBgResp]) As [AvgRespTimeALSAMBgResp],
SUM([AvgRespTimeBLSAMBResp]) As [AvgRespTimeBLSAMBResp],
SUM([NoStructInspectEd]) As [NoStructInspectEd],
SUM([NoStructInspectInspected]) As [NoStructInspectInspected],
SUM([NoStructInspectInvestigated]) As [NoStructInspectInvestigated],
SUM([NoHazardCodesViolIssued]) As [NoHazardCodesViolIssued],
SUM([NumInspected]) As [NumInspected], SUM([NumIssued]) As [NumIssued],
SUM([Comments]) As [Comments]
From FireReportsData
Where (YEAR(StartDate) = @ReportYear)
-- Here's the part I missed before.
And (StartDate <= @StartDate)
) 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])
) 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
Union All
-- Create a "totals" record.
Select 'Total Fire Incidents' As [IncidentType], SUM(W.Incidents) As [Incidents],
SUM(Ytd.YtdIncidents) As [YtdIncidents]
From WeeklyReport As W
Inner Join YtdReport As Ytd On W.IncidentType = Ytd.IncidentType;
Go
If you have any questions then feel free to ask. It is important that you understand the concepts that you are working with or debugging the code later will be difficult. So please do ask for clarifications if you don't completely grasp everything that is going on in the code.
April 12, 2010 at 12:42 pm
I am truly grateful for your generous assistance and patience.
I am getting this error:
The type of column "AvgResponseTime" conflicts with the type of other columns specified in the UNPIVOT list.
I know what it means. AvgResponseTime is nvarchar data type.
I thought that removing SUM and leaving it as:
AvgResponseTime As [b]AvgResponseTime[/b], would fix it but still getting that error.
Any workaround?
The value is like 4:23. It is used for time. Infact, any fieldname with time on it = the following:
,[AvgRespTimeALSEngResp]
,[AvgRespTimeBLSEngResp]
,[AvgRespTimeALSAMBgResp]
,[AvgRespTimeBLSAMBResp]
are use to record time.
And yes, I am understanding for the most part, enough to make few changes but unfortunately, not enough to complete what I am after right now which is to add more fieldnames right after 'Total Fire Incidents']
Like:
Incident Type Reporting Week YTD
Structural Fires 1 1
Non-Structural Fires 3 5
etc
etc
Total Fire Incides 4 6
new field value value
new field value value
etc
and the rest
Again, many THANKS
April 12, 2010 at 5:30 pm
Well this turned into all kinds of fun...
The unpivots expect all of the fields to be of the same type. Since we are including comments we had to make them NVarChar(150).
Had to handle the "time" fields a bit differently. This code makes the assumption that time data will always be presented in an "x:xx" format or as "NA" or Null.
I was actually able to test this code with some sample data. Seemed to produce a solid result. So I hope it works for you.
-- If you already have a variable with the start date, you can avoid hitting the table
-- and just get your @ReportYear value from the variable.
Declare @StartDate DateTime;
Declare @EndDate DateTime;
Declare @ReportYear Int;
set @StartDate = '9/27/2009';
set @EndDate = '10/4/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), 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])
) As FireDataUnpivot
),
YtdTimeAverages (AvgResponseTime, AvgRespTimeALSEngResp, AvgRespTimeBLSEngResp,
AvgRespTimeALSAMBgResp, AvgRespTimeBLSAMBResp)
As
(
Select ISNULL(CONVERT(NVarChar(150), (AvgResponseTime / 60)) + N':'
+ CONVERT(NVarChar(150), (AvgResponseTime % 60)), N'NA'),
ISNULL(CONVERT(NVarChar(150), (AvgRespTimeALSEngResp / 60)) + N':'
+ CONVERT(NVarChar(150), (AvgRespTimeALSEngResp % 60)), N'NA'),
ISNULL(CONVERT(NVarChar(150), (AvgRespTimeBLSEngResp / 60)) + N':'
+ CONVERT(NVarChar(150), (AvgRespTimeBLSEngResp % 60)), N'NA'),
ISNULL(CONVERT(NVarChar(150), (AvgRespTimeALSAMBgResp / 60)) + N':'
+ CONVERT(NVarChar(150), (AvgRespTimeALSAMBgResp % 60)), N'NA'),
ISNULL(CONVERT(NVarChar(150), (AvgRespTimeBLSAMBResp / 60)) + N':'
+ CONVERT(NVarChar(150), (AvgRespTimeBLSAMBResp % 60)), 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), 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])
) 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;
Go
April 12, 2010 at 9:02 pm
You are simply the BEST.
Many, many thanks.
I understand your comments about making the unpivot column names to nvarchar(150).
Did you change the datatypes on the db side as well?
I am getting this error:
Invalid length parameter passed to the SUBSTRING function.
Warning: Null value is eliminated by an aggregate or other SET operation.
It says line 12.
I could not go far enough.
You are truly and Angel of God.
Thanks a lot for bailing me out.
April 12, 2010 at 11:48 pm
Again, it is not a problem. Always glad to help if I am able.
Did you change the datatypes on the db side as well?
No. I left the table structure as you posted it a while back. I will post all of the code I had to generate the table and test data below.
I am getting this error:
Invalid length parameter passed to the SUBSTRING function.
Warning: Null value is eliminated by an aggregate or other SET operation.
My guess is that it has to do with the "Left" functions used when working with the average time data in the subquery in the YtdTimeAverages CTE. Check the data in the time fields. Remember my warning:
Had to handle the "time" fields a bit differently. This code makes the assumption that time data will always be presented in an "9:99" format or as "NA" or Null.
If you have a value in one of those fields that is not "NA", Null, or a properly time formatted value (need 0:42; :42 or 42 will cause an error), that could cause the error you are seeing.
If (OBJECT_ID('dbo.FIREReportsData', 'Table') Is Not Null)
Begin;
Drop Table dbo.FIREReportsData;
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
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');
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), 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])
) 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), 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])
) 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;
Go
If (OBJECT_ID('dbo.FIREReportsData', 'Table') Is Not Null)
Begin;
Drop Table dbo.FIREReportsData;
End;
Go
I did make a small formatting tweak to the YtdTimeAverages CTE to display second values less than 10 properly.
April 13, 2010 at 6:58 am
Thank you very much sir for all this work.
I really, really appreciate it.
The one BIG issue that I have is still not resolved and that is the issue of generating sub totals.
It is likely that I am not explaining it well.
Take for instance these fieldnames:
[StructuralFires],
[Non_StructuralFires] ,
[Non_Fire_Emergencies],
[Fire_Alarms],
[Hazardous_MaterialsRespIncids],
They are summed up to get Total Fire Incidents.
Then the rest of the fieldnames follow.
Here is the way data should look:
Incident Types Incidents YTDIncidents
---------------------------------------------------------------
StructuralFires 7 7
Non_StructuralFires 33 33
Non_Fire_Emergencies 2 2
Fire_Alarms 19 19
Hazardous_MaterialsRespIncids 4 4
Total Fire Incidents 65 65
AvgResponseTime 3:17 3:17
ALS_EngMedSvcsResp 11 11
BLS_EngMedSvcsResp 4 4
ALS_AMBSvcsResp 8 8
BLS_AMBSvcsResp 4 4
AvgRespTimeALSEngResp NA NA
AvgRespTimeBLSEngResp 3:16 3:16
AvgRespTimeALSAMBgResp 6:13 6:13
AvgRespTimeBLSAMBResp 12:17 12:17
NoStructInspectEd 3 3
NoStructInspectInspected 10 10
NoStructInspectInvestigated 15 15
NoHazardCodesViolIssued 6 6
NumInspected 4 4
NumIssued 9 9
Comments Third data dump
Notice that incidents for StructuralFires,non_StructuralFires,non_Fire_Emergencies,Fire_Alarms and Hazardous_MaterialsRespIncids are dded up to get 'Total Fire Incidents', just like you did it before.
Only thing missing from what you did before was that after the Total Fire Incidents sub total, the rest of the fieldnames from AvgResponseTime all the way down to Comments are included as well.
I don't know how difficult this will be for a great talent like you but that's the biggest issue so far.
April 13, 2010 at 11:13 am
-- 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
Union
Select 'Total Fire Incidents', CONVERT(NVarChar(150), SUM(CAST(W.Incidents As Int))),
CONVERT(NVarChar(150), SUM(CAST(Ytd.YtdIncidents As Int)))
From WeeklyReport As W
Inner Join YtdReport As Ytd On Ytd.IncidentType = W.IncidentType
Where (W.IncidentType In ('StructuralFires', 'Non_StructuralFires', 'Non_Fire_Emergencies',
'Fire_Alarms', 'Hazardous_MaterialsRespIncids'));
Go
April 13, 2010 at 12:38 pm
K Cline,
Even my father, the most patient man I know, will NOT be as patient as you have been with me so far and I worry but I may be using it all up.
However, I have no choice than to come back to you for more because the data is still not right.
The data produced by this last query:
Select 'Total Fire Incidents', CONVERT(NVarChar(150), SUM(CAST(W.Incidents As Int))),
CONVERT(NVarChar(150), SUM(CAST(Ytd.YtdIncidents As Int)))
From WeeklyReport As W
Inner Join YtdReport As Ytd On Ytd.IncidentType = W.IncidentType
Where (W.IncidentType In ('StructuralFires', 'Non_StructuralFires', 'Non_Fire_Emergencies',
'Fire_Alarms', 'Hazardous_MaterialsRespIncids'));
should NOT be at the very last end of the data layout.
It should be placed right after :
Hazardous_MaterialsRespIncids 4 12
but not at the end.
That query should SUM up (StructuralFires+Non_StructuralFires+Non_Fire_Emergencies+
Fire_Alarms+ Hazardous_MaterialsRespIncids) and then display their value below:
Hazardous_MaterialsRespIncids 4 12
In other words, it should be a SubTOTAL of StructuralFires+Non_StructuralFires+Non_Fire_Emergencies+
Fire_Alarms+ Hazardous_MaterialsRespIncids).
This was my point since Friday and I am confident I am not explaining it well.
PLEASE hang in there with me.
When I said that that was the last bit left, I meant it then and I mean it now but I don't care about 'Total Fire Incidents' being the GRAND TOTAL.
I want 'Total Fire Incidents' to be SubTotal.
I hope I am clear this time and I certainly hope that it is doable.
I am sooo sorry for all the pain.
MAY THE GOOD LORD BLESS YOU FOR YOUR GENEROSITY AND PATIENCE.
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply