Return a single ResultSet from my queries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Return a single ResultSet from my queries

Author

Topic

wales321
Starting Member

27 Posts

Posted - 2013-04-11 : 09:10:24

As part of a project i am working on i want to display an activity monitor which displays a count of requests made on a particular day in 1 hour time slots.
The project is in java and during testing all i wanted to do was print the results to the console so i had a for loop running 24 times (once for every hour) and then i called this simple SQL query
SELECT COUNT(*) FROM WebProxy WHERE aDate =? AND aTime BETWEEN ? AND ?
The date stays constant but i increase the time constraints on every iteration of the for loop.
This works fine for printing to the console but now i want to use it in my GUI and creating graphs, if i keep it this way i will only end up recording the result of the final iteration in my resultset. Is there a simple way i could get this to return a single resultset for me?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-04-11 : 09:30:32

SELECT DatePart(hour, aDate), COUNT (*)
FROM WEbProxy
WHERE aDate = .....
GROUP BY DatePart(hour, aDate)

wales321
Starting Member

27 Posts

Posted - 2013-04-11 : 09:49:41

Thanks, that will return the hour along with the count but what i want my result set to return is all 24 hours like you have shown above. If i do that with a loop in java i will end up with 24 result sets. Is it good practice to use a while loop in sql for this?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-04-11 : 09:50:56

Show the actual table structure and some sample data along with expected output please.

wales321
Starting Member

27 Posts

Posted - 2013-04-11 : 10:13:25

The table structure for WebProxy is
Date | Time | Address

I want a count of how many addresses were accessed in each hour time slot for a particular day, so the results would be as below.

Time slot | Requests
00:00:00 - 01:00:00 | 22
01:00:00 - 02:00:00 | 3
02:00:00 - 03:00:00 | 16
03:00:00 - 04:00:00 | 5
etc . . .

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-12 : 01:46:00

--May be this?
Create one table with time slots

DECLARE @WebProxy TABLE(Date DATE, Time TIME, Address VARCHAR(10))

DECLARE @TimeSlots TABLE ( CheckIn TIME, CheckOut TIME)
INSERT INTO @TimeSlots VALUES('00:00:00', '01:00:00'), ('01:00:00', '02:00:00'), ('02:00:00', '03:00:00'), ('03:00:00', '04:00:00'),('04:00:00', '05:00:00'), ('05:00:00', '06:00:00'), ('06:00:00', '07:00:00'), ('07:00:00', '08:00:00'), ('08:00:00', '09:00:00'), ('09:00:00', '10:00:00'),('10:00:00', '11:00:00'),('11:00:00', '12:00:00'),('12:00:00', '13:00:00'), ('13:00:00', '14:00:00'), ('14:00:00', '15:00:00'), ('15:00:00', '16:00:00'), ('16:00:00', '17:00:00'), ('18:00:00', '19:00:00'),
('19:00:00', '20:00:00'), ('20:00:00', '21:00:00'), ('21:00:00', '22:00:00') .......

SELECT ts.CheckIn, ts.checkout, COUNT(Address) requests
FROM @WebProxy w
JOIN @TimeSlots ts on w.TIME BETWEEN ts.CheckIn AND ts.CheckOut
WHERE w.Date = '2013-03-13'
GROUP BY ts.CheckIn, ts.CheckOut

wales321
Starting Member

27 Posts

Posted - 2013-04-12 : 08:06:39

quote:
Originally posted by bandi

--May be this?
Create one table with time slots

DECLARE @WebProxy TABLE(Date DATE, Time TIME, Address VARCHAR(10))

DECLARE @TimeSlots TABLE ( CheckIn TIME, CheckOut TIME)
INSERT INTO @TimeSlots VALUES('00:00:00', '01:00:00'), ('01:00:00', '02:00:00'), ('02:00:00', '03:00:00'), ('03:00:00', '04:00:00'),('04:00:00', '05:00:00'), ('05:00:00', '06:00:00'), ('06:00:00', '07:00:00'), ('07:00:00', '08:00:00'), ('08:00:00', '09:00:00'), ('09:00:00', '10:00:00'),('10:00:00', '11:00:00'),('11:00:00', '12:00:00'),('12:00:00', '13:00:00'), ('13:00:00', '14:00:00'), ('14:00:00', '15:00:00'), ('15:00:00', '16:00:00'), ('16:00:00', '17:00:00'), ('18:00:00', '19:00:00'),
('19:00:00', '20:00:00'), ('20:00:00', '21:00:00'), ('21:00:00', '22:00:00') .......

SELECT ts.CheckIn, ts.checkout, COUNT(Address) requests
FROM @WebProxy w
JOIN @TimeSlots ts on w.TIME BETWEEN ts.CheckIn AND ts.CheckOut
WHERE w.Date = '2013-03-13'
GROUP BY ts.CheckIn, ts.CheckOut

Thanks bandi, that is more what i was looking for. The only problem is that it only returns results when there is a match. I want all time slots to appear even if there is 0 requests for that particular time slot. Currently it is only returning the rows with requests like below.
I have tried using RIGHT JOIN to return all rows from the TimeSlots table but that doesn't seem to work.

18:00:00 19:00:00 678
20:00:00 21:00:00 111

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-12 : 08:10:57

-- simply change to LEFT JOIN of TimeSlots
SELECT ts.CheckIn, ts.checkout, COUNT(Address) requests
FROM TimeSlots ts
LEFT JOIN WebProxy w on w.TIME BETWEEN ts.CheckIn AND ts.CheckOut
WHERE w.Date = '2013-03-13'
GROUP BY ts.CheckIn, ts.CheckOut

wales321
Starting Member

27 Posts

Posted - 2013-04-12 : 09:49:32

I am using the code that you suggested
SELECT ts.CheckIn, ts.CheckOut, COUNT(WebAddress) requests
FROM TimeSlots ts
LEFT JOIN WebProxy w on w.ATime BETWEEN ts.CheckIn AND ts.CheckOut
WHERE w.ADate = '2012-04-12'
GROUP BY ts.CheckIn, ts.CheckOut ;

But i am still getting the same results as last time.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-12 : 10:32:22

Replace "Where" in your query with "And"

Cheers
MIK

wales321
Starting Member

27 Posts

Posted - 2013-04-12 : 11:20:15

quote:
Originally posted by MIK_2008

Replace "Where" in your query with "And"

Cheers
MIK

Ahh thats perfect now, it wasn't working for a while but that was a DB issue and not the change to the code.

Thanks for the help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources