Solved

How do I segregate data in a SQL query?

Posted on 2008-10-12
7
368 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 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

19 Experts available now in Live!

Get 1:1 Help Now