I need help getting this to work. I need to get a sum value for all previous years without double counting any cylinder gas weight.
Cyl1 has a weighing event in each of the previous 5 years and this year.
The only weighing event that should be counted towards the total sum is the most recent weighing event LAST year.
Cyl2 had a weighing event 5 years ago and 4 years ago.
The only weighing event that should be counted towards the total sum is the one 4 years ago with the max Date for that year.
Note: All cylinders may be weighed more than once a year so it has to be the Max Date for the year used.
I also need a variant of this SP that includes the most recent weighing event including current year.
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[usp_SF6_GetCylinderBOY]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[usp_SF6_GetCylinderBOY]
CREATE PROCEDURE [dbo].[usp_SF6_GetCylinderBOY]
select we.ContainerID, we.GasWeight,
row_number() over (partition by we.ContainerID order by we.DateStamp desc) rn
FROM [WeighingEvents] we
JOIN [Container] c ON we.[ContainerID] = c.[ContainerID]
WHERE c.ContainerTypeID = 1 AND we.[CompanyID] = @CompanyID AND we.[Active] = 1 AND (YEAR(we.[DateStamp]) = YEAR(@YearPartDate))
WHERE rn = 1