[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 444
  • Last Modified:

Generate histogram using SQL query based on timeseries data

I would like to generate a histogram based on a set of time series data stored in SQL server.  For example I have lots of rows of data stamped with a date and time and want to create 24 classes to put the data in.  Is there a way that I can do this with a SQL query rather than writing an external program?  The BOL has a topic listed under "histogram chart" but I don't think that applies to what I am talking about.
0
stealth188
Asked:
stealth188
2 Solutions
 
BillAn1Commented:
You can do it with a case statement,

e.g.
     SELECT bucket, count(*) from (
               SELECT
                   case when DateStamp < '2001-01-01' then 'bucket1'
                           when DateStamp between '2001-01-01' and '2002-01-01' then 'bucket2'
                           when DateStamp between '2002-01-01' and '2003-01-01' then 'bucket3'
                           ...
                  end as bucket
                FROM MyTable ) inr
              group by bucket

0
 
Ken SelviaRetiredCommented:
Perhaps see this posting for a similar question and solution:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20999291.html
0
 
stealth188Author Commented:
Thank you both for your input.  I'll be working with this today and let you know how it works out.  Thanks!
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now