Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How do I segregate data in a SQL query?

Posted on 2008-10-12
7
Medium Priority
?
411 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 22

Assisted Solution

by:dportas
dportas earned 400 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 1600 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
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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 course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

661 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