How do I sum up YTD column?

Question

Post reply

How do I sum up YTD column?

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply

K Cline Ten Centuries Points: 1391 More actions · Answer 1

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.

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 2

Thank you very, very much K Cline.

I will give this a go and let you know how it turns out.

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 3

Thank you very, very much K Cline.

I will give this a go and let you know how it turns out.

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 4

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.

K Cline Ten Centuries Points: 1391 More actions · Answer 5

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

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 6

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.

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 7

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

K Cline Ten Centuries Points: 1391 More actions · Answer 8

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.

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 9

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

Total Fire Incides 4 6

new field value value

etc

and the rest

Again, many THANKS

K Cline Ten Centuries Points: 1391 More actions · Answer 10

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

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 11

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.

K Cline Ten Centuries Points: 1391 More actions · Answer 12

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';