question

Arun 1 avatar image
Arun 1 asked

Group by minute

I have a table with Share transaction times and Price. What is the proper query to get the records counts for each minute for any given hour. I want opening value, closing value, minimum price, maximum price, volume of trade group by each minute for any given hour. Thanks

Sample data

                    
Company_Name|Quote|TrnTime|                    
XXXXXXXXXXXX|90.00|09:55:06|                    
XXXXXXXXXXXX|90.50|09:55:06|                    
XXXXXXXXXXXX|90.40|09:55:06|                    
datetimegroup-by
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kristen avatar image
Kristen answered

This will "round" a DateTime value to the minute - is that what you mean?

SELECT DATEADD(Minute, DATEDIFF(Minute, 0, TrnTime), 0)

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Arun 1 avatar image
Arun 1 answered

Thanks for the reply. I have done up to grouping by minute with "Group by DATEPART(mi,TrnTime)". Share quote has high volume of transactions. I have grouped the transactions per minute. I require company_name ,opening value which may be higher or lower than the minimum price , like wise the closing value, minimum price, maximum price, volume of trade group by each minute for any given hour. Thanks, Arun

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Arun 1 avatar image
Arun 1 answered

Yes, We will give any one hour duration in the "Where TrnTime Between 'xx:xx:xx' AND 'xx:xx:xx'". There will be 60 one minute groups with various Company_Names and share quotes for that company. I have to present opening value which may be higher or lower than the minimum price , like wise the closing value, minimum price, maximum price, volume of trade group by each minute. We may pull minimum and maximum quotes and count for the group. How to extract opening and closing quotes for each group is my requirement. Thanks, Arun

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.