Solved

How do I segregate data in a SQL query?

Posted on 2008-10-12
7
387 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
What logic to build in order to get a weekly reminder 9 51
How do I refer to a session variable in a query? 4 31
Help Parsing a String with SQL Syntax 23 42
Help Required 2 29
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

785 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