Adjust T-SQL query to include DateTime (TimeStamp)

Question

question

StoyanChalakov asked Feb 4, '21 StoyanChalakov answered Feb 5, '21

Adjust T-SQL query to include DateTime (TimeStamp)

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

sql-server-general sql-server-transact-sql

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog · Feb 04, 2021 at 10:26 PM

Apparently retrieving the DateTime value requires that SQL Server needs to reads somewhere it does not have to go when that column is not defined.

To be able to help with this, we would need to see the query plans, and also the definition of the view. That is, if you want help from SQL Server people - not all of us knows System Center.

But you may be better off with adding a tag for System Center.

0 ·

Answer 1 · 2021-02-05T11:40:44.963Z

StoyanChalakov answered Feb 5, '21

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

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2021-02-05T07:43:35.313Z

MelissaMa-msft answered Feb 5, '21

Hi @StoyanChalakov,

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.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question