• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 95
  • Last Modified:

SQL - Need to change this SP to sum all previous years

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.

Example:

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]
GO
CREATE PROCEDURE [dbo].[usp_SF6_GetCylinderBOY] 
	@CompanyID int,
	@YearPartDate datetime
AS 
BEGIN

SELECT SUM(GasWeight) 
FROM ( 
   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))
              ) sq
WHERE rn = 1
	
END
GO

Open in new window

0
CAMPzxzxDeathzxzx
Asked:
CAMPzxzxDeathzxzx
1 Solution
 
ste5anSenior DeveloperCommented:
Sounds like you're looking for:

ALTER PROCEDURE dbo.usp_SF6_GetCylinderBOY
    @CompanyID INT ,
    @YearPartDate DATETIME
AS 
    SET NOCOUNT ON;

    WITH    Filtered
              AS ( SELECT   WE.ContainerID ,
                            WE.GasWeight ,
                            ROW_NUMBER() OVER ( PARTITION BY WE.ContainerID ORDER BY WE.DateStamp DESC ) AS RN
                   FROM     WeighingEvents WE
                            INNER JOIN Container C ON WE.ContainerID = C.ContainerID
                   WHERE    C.ContainerTypeID = 1
                            AND WE.CompanyID = @CompanyID
                            AND WE.Active = 1
                            AND WE.DateStamp < DATEADD(YEAR, DATEDIFF(YEAR, 0, @YearPartDate), 0)
                 )
        SELECT  SUM(F.GasWeight)
        FROM    Filtered F
        WHERE   F.RN = 1 
GO

Open in new window

0
 
CAMPzxzxDeathzxzxAuthor Commented:
You rock dude
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now