Adjust T-SQL query to include DateTime (TimeStamp)

Question

Hi Guys,

I need some help with adjusting an existing query to to include a TimeStamp, which is actually the "DateTime" column in one of the views (vEvent, System Center Operations Manager Data Warehouse).
Long story short - I needed help to get some data in a specific form, so asked in the forums and after short discussion I got the query I needed. Here is the post:

T-SQL query to present the data in an more understandable/readable way
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/445785f6-b013-48c6-8dbf-e4d218f591a0/tsql-query-to-present-the-data-in-an-more-understandablereadable-way?forum=transactsql&prof=required

This post resulted in the following solutions, which works and delivers the data I need:

select ev.EventOriginId,
MAX(CASE WHEN ParameterIndex=1 THEN ParameterValue END) ParamtereValue1,
MAX(CASE WHEN ParameterIndex=2 THEN ParameterValue END) ParamtereValue3,
MAX(CASE WHEN ParameterIndex=3 THEN ParameterValue END) ParamtereValue3
FROM Event.vEvent ev inner join Event.vEventParameter evp
on ev.EventOriginId = evp.EventOriginId WHERE EventDisplayNumber = '2889'
GROUP BY ev.EventOriginId

The thing is that I now have to know the date of the respective events, so I tried modifying the query as the "DateTime" is a column from vEvent (same as EventOriginId). Unfortunately this query starts executing and never ends :

select ev.EventOriginId,ev.DateTime,
MAX(CASE WHEN ParameterIndex=1 THEN ParameterValue END) ParamtereValue1,
MAX(CASE WHEN ParameterIndex=2 THEN ParameterValue END) ParamtereValue3,
MAX(CASE WHEN ParameterIndex=3 THEN ParameterValue END) ParamtereValue3
FROM Event.vEvent ev inner join Event.vEventParameter evp
on ev.EventOriginId = evp.EventOriginId WHERE EventDisplayNumber = '2889'
GROUP BY ev.EventOriginId,ev.DateTime

Can you please help me to include the "DateTime" in the results?

Many thanks in advance for all suggestions!

Regards,
Stoyan

Answer 1

Hi Melissa,

I could not get it working with your example as it uses static values and does not meet the requirements. So a tried further and was able to get the exectution within an aceeptable timeframe by specifying a Filter and filtering out the results for a specific Date:

select ev.EventOriginId,ev.DateTime,
MAX(CASE WHEN ParameterIndex=1 THEN ParameterValue END) ParamtereValue1,
MAX(CASE WHEN ParameterIndex=2 THEN ParameterValue END) ParamtereValue3,
MAX(CASE WHEN ParameterIndex=3 THEN ParameterValue END) ParamtereValue3
FROM Event.vEvent ev inner join Event.vEventParameter evp
on ev.EventOriginId = evp.EventOriginId WHERE EventDisplayNumber = '2889' AND Datetime BETWEEN '2021-02-02 00:00:00.000' AND '2021-02-02 23:59:59.998'
GROUP BY ev.EventOriginId,ev.DateTime

This gave me the result I was striving for!

Many thanks for your time and effort!

Regards,
Stoyan

Answer 2

Hi @SChalakov ,

I reused your DDL and made some updates on your sample data as below:

DROP TABLE if exists vEventParameter  
DROP TABLE if exists vEvent  
  
CREATE TABLE vEventParameter(  
   EventOriginID VARCHAR(114) NOT NULL    
  ,ParameterIndex VARCHAR(30)  
  ,ParameterValue VARCHAR(30)  
);  
INSERT INTO vEventParameter(EventOriginID,ParameterIndex,ParameterValue) VALUES  
 ('ID1','1','Value1')  
,('ID1','2','Value2')  
,('ID1','3','Value3')  
,('ID2','1','Value1')  
,('ID2','2','Value2')  
,('ID2','3','Value3');  
  
CREATE TABLE vEvent(  
   EventOriginID VARCHAR(114) NOT NULL    
  ,[DateTime] VARCHAR(30)  
  ,EventDisplayNumber  VARCHAR(30)  
);  
INSERT INTO vEvent(EventOriginID, [DateTime],EventDisplayNumber)  
values('ID1','2021-02-01 00:00:00','2889')  
,('ID2','2021-02-02 00:00:00','2889')  
,('ID3','2021-02-03 00:00:00','2889')  
  
 select ev.EventOriginId, DateTime,  
 MAX(CASE WHEN ParameterIndex=1 THEN ParameterValue END) ParamtereValue1,  
 MAX(CASE WHEN ParameterIndex=2 THEN ParameterValue END) ParamtereValue3,  
 MAX(CASE WHEN ParameterIndex=3 THEN ParameterValue END) ParamtereValue3  
 FROM vEvent ev inner join vEventParameter evp  
 on ev.EventOriginId = evp.EventOriginId WHERE EventDisplayNumber = '2889'  
 GROUP BY ev.EventOriginId,ev.DateTime  

Output:

EventOriginId	DateTime	ParamtereValue1	ParamtereValue3	ParamtereValue3  
ID1	2021-02-01 00:00:00	Value1	Value2	Value3  
ID2	2021-02-02 00:00:00	Value1	Value2	Value3  

Above result could be correct as you described. If it was unexpected, please provide your sample data and expected output so that we would proceed with checking.

Thank you for understanding!

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.