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

I need max date and max time

I need this SP to return the 102.8 and not the 106.2


Capture.JPG
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[usp_Get_PreviousGasWeight]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[usp_Get_PreviousGasWeight];
GO

CREATE PROC [dbo].[usp_Get_PreviousGasWeight] 
		@ContainerID int,
		@CompanyID int
AS 
BEGIN
	SELECT [GasWeight] 
	FROM [dbo].[WeighingEvents]
	WHERE [ContainerID] = @ContainerID AND [CompanyID] = @CompanyID AND [Active] = 1
	ORDER BY [DateStamp] DESC
				
END
GO

Open in new window

0
CAMPzxzxDeathzxzx
Asked:
CAMPzxzxDeathzxzx
  • 2
1 Solution
 
Scott PletcherSenior DBACommented:
Verify the contents of these columns in the table:
[ContainerID]
[CompanyID]
[Active]

There must be something preventing the row you want and expect from being considered by the query.  Not active?!
0
 
PortletPaulfreelancerCommented:
You have ORDER BY [DateStamp] DESC so 2017-03-08 11:40 (102.8) is later than 2017-03-08 10:00 (106.2)

You also seem to only want one value... but the query isn't limited to one row. Do you need MAX() perhaps?

      SELECT MAX( [GasWeight] )
      FROM [dbo].[WeighingEvents]
      WHERE [ContainerID] = @ContainerID AND [CompanyID] = @CompanyID AND [Active] = 1
      /* ORDER BY [DateStamp] DESC */
0
 
PortletPaulfreelancerCommented:
While I'm not sure what you really want, perhaps this will help:
SELECT
      *
FROM (
      SELECT
            *
          , ROW_NUMBER() OVER (ORDER BY [DateStamp] DESC) AS rn
      FROM [dbo].[WeighingEvents]
      WHERE [ContainerID] = @ContainerID
      AND [CompanyID] = @CompanyID
      AND [Active] = 1) AS d
WHERE rn = 1

Open in new window

0
 
CAMPzxzxDeathzxzxAuthor Commented:
Thanks
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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