Solved

How do I segregate data in a SQL query?

Posted on 2008-10-12
7
379 Views
Last Modified: 2010-04-21
I have a table of data that I would like to organize a little better.  TableA represents the raw data, TableB represents what I would like to see but now sure how to obtain:

TableA
DateVisited                   AreaVisited
5/1/2008 8:00:00AM     Area1
5/1/2008 8:15:00AM     Area1
5/1/2008 9:00:00AM     Area2
5/1/2008 10:00:00AM   Area1
5/1/2008 8:05:00AM     Area 1
5/1/2008 8:20:00AM    Area 1
5/1/2008 10:15:00AM  Area 2

TableB
TimeVisited                    CountArea1   CountArea2
8:00:00AM-8:29:59AM          4                    0
8:30:00AM-8:59:59AM          0                    0
9:00:00AM-9:29:59AM          0                    1
9:30:00AM-9:59:59AM          0                    0
10:00:00AM-10:29:59AM      1                    1

How do I structure my SQL to make TableB from information found in TableA?
0
Comment
Question by:irishski
  • 3
  • 3
7 Comments
 
LVL 22

Assisted Solution

by:dportas
dportas earned 100 total points
ID: 22698663
One method is to use PIVOT:
http://msdn.microsoft.com/en-us/library/ms177410(SQL.90).aspx

However, your TableB is more like a report rather than the usual output of a query. It's called a cross tab report and it may make more sense to do it in a reporting tool / presentation tier rather than in SQL.
0
 
LVL 39

Accepted Solution

by:
Roger Baklund earned 400 total points
ID: 22698944
What makes this complicated is the fact that the column TimeVisited does not exist. But that can easily be fixed, because there is a limited and predictable set of half-hours in a day.

Below code is tested on MySQL5, but it should be easily translated to any SQL dialect.
create table timetable(

  starttime time not null primary key,

  stoptime time);
 

insert into timetable values 

 (080000,082959),

 (083000,085959),

 (090000,092959),

 (093000,095959),

 (100000,102959);
 

select 

  concat(date_format(starttime,'%h:%i:%s%p'),'-',

         date_format(stoptime,'%h:%i:%s%p')) as TimeVisited,

  sum(if(AreaVisited='Area1',1,0)) as CountArea1,

  sum(if(AreaVisited='Area2',1,0)) as CountArea2 

from timetable

left join tableA on

  date_format(DateVisited,'%h:%i:%s') 

      between starttime and stoptime

group by 1;

Open in new window

0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22698960
Sorry, should of course be %h:%i:%s%p for DateVisited too.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:irishski
ID: 22699232
cxr... thanks for the response...
datatype time doesn't appear to be supported in SQL Server 2005?  Do you have another I should try?
0
 

Author Comment

by:irishski
ID: 22699246
Scratch that, I had to create one:

create type Time from dateTime
create rule TimeOnlyRule as datediff(dd,0,@DateTime) = 0
EXEC sp_bindrule 'TimeOnlyRule', 'Time'

Still working through cxr's suggestion.
0
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22699250
You can use a varchar. The date_format() function returns a string, so this is a string comparison.
0
 

Author Closing Comment

by:irishski
ID: 31505473
Mostly great!  Thank you for the effort.  Had to switch things around a bit to make it compatible with MS SQL Server.  RE: the report idea, very true this is more a report piece... still wanted the ability to run it on the server.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query Syntax Error 9 48
SQL Server 2008 R2 - Execution Plan 3 57
SQL Server 2008 R2 - Sums/Grouping 7 51
Getting same value for every field in SQL 2 29
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now