?
Solved

How do I segregate data in a SQL query?

Posted on 2008-10-12
7
Medium Priority
?
418 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 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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

569 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