rows group by second

Question

Post reply

rows group by second

moorthy.jy

SSCommitted

Points: 1753
More actions
July 3, 2009 at 2:16 pm

#95817

Dear all,
I have a table with TranIn, TranOut colums with datetime stored in YYYY-MM-DD HH:MM:SS:MSS format.
i need to count all the transactions group by every second up to 10 seconds. lets say
1 second -- 2 transactions
2 seconds -- 0 transactions
3 seconds -- 1 transactions
4 seconds -- 0 transactions
5 seconds -- 0 transactions
.
.
10seconds -- 1 transactions
create table #tmp(nos int,TranIn datetime, TranOut datetime)
insert into #tmp
select 1,'2009-07-02 05:41:02.123', '2009-07-02 05:41:02.567' union all
select 2,'2009-07-02 20:12:03.120', '2009-07-02 20:12:04.270' union all
select 3,'2009-07-02 14:47:54.060', '2009-07-02 14:47:59.776' union all
select 4,'2009-07-02 16:50:02.320', '2009-07-02 16:50:11.850' union all
select 5,'2009-07-02 13:55:33.135', '2009-07-02 13:55:45.000, union all
select 6,'2009-07-02 14:47:24.567', '2009-07-02 14:47:28.456' union all
select 7,'2009-07-02 19:12:12.345', '2009-07-02 19:12:27.450'
I ignore the millisecond anyway. Please help me with this query.

Viewing 9 posts - 1 through 8 (of 8 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Hi,

your data seems not to lead to the expected result:

SELECT

DATEDIFF(ss,TranIn,TranOut) AS SECONDS,

COUNT(*) AS CNT

FROM #tmp

GROUP BY DATEDIFF(ss,TranIn,Tranout)

/* result

SECONDSCNT

01

11

41

51

91

121

151

*/

Reason for that is the first row just has a little more than 0.4 seconds, so you'll get the count "1" for 0 seconds.

If you have difficulties to modify the code shown above to meet your requirements, let us know.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

WayneS SSC Guru Points: 95460 More actions · Answer 2

First, I want to thank you for posting the DDL and DML for the test data.

Now, I have a question. What second are you looking for... elapsed, TranIn, or TranOut?

If your looking for elapsed, try this:

;with CTE AS

( -- get the elapsed time in seconds for each transaction, with a count by # of seconds

select sec = DateDiff(second, TranIn, TranOut), qty = count(*)

from #tmp

group by DateDiff(second, TranIn, TranOut)

)

, Tally AS -- if you already have a tally table, you can omit it here and use it below

( -- build a tally table of numbers between 1 and 10

select Number from master.dbo.spt_values where [Type] = 'P' and number between 1 and 10

)

-- now bring them all together

select [Second] = Tally.Number,

[Quantity of Transactions] = case when CTE.sec is null then 0 else CTE.qty end,

[Description] = convert(varchar(2), Tally.Number) +

' second' +

case when Tally.Number = 1 then '' else 's' end +

' -- ' +

convert(varchar(10), case when CTE.sec is null then 0 else CTE.qty end) +

' transactions'

from Tally

LEFT OUTER JOIN CTE ON Tally.Number = CTE.sec

where Tally.Number between 1 and 10 -- this line is only needed if using your own tally table.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

moorthy.jy SSCommitted Points: 1753 More actions · Answer 3

Thank you Lutz and Wayne. Yes I am looking for the diffrence in seconds in TranIn and TranOut columns

LutzM SSC Guru Points: 107049 More actions · Answer 4

jymoorthy (7/3/2009)
Thank you Lutz and Wayne. Yes I am looking for the difference in seconds in TranIn and TranOut columns

What rule for rounding values would you use?

The example you provided has a value of some 0.4 seconds. Would you count this as "1 sec" or as "0 sec"? Would you always round to the next full second or using financial rounding method?

@Wayne: good catch! Didn't notice the OP wanted a list with all seconds, including NULL values... I was a little more focused on the rounding issue...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

moorthy.jy SSCommitted Points: 1753 More actions · Answer 5

Would you count this as "1 sec" or as "0 sec"?

I want to count in 0 as i specified in my post, " ignore the milliseconds" its difficult to calculate in milliseconds and convert into seconds. that will be more complicated to calculate...

Thanks again.

LutzM SSC Guru Points: 107049 More actions · Answer 6

I'm sorry for not asking all questions I have at once... :blush:

Your example has two rows with 12 rsp 15 seconds.

How would you handle those?

a) add it up in the "10 seconds group"

b) ignore the values (that's actually happening when you use Wayne's code)

c) show it as separate rows

Note: if sample data and expected result actually match it makes it a lot easier to test the solutions. Just a note, not offensive in any kind!

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

WayneS SSC Guru Points: 95460 More actions · Answer 7

lmu92 (7/3/2009)
I'm sorry for not asking all questions I have at once... :blush:
Your example has two rows with 12 rsp 15 seconds.
How would you handle those?
a) add it up in the "10 seconds group"
b) ignore the values (that's actually happening when you use Wayne's code)
c) show it as separate rows
Note: if sample data and expected result actually match it makes it a lot easier to test the solutions. Just a note, not offensive in any kind!

Lutz,

I did it the way I did because of this original comment:

i need to count all the transactions group by every second up to 10 seconds.

I interpreted that to mean just 1-10, ignore anything greater.

Of course, if the poster would have supplied expected results from the supplied test data then all of these questions would have been answered, and we wouldn't have to work on assumptions and ask a bunch of clarifying questions. 😉

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

moorthy.jy SSCommitted Points: 1753 More actions · Answer 8

Thanks Lutz and Wayne. I am sorry for not giving all the required details at once.

I am looking for the results upto 10 seconds only. (even, if there is a new requirement, i can add it later by myself). In 99% cases the transaction would finish within 1 second. after 15 seconds, its timed out . I might have made a mistake in my test data by adding 15 seoncds treansaction. i am sorry for that.

Here i am preparing a delayed transaction list, which will show as i required in my original post. Thanks for your time.