Adjust T-SQL query to include DateTime (TimeStamp)

SChalakov 10,396 Reputation points MVP
2021-02-04T22:11:11.833+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,125 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,665 questions
{count} votes

Accepted answer
  1. SChalakov 10,396 Reputation points MVP
    2021-02-05T11:40:44.963+00:00

    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

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. MelissaMa-MSFT 24,201 Reputation points
    2021-02-05T07:43:35.313+00:00

    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.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.