Stored Proc - Get DISTINCT errors with a Date Range, not providing DISTINCT

Question

Stored Proc - Get DISTINCT errors with a Date Range, not providing DISTINCT

daerne

SSC Veteran

Points: 221
More actions
December 19, 2019 at 3:37 pm

#3709162
Good Morning everyone,
I'm attempting to Merge a couple of stored procedures into one. I need to be able to pass in a DISTINCT error message and a date range. Individually, they both work...however, when I merge them and try to run I don't get the DISTINCT errors. They all just come in.
Looking for some guidance if someone knows where I'm going wrong.
ALTER PROCEDURE [dbo].[GetUniqueValueNames]
@MessageProcessFolio nvarchar(75),
@StartDate DateTime = NULL,
@EndDate DateTime = NULL
AS
SELECT DISTINCT [MessageError], MessageLogDate
FROM [dbo].[LogArchive]
WHERE [MessageProcessFolio] = @MessageProcessFolio
AND MessageLogDate BETWEEN Coalesce(@StartDate, MessageLogDate) AND
Coalesce(@EndDate, MessageLogDate)
- This topic was modified 4 years, 11 months ago by daerne.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

oogibah SSC Eights! Points: 827 More actions · Answer 1

Just to clarify, when you say distinct errors are you wanting each individual error type to only show up once? With the way this is written if you have the same error a minute apart its going to show the same error and both times a minute apart because those combination of values is distinct.

daerne SSC Veteran Points: 221 More actions · Answer 2

Correct.

I am searching through a log file to get distinct error messages. The extra is to then grab them in a date range.

oogibah SSC Eights! Points: 827 More actions · Answer 3

This might not be 100% correct, but this seems to work for me but as a note, basically whats going to happen here is if the error occurred on multiple dates you aren't going to see that you're only going to see that on at least one of the days that error occurred which I believe is what you are asking for.

WITH CTE ([MessageError], [MessageLogDate], RowNumber)
AS (SELECT [MessageError], 
           [MessageLogDate],  
           ROW_NUMBER() OVER(PARTITION BY [MessageError]
  ORDER BY [MessageError])
   FROM dbo.[LogArchive])
SELECT CTE.[MessageError],CTE.[MessageLogDate]
FROM CTE
WHERE CTE.RowNumber='1'
ORDER BY CTE.[MessageError]

daerne SSC Veteran Points: 221 More actions · Answer 4

In my searching, I started seeing ROW_NUMBER and OVER search variations.

Thanks, I'll try this and try to understand those elements a little better.

oogibah SSC Eights! Points: 827 More actions · Answer 5

I'm learning myself but from my understanding, in doing this you're giving every iteration of that error message an assigned row number 1...2...3...4. depending on how many times it appears, and by saying only give me row number 1 you will only get that message once.

Row Number is important here but the main keyword in play is Partition By

frederico_fonseca SSCoach Points: 16157 More actions · Answer 6

a simple group by and max would give the desired result without the need to do a sort/numbering with row_number

ALTER PROCEDURE dbo.GetUniqueValueNames
@MessageProcessFolio nvarchar(75),
StartDate DateTime = NULL,
@EndDate DateTime = NULL

AS

SELECT MessageError, max(MessageLogDate) as MessageLogDate
FROM dbo.LogArchive
WHERE MessageProcessFolio = @MessageProcessFolio
AND MessageLogDate BETWEEN Coalesce(@StartDate, MessageLogDate) AND Coalesce(@EndDate, MessageLogDate)
group by MessageError

daerne SSC Veteran Points: 221 More actions · Answer 7

SSChampion,

Thanks that worked. max(MessageLogDate) as MessageLogDate was what I was missing.

oogibah SSC Eights! Points: 827 More actions · Answer 8

Makes sense, I messed with that for a second but I was being dumb about it.