Query Help

Question

Query Help

ramana3327

SSCoach

Points: 19722
More actions
June 1, 2014 at 6:52 am

#289325

Hi All,
I want to analyze a particular transaction behavious over the time. For that
I need to write a small query. In a database, I have a table1 with columns APPlicationName, Phase, TransactionName, TimeStamp, MachineName, TimeTaken.
Here we have 6 applications running and phase dev or Test like that and Machine name is computer name.
I need to write a query to find that a particular transaction how much time it took per hour, per week, per month,.. and how many times it executed in Production(Phase) for a particular transaction for an application.
select Applicationname,Phase, count, Transactionname, Time or Date
where transaction name like '%Transaction1%'
and Phase='prod'
and applicationname='application1'.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Not really sure what you are expecting based on your post. It would help if you could post the DDL for your table, some sample data in a readily consumable (read that as cut/paste/execute) to populate your table and what the expected results of the query would look like.

ramana3327 SSCoach Points: 19722 More actions · Answer 2

Ok Lynn.

I have a table in db like this to log the each application execution time

CREATE TABLE [dbo].[AppTimeLog](

[TimingLogID] [int] IDENTITY(1,1) NOT NULL,

[ApplicationName] [nvarchar](150) NOT NULL,

[Phase] [nvarchar](150) NOT NULL,

[TransactionName] [nvarchar](150) NOT NULL,

[Timestamp] [datetime] NOT NULL,

[MachineName] [nvarchar](32) NO0 NULL,

[Timetook] [int] NOT NULL,

[CreatedBy] [nvarchar](100) NULL,

[CreatedDate] [datetime] NULL)

I need to do some analysis on each transaction. I need to find Transaction1 how much time it took per hour, perday, per week wise in a application and how many times it executed

Phase, Transactionname & Application names goes in the where clause

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

ramana3327 (6/3/2014)
Ok Lynn.
I have a table in db like this to log the each application execution time
CREATE TABLE [dbo].[AppTimeLog](
[TimingLogID] [int] IDENTITY(1,1) NOT NULL,
[ApplicationName] [nvarchar](150) NOT NULL,
[Phase] [nvarchar](150) NOT NULL,
[TransactionName] [nvarchar](150) NOT NULL,
[Timestamp] [datetime] NOT NULL,
[MachineName] [nvarchar](32) NO0 NULL,
[Timetook] [int] NOT NULL,
[CreatedBy] [nvarchar](100) NULL,
[CreatedDate] [datetime] NULL)
I need to do some analysis on each transaction. I need to find Transaction1 how much time it took per hour, perday, per week wise in a application and how many times it executed
Phase, Transactionname & Application names goes in the where clause

You got the first part (ddl), but there is a major piece missing (sample data). Please post some insert statements representative of your problem along with what you would expect to be the output from a query.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 4

Hi

Try queries like this one for example

--For today

DECLARE @date_start datetime

SELECT @date_start = CONVERT(datetime, CONVERT(date, GETDATE()))

DECLARE @date_end datetime

SELECT @date_end = DATEADD(millisecond, -3, CONVERT(datetime, CONVERT(date, GETDATE()+1))) --SQL server datetime rounds up on 3ms

SELECT t.TransactionName, SUM(t.Timetook) TotalTrnTime, COUNT(*) TotalExecutionCount

FROM AppTimeLog t

WHERE t.[Timestamp] BETWEEN @date_start AND @date_end

--and t.TransactionName = 'Transaction name'

GROUP BY t.TransactionName

Igor Micev,My blog: www.igormicev.com