Share via

Adjust T-SQL query to include DateTime (TimeStamp)

SChalakov 10,271 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.
12,872 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,562 questions
Accepted answer
  1. SChalakov 10,271 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

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. MelissaMa-MSFT 24,176 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