March 31, 2010 at 10:58 am
Greetings again honorable experts,
I pray that you can rescue me one more time as this forum has become my favorite forum.
Per the query below, I am I have Header text as follows:
Incident Type Reporting Week YTD
Structural Fires 1 1
Non-Structural Fires 3 5
etc
etc
Total Fire Incides 4 6
and the rest
My challenge now is to get the YTD totals for each row and of course grand total.
Any ideas how I can do this given that this is a weekly report where you enter startdate and enddate to generate a report.
How do I formulate the query so that weekly totals are getting added up to generate YTD totals?
Please see the query that I have so far. You can see from the code below that I have attempted to generate the YTD totals but not sure what I am doing wrong as each row is giving me same value.
Thanks again for all your help.
SELECT StructuralFires
,Non_StructuralFires
,Non_Fire_Emergencies
,Fire_Alarms
,Hazardous_MaterialsRespIncids
,(StructuralFires+Non_StructuralFires+Non_Fire_Emergencies+Fire_Alarms+Hazardous_MaterialsRespIncids) AS 'Total Fire Service Incidents'
,AvgResponseTime
,ALS_EngMedSvcsResp
,BLS_EngMedSvcsResp
,ALS_AMBSvcsResp
,BLS_AMBSvcsResp
,AvgRespTimeALSEngResp
,AvgRespTimeBLSEngResp
,AvgRespTimeALSAMBgResp
,AvgRespTimeBLSAMBResp
,NoStructInspectEd
,NoStructInspectInspected
,NoStructInspectInvestigated
,NoHazardCodesViolIssued
,NumInspected
,NumIssued
,Comments
,StartDate
,EndDate
,sum( IsNull(StructuralFires,0) + IsNull(Non_StructuralFires,0) + IsNull(Non_Fire_Emergencies,0) + IsNull(Fire_Alarms,0) + IsNull(Hazardous_MaterialsRespIncids,0) + IsNull(StructuralFires+Non_StructuralFires+Non_Fire_Emergencies+Fire_Alarms+Hazardous_MaterialsRespIncids,0)) as [YTD]
FROM FIREReportsData
Where StartDate = @StartDate
AND EndDate = @EndDate
GROUP BY 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
March 31, 2010 at 11:07 am
A few things tht would really help, the DDL (CREATE TABLE statement(s)) for the table(s) involved, sample data (series of INSERT INTO statements) for the table(s) involved, expected results based on the sample data you provide.
I'm sure there are people who will help without all that, but by providing this info, you will get more help faster and in return for your efforts you will also get TESTED code in return.
March 31, 2010 at 11:39 am
Hi Lynn,
Ok sure.
Here is sample data. I just started; so not much data at all.
******
DDL
********
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] [datetime] NULL,
[EndDate] [datetime] NULL,
CONSTRAINT [PK_FIREReportsData] PRIMARY KEY CLUSTERED
**********
Sample data
**********
1 1 1 0 8 0 5:13 17 8 16 0 5:13 5:13 13:54 No BLS 0 50 0 0 30 0 The increase number of inspections include Company Level inspections for the entire month of September 2009. 2009-09-26 00:00:00.000 2009-10-03 00:00:00.000
2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2009-09-25 00:00:00.000 2009-10-02 00:00:00.000
4 0 2 4 0 0 0 0 0 0 0 0 0 0 0 0 test 2009-09-27 00:00:00.000 2009-10-04 00:00:00.000
I showed the layout in my original post.
Please let me know if you need anything else.
Again, thanks alot for your help and the prompt response.
March 31, 2010 at 11:54 am
The sample data is not in a usable format, should be provided as a series of INSERT INTO statements that can be cut/paste/run in SSMS.
Also need the expected results based on the sample data. This gives us something to check the output against to be sure it returns what you expect.
March 31, 2010 at 12:22 pm
Hi Lynn,
Here is sample data (3) that you can cut/paste
INSERT INTO FIB.dbo.FIREReportsData
(DataGroupID
,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')
//**************************************
INSERT INTO FIB.dbo.FIREReportsData
(DataGroupID
,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
(
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')
//**************************************
INSERT INTO FIB.dbo.FIREReportsData
(DataGroupID
,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
(
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')
Based on above data,
if YTD column is computed, since we have 3 date ranges (9/24/2009-10/1/2009, 9/25/2009-10/2/2009, 9/26/2009-10/3/2009), if we sum up each row, for instance Sum(Structural Fires) for these 3 date ranges, then YTD would 15. This holds true for the rest of the fields.
What's more, the more date ranges are added, then of course YTD total changes as well.
Handling the YTD totals with date ranges is the biggest issue for me.
Again, thanks very much. I hope I have enough info.
Otherwise, please let me know.
March 31, 2010 at 9:24 pm
Try this:
SELECT ReportQID,sum(StructuralFires) as StructuralFires
,sum(Non_StructuralFires) as Non_StructuralFires
FROM FIREReportsData
GROUP BY ReportQID
with CUBE
Result
ReportQID StructuralFires Non_StructuralFires
----------- --------------- -------------------
1 1 1
2 7 1
3 7 33
NULL 15 35
You can see total by ReportQID, and grand total at the last row:-D
March 31, 2010 at 10:30 pm
Try what nguyennd posted, if it doesn't work repost your expected results. I don't mean using word, but show u what the result set should look like when the query is processed. Remember, a picture is worth a thousand words.
April 1, 2010 at 6:13 am
This is what my expected results should look like:
Incident Type Reporting Week YTD
Structural Fires 1 1
Non-Structural Fires 3 5
etc
etc
Total Fire Incides 4 6
and the rest
All the columns selected and we obtain YTD for each column.
April 1, 2010 at 7:03 am
As newbie as I am with SQL, I am beginning to feel a bit better because I thought this would be easy to seasoned veterans and I would be made to look like a fool.
April 1, 2010 at 4:58 pm
Couple of questions.
1. How do you define "Reporting Week"? Is each record considered a reporting week? I didn't see any other mechanism for keeping track of your weeks otherwise. Unless you are using the start and end dates, which leads me to question 2.
2. From the sample data you provided the start and end dates for the data dumps were 9/24/2009 to 10/1/2009, 9/25/2009 to 10/2/2009, and 9/26/2009 to 10/3/2009 respectively. So are your weeks "rolling weeks"?
3. If you are using a rolling week as your data suggests, then simply summing data will not provide you a result set with any meaning. The data either needs to be stored in terms of weeks or on a daily basis. Otherwise you are going to have to find the delta between each of the records and then sum that delta value.
4. In your sample output you have Structural Fires for week 1, but Non-Structural Fires for week 3, etc. Are you wanting to select one week and see the values for that individual week and also the YTD value? Or are you wanting to list out values for category for each week? I apologize but I am not clear on what you are asking for.
April 2, 2010 at 7:50 am
K Cline,
Thanks a lot for your interest in helping.
To answer your questions,
"Reporting Week" is defined as filling up an excel spreadsheet with data similar to what I showed in as sample data.
Is each record considered a reporting week?
If by Record you mean an entirely completed form, then yes.
I didn't see any other mechanism for keeping track of your weeks otherwise. Unless you are using the start and end dates
This is a very important point.
Perhaps, you can tell me of a mechanism for tracking the weeks.
I am sure that that will make the work of calculating YTD totals that much easier for me.
Right now, we use StartDate to EndDate.
So are your weeks "rolling weeks"?
If by Rolling Weeks you mean, generating cumulative weekly totals, in other words, adding last week's total to this week's total and adding this week's total to next week's total, etc, then NO; that's not the way it works.
Each week's report is different. The only column that keeps total for ALL weeks is YTD column.
Hopefully, this answers this particular question for you.
he data either needs to be stored in terms of weeks or on a daily basis. Otherwise you are going to have to find the delta between each of the records and then sum that delta value.
It will really help me if you can suggest how I can go about storing these data on a weekly or daily basis.
What is delta as it relates to data anyway?
In your sample output you have Structural Fires for week 1, but Non-Structural Fires for week 3, etc. Are you wanting to select one week and see the values for that individual week and also the YTD value? Or are you wanting to list out values for category for each week?
Structural Fires and Non-Structural Fires are two of the fieldnames and each of the fieldnames should have data every week.
We are not getting data for one fieldname one week and another fieldname, another week.
We are getting data for all fieldnames each week and also the YTD value exactly as you described here ->select one week and see the values for that individual week and also the YTD value
You have asked some great questions.
Hopefully, based on the questions you asked and the answers I provided, you or someone can suggest what changes I need to make to the db design in order to help me generate YTD values.
Again, thanks very much
April 2, 2010 at 11:17 am
I'm going to try and address things in smaller chunks. To cover everything at once can be a bit lengthy and confusing.
Sorry, I'll try and define what I mean by a rolling week. Most people think of a standard week as something like Sunday through Saturday. If the data were using standard weeks it would come in as:
week 1: 9/20/2009 to 9/26/2009
week 2: 9/27/2009 to 10/3/2009
week 3: 10/4/2009 to 10/10/2009, etc.
However, the sample data seemed to be following this pattern:
week 1: 9/24/2009 to 10/1/2009 (Thursday to Thursday)
week 2: 9/25/2009 to 10/2/2009 (Friday to Friday)
week 3: 9/26/2009 to 10/3/2009 (Saturday to Saturday)
I am guessing that the the end date is not inclusive, otherwise we have 8 day weeks. This is what I mean by a rolling week. The data is a snapshot of the most recent 7 day period, not a typical Sunday to Saturday calendar week.
The problem in trying to use this data in calculating YTD values is that the data for each day is saved 7 times. For instance the data for 10/1/2009 is saved in each of the following weeks' data: 9/25 - 10/2; 9/26 - 10/3; 9/27 - 10/4; 9/28 - 10/5; 9/29 - 10/6; 9/30 - 10/6; 10/1 - 10/7.
This prevents just using standard SUM() logic to find your YTD values.
If this is indeed how the reports are filed, is there any way to get the data on a daily basis? It would seem that the report is currently being saved every day. But instead of saving the past 7 days' results, can you just get the results for one day at time? The format of the input data will determine what options are available for producing the report.
April 2, 2010 at 12:04 pm
Thank you for clarifying things for me.
They start Saturday - Friday, Then Saturday through Friday, etc
That's how they do it.
If that's what you mean by rolling week, then yes, you are correct.
Your next question is whether there is a way to get data on a daily basis.
I asked that question and I am told NO.
I am told that they collect this data once a week and enter them into an Excel spreadsheet once a week.
They have various individuals collecting this data on a piece of paper and handing to them.
Then they choose one day of the week (for a particular date range) to enter all of that data for that particulare week (date range).
I am just thinking that there is something flag or trigger or something that can be treated as a fieldname and stored as a calculated value.
This fieldname will sum up each fieldnames numbers and saves that on the db.
Then we can just select it as YTD figure for that fieldname.
I honestly didn't this would be this challenging.
Thank you so very much for your assistance.
April 2, 2010 at 1:22 pm
They start Saturday - Friday, Then Saturday through Friday, etc
Based off of this I am guessing that you will have standard weeks and that the sample data overlapping was just due to the fact it was made up sample data.
Here is a shot at some code to possibly get you started. I didn't include all of the columns, but it should be enough that you hopefully see where it is going. I am sure the are ways to improve this or make it more efficient. If the results are not what you are looking for then let me know and we'll go from there.
-- Modified the columns to Not Null. At the least, set the integer values to Not Null and give
-- them a default. Saves from having to handle the Nulls in the later code.
-- Also changed DateTime columns to Date. Time doesn't seem to apply here, so why store it?
Declare @FireReportsData Table
(
[ReportQID] Int Identity(1,1) Not Null Primary Key,
[StructuralFires] Int Not Null Default (0),
[Non_StructuralFires] Int Not Null Default (0),
[Non_Fire_Emergencies] Int Not Null Default (0),
[Fire_Alarms] Int Not Null Default (0),
[Hazardous_MaterialsRespIncids] Int Not Null Default (0),
[AvgResponseTime] NVarChar(50) Not Null,
[ALS_EngMedSvcsResp] Int Not Null Default (0),
[BLS_EngMedSvcsResp] Int Not Null Default (0),
[ALS_AMBSvcsResp] Int Not Null Default (0),
[BLS_AMBSvcsResp] Int Not Null Default (0),
[AvgRespTimeALSEngResp] NVarChar(50) Not Null,
[AvgRespTimeBLSEngResp] NVarChar(50) Not Null,
[AvgRespTimeALSAMBgResp] NVarChar(50) Not Null,
[AvgRespTimeBLSAMBResp] NVarChar(50) Not Null,
[NoStructInspectEd] Int Not Null Default (0),
[NoStructInspectInspected] Int Not Null Default (0),
[NoStructInspectInvestigated] Int Not Null Default (0),
[NoHazardCodesViolIssued] Int Not Null Default (0),
[NumInspected] Int Not Null Default (0),
[NumIssued] Int Not Null Default (0),
[Comments] NVarChar(150) NULL,
[StartDate] Date Not Null,
[EndDate] Date Not Null
);
Insert Into @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/26/2009', '10/2/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', '10/3/2009', '10/9/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', '10/10/2009', '10/16/2009');
Declare @WeekId Int,
@ReportYear Int;
-- Assign the week's data you want to retrieve.
Set @WeekId = 2;
-- Determine the year you are working with.
Set @ReportYear =
(
Select YEAR(StartDate)
From @FireReportsData
Where (ReportQID = @WeekId)
);
-- 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 (ReportQID = @WeekId)
) 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
There are edge case issues with this data, i.e. when a week spans the end of one year and the beginning of another. You will need to determine how you want to handle that. Hopefully they will either give you a special dataset to handle that case or they don't care if their YTD count starts on the first full report week of a year.
** In a real table I would name my default constraints. That way you know what the constraint is just by looking at the name.
[StructuralFires] Int Not Null
Constraint [Df_FireReportsData_StructuralFires] Default (0),
April 2, 2010 at 2:39 pm
Thanks a lot K Cline for all the time you spent helping out on this.
I really, really appreciate it.
So, just looking at this code, it means that the user can no longer filter by date?
In other words,the user cannot enter Start Date and End Date to retrieve data for a given week?
Again, lots of thanks for your assistance.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply