thank you
that data you pasted will work
i will work on that
hi
what do you mean by this ?
Secondly, unfortunately it did not total as the year went on. They appear to just be monthly totals still.
also what are you trying to do here
DATEFROMPARTS(YEAR([Incident_Date]), MONTH([Incident_Date]), 1) AS [Incident_Date]
you want each incident _date to go to the first of the month
'2021-12-15'
becomes
'2021-12-01'
how do want the data to look like ? like this ?
Hey, regarding your second question first. Even though there are records found throughout each month, ie: 12/9/2022 or 12/22/2022, they should all be counted as just 12/1/2022. One row per month, essentially. Anything that happened in December 2022 should have a Incident_Date of 12/01/2022. I think you’re showing it correctly.
Regarding your first question, your first code gave me the totals for each month, showing on the first date of each month, unless I’m mistaken. I need them to add up as the year goes along.
So, 1/1/2022 should show all January’s records. 2/1/2022 would show all January’s and February’s records. 3/1/2022 would show all three month. If you work up to the next year it would start over for 1/1/2023
Ok got you
I will work on it
Thank you
hi
i came up with this .. please have a look .. does this look like something you want
running total means what ? please share sample data how it should look
if you want each row by month .. but talking about yearly
what does that mean ?
; with cte as
(
SELECT
YEAR([Incident_Date]) as Year_IncDate
, case when Operating_Area = Operating_Area then 'AllGroups' end as 'AllGroups'
, CASE WHEN [Recordable_Injury] IS NOT NULL THEN 1 ELSE 0 END AS [RIF]
, CASE WHEN [Recordable_Injury] IN ('Lost Time', 'Fatality') THEN 1 ELSE 0 END AS [LTIR]
, CASE WHEN [PSET1_2] IS NOT NULL THEN 1 ELSE 0 END AS [PSER]
FROM
[dbo].[EHS_Reporting_Records]
UNION ALL
SELECT
YEAR([Incident_Date]) as Year_IncDate
, case WHEN Operating_Area in ('AreaA', 'AreaC', 'AreaD', 'AreaE', 'AreaF', 'AreaG', 'AreaB', 'AreaH', 'AreaI', 'AreaJ', 'AreaK') then 'GroupA' end as 'GroupA'
, CASE WHEN [Recordable_Injury] IS NOT NULL THEN 1 ELSE 0 END AS [RIF]
, CASE WHEN [Recordable_Injury] IN ('Lost Time', 'Fatality') THEN 1 ELSE 0 END AS [LTIR]
, CASE WHEN [PSET1_2] IS NOT NULL THEN 1 ELSE 0 END AS [PSER]
FROM
[dbo].[EHS_Reporting_Records]
UNION ALL
SELECT
YEAR([Incident_Date]) as Year_IncDate
, case WHEN Operating_Area in ('AreaA', 'AreaC', 'AreaD', 'AreaE', 'AreaF', 'AreaG', 'AreaB', 'AreaH', 'AreaJ', 'AreaK') then 'GroupB' end as 'GroupB'
, CASE WHEN [Recordable_Injury] IS NOT NULL THEN 1 ELSE 0 END AS [RIF]
, CASE WHEN [Recordable_Injury] IN ('Lost Time', 'Fatality') THEN 1 ELSE 0 END AS [LTIR]
, CASE WHEN [PSET1_2] IS NOT NULL THEN 1 ELSE 0 END AS [PSER]
FROM
[dbo].[EHS_Reporting_Records]
UNION ALL
SELECT
YEAR([Incident_Date]) as Year_IncDate
, case WHEN Operating_Area in ('AreaC', 'AreaD', 'AreaE', 'AreaG', 'AreaH', 'AreaJ', 'AreaK') then 'GroupC' end as 'GroupC'
, CASE WHEN [Recordable_Injury] IS NOT NULL THEN 1 ELSE 0 END AS [RIF]
, CASE WHEN [Recordable_Injury] IN ('Lost Time', 'Fatality') THEN 1 ELSE 0 END AS [LTIR]
, CASE WHEN [PSET1_2] IS NOT NULL THEN 1 ELSE 0 END AS [PSER]
FROM
[dbo].[EHS_Reporting_Records]
UNION ALL
SELECT
YEAR([Incident_Date]) as Year_IncDate
, case WHEN Operating_Area in ('AreaC', 'AreaD', 'AreaE', 'AreaG', 'AreaH', 'AreaK') then 'GroupD' end as 'GroupD'
, CASE WHEN [Recordable_Injury] IS NOT NULL THEN 1 ELSE 0 END AS [RIF]
, CASE WHEN [Recordable_Injury] IN ('Lost Time', 'Fatality') THEN 1 ELSE 0 END AS [LTIR]
, CASE WHEN [PSET1_2] IS NOT NULL THEN 1 ELSE 0 END AS [PSER]
FROM
[dbo].[EHS_Reporting_Records]
UNION ALL
SELECT
YEAR([Incident_Date]) as Year_IncDate
, case WHEN Operating_Area in ('AreaA', 'AreaB') then 'GroupE' end as 'GroupE'
, CASE WHEN [Recordable_Injury] IS NOT NULL THEN 1 ELSE 0 END AS [RIF]
, CASE WHEN [Recordable_Injury] IN ('Lost Time', 'Fatality') THEN 1 ELSE 0 END AS [LTIR]
, CASE WHEN [PSET1_2] IS NOT NULL THEN 1 ELSE 0 END AS [PSER]
FROM
[dbo].[EHS_Reporting_Records]
UNION ALL
SELECT
YEAR([Incident_Date]) as Year_IncDate
, case WHEN Operating_Area in ('AreaA', 'AreaF', 'AreaB') then 'GroupF' end as 'GroupF'
, CASE WHEN [Recordable_Injury] IS NOT NULL THEN 1 ELSE 0 END AS [RIF]
, CASE WHEN [Recordable_Injury] IN ('Lost Time', 'Fatality') THEN 1 ELSE 0 END AS [LTIR]
, CASE WHEN [PSET1_2] IS NOT NULL THEN 1 ELSE 0 END AS [PSER]
FROM
[dbo].[EHS_Reporting_Records]
)
select distinct
Year_IncDate
, [Allgroups] as [Operating_Area]
, sum(RIF) OVER (ORDER BY Year_IncDate,[Allgroups]) as RIF
, sum(LTIR) OVER (ORDER BY Year_IncDate,[Allgroups]) as LTIR
, sum(PSER) OVER (ORDER BY Year_IncDate,[Allgroups]) as PSER
from
cte
where
[Allgroups] is not null
group by
Year_IncDate
, [Allgroups]
, RIF
, LTIR
, PSER
order by
Year_IncDate
, [Allgroups]
, RIF
, LTIR
, PSER
another way to look at it
; with cte as
(
SELECT
YEAR([Incident_Date]) as Year_IncDate
, case when Operating_Area = Operating_Area then 'AllGroups' end as 'AllGroups'
, CASE WHEN [Recordable_Injury] IS NOT NULL THEN 1 ELSE 0 END AS [RIF]
, CASE WHEN [Recordable_Injury] IN ('Lost Time', 'Fatality') THEN 1 ELSE 0 END AS [LTIR]
, CASE WHEN [PSET1_2] IS NOT NULL THEN 1 ELSE 0 END AS [PSER]
FROM
[dbo].[EHS_Reporting_Records]
UNION ALL
SELECT
YEAR([Incident_Date]) as Year_IncDate
, case WHEN Operating_Area in ('AreaA', 'AreaC', 'AreaD', 'AreaE', 'AreaF', 'AreaG', 'AreaB', 'AreaH', 'AreaI', 'AreaJ', 'AreaK') then 'GroupA' end as 'GroupA'
, CASE WHEN [Recordable_Injury] IS NOT NULL THEN 1 ELSE 0 END AS [RIF]
, CASE WHEN [Recordable_Injury] IN ('Lost Time', 'Fatality') THEN 1 ELSE 0 END AS [LTIR]
, CASE WHEN [PSET1_2] IS NOT NULL THEN 1 ELSE 0 END AS [PSER]
FROM
[dbo].[EHS_Reporting_Records]
UNION ALL
SELECT
YEAR([Incident_Date]) as Year_IncDate
, case WHEN Operating_Area in ('AreaA', 'AreaC', 'AreaD', 'AreaE', 'AreaF', 'AreaG', 'AreaB', 'AreaH', 'AreaJ', 'AreaK') then 'GroupB' end as 'GroupB'
, CASE WHEN [Recordable_Injury] IS NOT NULL THEN 1 ELSE 0 END AS [RIF]
, CASE WHEN [Recordable_Injury] IN ('Lost Time', 'Fatality') THEN 1 ELSE 0 END AS [LTIR]
, CASE WHEN [PSET1_2] IS NOT NULL THEN 1 ELSE 0 END AS [PSER]
FROM
[dbo].[EHS_Reporting_Records]
UNION ALL
SELECT
YEAR([Incident_Date]) as Year_IncDate
, case WHEN Operating_Area in ('AreaC', 'AreaD', 'AreaE', 'AreaG', 'AreaH', 'AreaJ', 'AreaK') then 'GroupC' end as 'GroupC'
, CASE WHEN [Recordable_Injury] IS NOT NULL THEN 1 ELSE 0 END AS [RIF]
, CASE WHEN [Recordable_Injury] IN ('Lost Time', 'Fatality') THEN 1 ELSE 0 END AS [LTIR]
, CASE WHEN [PSET1_2] IS NOT NULL THEN 1 ELSE 0 END AS [PSER]
FROM
[dbo].[EHS_Reporting_Records]
UNION ALL
SELECT
YEAR([Incident_Date]) as Year_IncDate
, case WHEN Operating_Area in ('AreaC', 'AreaD', 'AreaE', 'AreaG', 'AreaH', 'AreaK') then 'GroupD' end as 'GroupD'
, CASE WHEN [Recordable_Injury] IS NOT NULL THEN 1 ELSE 0 END AS [RIF]
, CASE WHEN [Recordable_Injury] IN ('Lost Time', 'Fatality') THEN 1 ELSE 0 END AS [LTIR]
, CASE WHEN [PSET1_2] IS NOT NULL THEN 1 ELSE 0 END AS [PSER]
FROM
[dbo].[EHS_Reporting_Records]
UNION ALL
SELECT
YEAR([Incident_Date]) as Year_IncDate
, case WHEN Operating_Area in ('AreaA', 'AreaB') then 'GroupE' end as 'GroupE'
, CASE WHEN [Recordable_Injury] IS NOT NULL THEN 1 ELSE 0 END AS [RIF]
, CASE WHEN [Recordable_Injury] IN ('Lost Time', 'Fatality') THEN 1 ELSE 0 END AS [LTIR]
, CASE WHEN [PSET1_2] IS NOT NULL THEN 1 ELSE 0 END AS [PSER]
FROM
[dbo].[EHS_Reporting_Records]
UNION ALL
SELECT
YEAR([Incident_Date]) as Year_IncDate
, case WHEN Operating_Area in ('AreaA', 'AreaF', 'AreaB') then 'GroupF' end as 'GroupF'
, CASE WHEN [Recordable_Injury] IS NOT NULL THEN 1 ELSE 0 END AS [RIF]
, CASE WHEN [Recordable_Injury] IN ('Lost Time', 'Fatality') THEN 1 ELSE 0 END AS [LTIR]
, CASE WHEN [PSET1_2] IS NOT NULL THEN 1 ELSE 0 END AS [PSER]
FROM
[dbo].[EHS_Reporting_Records]
)
select distinct
[Allgroups] as [Operating_Area]
, Year_IncDate
, sum(RIF) OVER (ORDER BY Year_IncDate,[Allgroups]) as RIF
, sum(LTIR) OVER (ORDER BY Year_IncDate,[Allgroups]) as LTIR
, sum(PSER) OVER (ORDER BY Year_IncDate,[Allgroups]) as PSER
from
cte
where
[Allgroups] is not null
group by
[Allgroups]
, Year_IncDate
, RIF
, LTIR
, PSER
order by
[Allgroups]
, Year_IncDate
, RIF
, LTIR
, PSER
I tried running this, but I don't think you have it set up as Create view. I added the top part:
CREATE VIEW [dbo].[EHS_Reporting_Records_Count_Combined_New]
AS
with cte as
but it's giving me a syntax error.
Msg 1033, Level 15, State 1, Procedure EHS_Reporting_Records_Count_Combined_New, Line 89 [Batch Start Line 0]
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
remove the order by
Ah, that code ran. So, a couple things I guess.
It seems as though it's adding up, however it's not broken up by year. From the data I gave you, it seems like it's just adding up from 2016 on. It needs to start over each January.
Also, you've created a record called Allgroups which I think is summarizing all the remaining Areas, but I don't want that. They all need to be there and counted, as well as the new Groups
hi hopefully this it
this is the code please click on arrow
; with cte as
(
SELECT
YEAR([Incident_Date]) as Year_IncDate
, case WHEN Operating_Area in ('AreaA', 'AreaC', 'AreaD', 'AreaE', 'AreaF', 'AreaG', 'AreaB', 'AreaH', 'AreaI', 'AreaJ', 'AreaK') then 'GroupA' end as [Operating_Area]
, CASE WHEN [Recordable_Injury] IS NOT NULL THEN 1 ELSE 0 END AS [RIF]
, CASE WHEN [Recordable_Injury] IN ('Lost Time', 'Fatality') THEN 1 ELSE 0 END AS [LTIR]
, CASE WHEN [PSET1_2] IS NOT NULL THEN 1 ELSE 0 END AS [PSER]
FROM
[dbo].[EHS_Reporting_Records]
UNION ALL
SELECT
YEAR([Incident_Date]) as Year_IncDate
, case WHEN Operating_Area in ('AreaA', 'AreaC', 'AreaD', 'AreaE', 'AreaF', 'AreaG', 'AreaB', 'AreaH', 'AreaJ', 'AreaK') then 'GroupB' end
, CASE WHEN [Recordable_Injury] IS NOT NULL THEN 1 ELSE 0 END AS [RIF]
, CASE WHEN [Recordable_Injury] IN ('Lost Time', 'Fatality') THEN 1 ELSE 0 END AS [LTIR]
, CASE WHEN [PSET1_2] IS NOT NULL THEN 1 ELSE 0 END AS [PSER]
FROM
[dbo].[EHS_Reporting_Records]
UNION ALL
SELECT
YEAR([Incident_Date]) as Year_IncDate
, case WHEN Operating_Area in ('AreaC', 'AreaD', 'AreaE', 'AreaG', 'AreaH', 'AreaJ', 'AreaK') then 'GroupC' end
, CASE WHEN [Recordable_Injury] IS NOT NULL THEN 1 ELSE 0 END AS [RIF]
, CASE WHEN [Recordable_Injury] IN ('Lost Time', 'Fatality') THEN 1 ELSE 0 END AS [LTIR]
, CASE WHEN [PSET1_2] IS NOT NULL THEN 1 ELSE 0 END AS [PSER]
FROM
[dbo].[EHS_Reporting_Records]
UNION ALL
SELECT
YEAR([Incident_Date]) as Year_IncDate
, case WHEN Operating_Area in ('AreaC', 'AreaD', 'AreaE', 'AreaG', 'AreaH', 'AreaK') then 'GroupD' end
, CASE WHEN [Recordable_Injury] IS NOT NULL THEN 1 ELSE 0 END AS [RIF]
, CASE WHEN [Recordable_Injury] IN ('Lost Time', 'Fatality') THEN 1 ELSE 0 END AS [LTIR]
, CASE WHEN [PSET1_2] IS NOT NULL THEN 1 ELSE 0 END AS [PSER]
FROM
[dbo].[EHS_Reporting_Records]
UNION ALL
SELECT
YEAR([Incident_Date]) as Year_IncDate
, case WHEN Operating_Area in ('AreaA', 'AreaB') then 'GroupE' end
, CASE WHEN [Recordable_Injury] IS NOT NULL THEN 1 ELSE 0 END AS [RIF]
, CASE WHEN [Recordable_Injury] IN ('Lost Time', 'Fatality') THEN 1 ELSE 0 END AS [LTIR]
, CASE WHEN [PSET1_2] IS NOT NULL THEN 1 ELSE 0 END AS [PSER]
FROM
[dbo].[EHS_Reporting_Records]
UNION ALL
SELECT
YEAR([Incident_Date]) as Year_IncDate
, case WHEN Operating_Area in ('AreaA', 'AreaF', 'AreaB') then 'GroupF' end
, CASE WHEN [Recordable_Injury] IS NOT NULL THEN 1 ELSE 0 END AS [RIF]
, CASE WHEN [Recordable_Injury] IN ('Lost Time', 'Fatality') THEN 1 ELSE 0 END AS [LTIR]
, CASE WHEN [PSET1_2] IS NOT NULL THEN 1 ELSE 0 END AS [PSER]
FROM
[dbo].[EHS_Reporting_Records]
)
select distinct
Year_IncDate
, [Operating_Area]
, sum(RIF) OVER (partition by Year_IncDate ORDER BY Year_IncDate,[Operating_Area]) as RIF
, sum(LTIR) OVER (partition by Year_IncDate ORDER BY Year_IncDate,[Operating_Area]) as LTIR
, sum(PSER) OVER (partition by Year_IncDate ORDER BY Year_IncDate,[Operating_Area]) as PSER
from
cte
where [Operating_Area] is not null
group by
Year_IncDate
, [Operating_Area]
, RIF
, LTIR
, PSER
I'm so sorry this isn't working out. I'm afraid I'm getting results similar to what you're showing in the image.. for example, you're seeing 2,4,6,8,10,12, and then it repeats the next year.
I hate to be any further bother; you've gone well above and beyond what I could have hoped for from a support standpoint. I really appreciate you taking all this time.
I guess, for example, here's what 2021 and 2022 should look like, just for RIF for GroupA
| Date | Operating_Area | RIF |
|---|---|---|
| 1/1/2021 | GroupA | 9 |
| 2/1/2021 | GroupA | 16 |
| 3/1/2021 | GroupA | 19 |
| 4/1/2021 | GroupA | 23 |
| 5/1/2021 | GroupA | 25 |
| 6/1/2021 | GroupA | 27 |
| 7/1/2021 | GroupA | 31 |
| 8/1/2021 | GroupA | 32 |
| 9/1/2021 | GroupA | 37 |
| 10/1/2021 | GroupA | 40 |
| 11/1/2021 | GroupA | 43 |
| 12/1/2021 | GroupA | 46 |
| 1/1/2022 | GroupA | 8 |
| 2/1/2022 | GroupA | 14 |
| 3/1/2022 | GroupA | 17 |
| 4/1/2022 | GroupA | 19 |
| 5/1/2022 | GroupA | 24 |
| 6/1/2022 | GroupA | 27 |
| 7/1/2022 | GroupA | 30 |
| 8/1/2022 | GroupA | 36 |
| 9/1/2022 | GroupA | 37 |
| 10/1/2022 | GroupA | 45 |
| 11/1/2022 | GroupA | 49 |
| 12/1/2022 | GroupA | 54 |
The OP should provide the expected results for the provided test data.
The desired output is still far from clear.
Whatever the desired output I think it would benefit from an AreaGroups table. I have used a CTE.
Hacking Harish's code, here is my guess:
WITH AreaGroups
AS
(
SELECT Operating_Area, Operating_Group
FROM
(
VALUES ('AreaA', 'GroupA'),('AreaC', 'GroupA'),('AreaD', 'GroupA'),('AreaE', 'GroupA'),('AreaF', 'GroupA')
,('AreaG', 'GroupA'),('AreaB', 'GroupA'),('AreaH', 'GroupA'),('AreaI', 'GroupA'),('AreaJ', 'GroupA'),('AreaK', 'GroupA')
,('AreaA', 'GroupB'),('AreaC', 'GroupB'),('AreaD', 'GroupB'),('AreaE', 'GroupB'),('AreaF', 'GroupB'),('AreaG', 'GroupB')
,('AreaB', 'GroupB'),('AreaH', 'GroupB'),('AreaJ', 'GroupB'),('AreaK', 'GroupB')
,('AreaC', 'GroupC'),('AreaD', 'GroupC'),('AreaE', 'GroupC'),('AreaG', 'GroupC')
,('AreaH', 'GroupC'),('AreaJ', 'GroupC'),('AreaK', 'GroupC')
,('AreaC', 'GroupD'),('AreaD', 'GroupD'),('AreaE', 'GroupD')
,('AreaG', 'GroupD'),('AreaH', 'GroupD'),('AreaK', 'GroupD')
,('AreaA', 'GroupE'),('AreaB', 'GroupE')
,('AreaA', 'GroupF'),('AreaB', 'GroupF'),('AreaF', 'GroupF')
) V (Operating_Area, Operating_Group)
)
,MonthlyValues
AS
(
SELECT X.Incident_Month, A.Operating_Group
,SUM(CASE WHEN E.Recordable_Injury IS NOT NULL THEN 1 ELSE 0 END) AS RIF
,SUM(CASE WHEN E.Recordable_Injury IN ('Lost Time', 'Fatality') THEN 1 ELSE 0 END) AS LTIR
,SUM(CASE WHEN E.PSET1_2 IS NOT NULL THEN 1 ELSE 0 END) AS PSER
FROM dbo.EHS_Reporting_Records E
JOIN AreaGroups A
ON E.Operating_Area = A.Operating_Area
CROSS APPLY
(
VALUES(DATEFROMPARTS(YEAR(E.Incident_Date), MONTH(E.Incident_Date), 1))
) X (Incident_Month)
GROUP BY X.Incident_Month, A.Operating_Group
)
SELECT M.Incident_Month, M.Operating_Group
,SUM(RIF) OVER (PARTITION BY X.Incident_Year, M.Operating_Group ORDER BY M.Incident_Month) AS RIF
,SUM(LTIR) OVER (PARTITION BY X.Incident_Year, M.Operating_Group ORDER BY M.Incident_Month) AS LTIR
,SUM(PSER) OVER (PARTITION BY X.Incident_Year, M.Operating_Group ORDER BY M.Incident_Month) AS PSER
FROM MonthlyValues M
CROSS APPLY
(
VALUES(YEAR(M.Incident_Month))
) X (Incident_Year)
ORDER BY M.Incident_Month, M.Operating_Group;Thank you for your help, Ifor. I can see how providing my desired solution would have helped earlier, and I apologize I didn't do that. I have adjusted your code to creating a view and organized it so it's a little easier for me to read. It is providing the results I need, with the exception of the original records. While I am indeed seeing all the Grouped counts, I still need to have the original Areas ungrouped as well.. AreaA, AreaB, etc. Would you be able to alter your code to provide that for me please?
Thank you again, so much.
Okay, I believe I answered my own question. I added new sections for each area. For example,
('AreaA', 'AreaA') and that seems to be working.
The only other problem I have is that it leaves out months where there are no new records. For example, if there is 1 RIF in 1/1/2022 and then the next one is in 3/1/2022, there will be no row for 2/1/2022. I think this is an important step that is vital. I'm going to need to be able to look up a certain month of the year and find out how many injuries (RIF, LTIR, or PSE).
SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
CREATE OR ALTER VIEW dbo.EHS_Reporting_Records_Count_Combined_New
AS
WITH AreaGroups
AS
(
SELECT Operating_Area, Operating_Group
FROM
(
VALUES ('AreaA', 'GroupA'),('AreaC', 'GroupA'),('AreaD', 'GroupA'),('AreaE', 'GroupA'),('AreaF', 'GroupA')
,('AreaG', 'GroupA'),('AreaB', 'GroupA'),('AreaH', 'GroupA'),('AreaI', 'GroupA'),('AreaJ', 'GroupA'),('AreaK', 'GroupA')
,('AreaA', 'GroupB'),('AreaC', 'GroupB'),('AreaD', 'GroupB'),('AreaE', 'GroupB'),('AreaF', 'GroupB'),('AreaG', 'GroupB')
,('AreaB', 'GroupB'),('AreaH', 'GroupB'),('AreaJ', 'GroupB'),('AreaK', 'GroupB')
,('AreaC', 'GroupC'),('AreaD', 'GroupC'),('AreaE', 'GroupC'),('AreaG', 'GroupC')
,('AreaH', 'GroupC'),('AreaJ', 'GroupC'),('AreaK', 'GroupC')
,('AreaC', 'GroupD'),('AreaD', 'GroupD'),('AreaE', 'GroupD')
,('AreaG', 'GroupD'),('AreaH', 'GroupD'),('AreaK', 'GroupD')
,('AreaA', 'GroupE'),('AreaB', 'GroupE')
,('AreaA', 'GroupF'),('AreaB', 'GroupF'),('AreaF', 'GroupF')
,('AreaA','AreaA'),('AreaB','AreaB'),('AreaC','AreaC'),('AreaD','AreaD'),('AreaE','AreaE'),('AreaF','AreaF')
,('AreaG','AreaG'),('AreaH','AreaH'),('AreaI','AreaI'),('AreaJ','AreaJ'),('AreaK','AreaK')
) V (Operating_Area, Operating_Group)
)
/* For speed AllMonthGroups should be a table */
/* Generate Number Table N */
,N10 AS (SELECT N FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) V (N))
,N100 AS (SELECT N1.N FROM N10 N1, N10 N2)
/*,N10000 AS (SELECT N1.N FROM N100 N1, N100 N2)*/
,N(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) -1 FROM N100)
/* End Generate Number Table N */
,OGroups AS (SELECT DISTINCT Operating_Group FROM AreaGroups)
,MonthDetails
AS
(
SELECT DATEADD(month, DATEDIFF(month, 0, MIN(Incident_Date)), 0) AS MinMonth
,DATEDIFF(month, MIN(Incident_Date), MAX(Incident_Date)) + 1 AS NoOfMonths
FROM dbo.EHS_Reporting_Records
)
,AllMonthGroups(Incident_Month, Operating_Group)
AS
(
SELECT DATEADD(month, N.N, M.MinMonth), O.Operating_Group
FROM N
CROSS JOIN MonthDetails M
CROSS JOIN OGroups O
WHERE N.N < M.NoOfMonths
)
/* End AllMonthGroups */
,MonthlyValues
AS
(
SELECT X.Incident_Month, A.Operating_Group
,SUM(CASE WHEN E.Recordable_Injury IS NOT NULL THEN 1 ELSE 0 END) AS RIF
,SUM(CASE WHEN E.Recordable_Injury IN ('Lost Time', 'Fatality') THEN 1 ELSE 0 END) AS LTIR
,SUM(CASE WHEN E.PSET1_2 IS NOT NULL THEN 1 ELSE 0 END) AS PSER
FROM dbo.EHS_Reporting_Records E
JOIN AreaGroups A
ON E.Operating_Area = A.Operating_Area
CROSS APPLY
(
VALUES(DATEFROMPARTS(YEAR(E.Incident_Date), MONTH(E.Incident_Date), 1))
) X (Incident_Month)
GROUP BY X.Incident_Month, A.Operating_Group
)
SELECT A.Incident_Month, A.Operating_Group
,SUM(COALESCE(M.RIF, 0)) OVER (PARTITION BY X.Incident_Year, A.Operating_Group ORDER BY A.Incident_Month) AS RIF
,SUM(COALESCE(M.LTIR, 0)) OVER (PARTITION BY X.Incident_Year, A.Operating_Group ORDER BY A.Incident_Month) AS LTIR
,SUM(COALESCE(M.PSER, 0)) OVER (PARTITION BY X.Incident_Year, A.Operating_Group ORDER BY A.Incident_Month) AS PSER
FROM MonthlyValues M
CROSS APPLY
(
VALUES(YEAR(M.Incident_Month))
) X (Incident_Year)
RIGHT JOIN AllMonthGroups A
ON M.Incident_Month = A.Incident_Month
AND M.Operating_Group = A.Operating_Group;
/*ORDER BY A.Incident_Month, A.Operating_Group;*/
GO
1 Like
I don't know what else to say, but thank you. This is incredible. Your help is greatly appreciated. Likewise, harishgg1 your help has been very welcome.
Thank you both
hi
here is another way .. using a seperate table for groups .. idea was to make the code short and neat
groups table
drop table if exists #Groups
create table #Groups ( GroupVal varchar(20) , GroupAct varchar(10))
insert into #Groups select 'GroupA','AreaA'
insert into #Groups select 'GroupA','AreaC'
insert into #Groups select 'GroupA','AreaD'
insert into #Groups select 'GroupA','AreaE'
insert into #Groups select 'GroupA','AreaF'
insert into #Groups select 'GroupA','AreaG'
insert into #Groups select 'GroupA','AreaB'
insert into #Groups select 'GroupA','AreaH'
insert into #Groups select 'GroupA','AreaI'
insert into #Groups select 'GroupA','AreaJ'
insert into #Groups select 'GroupA','AreaK'
insert into #Groups select 'GroupB','AreaA'
insert into #Groups select 'GroupB','AreaC'
insert into #Groups select 'GroupB','AreaD'
insert into #Groups select 'GroupB','AreaE'
insert into #Groups select 'GroupB','AreaF'
insert into #Groups select 'GroupB','AreaG'
insert into #Groups select 'GroupB','AreaB'
insert into #Groups select 'GroupB','AreaH'
insert into #Groups select 'GroupB','AreaJ'
insert into #Groups select 'GroupB','AreaK'
insert into #Groups select 'GroupC','AreaC'
insert into #Groups select 'GroupC','AreaD'
insert into #Groups select 'GroupC','AreaE'
insert into #Groups select 'GroupC','AreaG'
insert into #Groups select 'GroupC','AreaH'
insert into #Groups select 'GroupC','AreaJ'
insert into #Groups select 'GroupC','AreaK'
insert into #Groups select 'GroupD','AreaC'
insert into #Groups select 'GroupD','AreaD'
insert into #Groups select 'GroupD','AreaE'
insert into #Groups select 'GroupD','AreaG'
insert into #Groups select 'GroupD','AreaH'
insert into #Groups select 'GroupD','AreaK'
insert into #Groups select 'GroupE','AreaA'
insert into #Groups select 'GroupE','AreaB'
insert into #Groups select 'GroupF','AreaA'
insert into #Groups select 'GroupF','AreaF'
insert into #Groups select 'GroupF','AreaB'
SELECT distinct
YEAR([Incident_Date]) as Year_IncDate
, GroupVal as Opearting_Area
, sum(CASE WHEN [Recordable_Injury] IS NOT NULL THEN 1 ELSE 0 END) OVER (partition by YEAR([Incident_Date]) ORDER BY YEAR([Incident_Date]),GroupVal) AS [RIF]
, sum(CASE WHEN [Recordable_Injury] IN ('Lost Time', 'Fatality') THEN 1 ELSE 0 END) OVER (partition by YEAR([Incident_Date]) ORDER BY YEAR([Incident_Date]),GroupVal) AS [LTIR]
, sum(CASE WHEN [PSET1_2] IS NOT NULL THEN 1 ELSE 0 END) OVER (partition by YEAR([Incident_Date]) ORDER BY YEAR([Incident_Date]),GroupVal) AS [PSER]
FROM
EHS_Reporting_Records a
join
#Groups b
on a.Operating_Area = b.GroupAct
GROUP BY
YEAR([Incident_Date]) , GroupVal , [Recordable_Injury], [PSET1_2]
ORDER BY
YEAR([Incident_Date]) , GroupVal
