-1

I would like to query and get for all the records with the same datetime ,filter by Type , Provider and Operator.
If I run select distinct EventStamp I get all the records that I need, but I miss the type , provider and operator.
Like the screenshots if I have the Type OPR I need this record If all the types are DDE I need Provider RDSSERVER\Intouch.

enter image description here

 SELECT [EventStamp] ,[TagName] ,[Description],[Area] ,[Type],[Value], Antes FROM
(
SELECT distinct [EventStamp]
      ,[TagName]
      ,[Description]
      ,[Area]
      ,[Type]
      ,[Value]
      ,[CheckValue] as Antes

  FROM [dbo].[v_EventHistory] where type = 'OPR' 
  UNION
  SELECT distinct [EventStamp]
      ,[TagName]
      ,[Description]
      ,[Area]
      ,[Type]
      ,[Value]
      ,[CheckValue] as Antes

  FROM [dbo].[v_EventHistory] where type = 'DDE' 
)as EV order by EventStamp desc

With this code I miss the type , provider and operator.

I nee like this

enter image description here

Shared query from Hannover (Thank you)

enter image description here

yaqui
  • 99
  • 1
  • 9
  • I don't follow what you mean by that you "miss the type , provider and operator." What do you mean by "miss"? I thought you might mean that the column isn't in the result set, but it is included in the query. – Thom A Feb 01 '23 at 17:08
  • Also, why `UNION` (which also forces a distinct too) when you could just have a single `SELECT` and have an `IN` in the `WHERE`. – Thom A Feb 01 '23 at 17:10
  • I need to get in my result set only one record for duplicate records in the same datetime and I need my result set have the provider and operator in my query. I can´t get this. Thank you – yaqui Feb 01 '23 at 17:10
  • Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – Thom A Feb 01 '23 at 17:11
  • Clarifying question: if you have two types "OPR" and "DDE" for example for one timestamp, how would you like that displayed? It sounds like you only want one row for the timestamp. So would you have something like a) At least two columns (e.g., one column shows "OPR" one column on the same row shows "DDE" b) One column [Types] that shows "OPR,DDE" or c) something else? for example to account now you have different providers that match to each. You could potentially use string concatenation techniques (that may differ depending on version of SQL Server) or a PIVOT for some of the above. – Sean Feb 01 '23 at 20:01
  • @SeanBloch Yes. This is what I try to get. Only one record for the same datetime. Like I have commnet to Hannover If the duplicate records have diferents type, I must select the type OPR. If the duplicate records have the same type , I must select based on provider. – yaqui Feb 02 '23 at 05:50

1 Answers1

0

It sounds like you need to use IN to identify all the records with the same Event Stamp, Provider and Operator.

SELECT * 
FROM [dbo].[v_EventHistory] 
WHERE   CAST([EventStamp] AS VARCHAR(25)) + [Provider] + [Operator] 
            IN  (   SELECT DISTINCT CAST([EventStamp] AS VARCHAR(25)) + [Provider] + [Operator] 
                    FROM [dbo].[v_EventHistory] 
                    WHERE [type] IN  ( 'OPR', 'DDE' )
                )
ORDER BY EventStamp DESC
Hannover Fist
  • 10,393
  • 1
  • 18
  • 39
  • Still I have duplicate records, this is what I need to avoid . I atach the result set to a better comprehension – yaqui Feb 01 '23 at 17:33
  • @yaqui - there are two records due to having two different TYPE fields. Do you not want to show that field (which would make the records distinct), show only one value, or show both values in a single text field? – Hannover Fist Feb 01 '23 at 22:22
  • @Hanonver Yes, I only want to show one record for the same datetime. If the duplicate records have diferents type, I must select the type OPR. If the duplicate records have the same type , I must select based on provider. But only one records. Sorry if I don´t explain very clear. – yaqui Feb 02 '23 at 05:47