asked on

How do I segregate data in a SQL query?

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?

SOLUTION

dportas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Roger Baklund

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roger Baklund

Sorry, should of course be %h:%i:%s%p for DateVisited too.

irishski

ASKER

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

irishski

ASKER

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.

Roger Baklund

You can use a varchar. The date_format() function returns a string, so this is a string comparison.

irishski

ASKER

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.