SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Return a single ResultSet from my queries
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wales321
Starting Member

27 Posts

Posted - 04/11/2013 :  09:10:24  Show Profile  Reply with Quote
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?

Edited by - wales321 on 04/11/2013 09:15:04

russell
Pyro-ma-ni-yak

USA
5071 Posts

Posted - 04/11/2013 :  09:30:32  Show Profile  Visit russell's Homepage  Reply with Quote
SELECT DatePart(hour, aDate), COUNT (*)
FROM WEbProxy
WHERE aDate = .....
GROUP BY DatePart(hour, aDate)
Go to Top of Page

wales321
Starting Member

27 Posts

Posted - 04/11/2013 :  09:49:41  Show Profile  Reply with Quote
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?
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5071 Posts

Posted - 04/11/2013 :  09:50:56  Show Profile  Visit russell's Homepage  Reply with Quote
Show the actual table structure and some sample data along with expected output please.
Go to Top of Page

wales321
Starting Member

27 Posts

Posted - 04/11/2013 :  10:13:25  Show Profile  Reply with Quote
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 . . .

Edited by - wales321 on 04/11/2013 10:13:56
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 04/12/2013 :  01:46:00  Show Profile  Reply with Quote
--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
Go to Top of Page

wales321
Starting Member

27 Posts

Posted - 04/12/2013 :  08:06:39  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 04/12/2013 :  08:10:57  Show Profile  Reply with Quote
-- 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
Go to Top of Page

wales321
Starting Member

27 Posts

Posted - 04/12/2013 :  09:49:32  Show Profile  Reply with Quote
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.
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1050 Posts

Posted - 04/12/2013 :  10:32:22  Show Profile  Reply with Quote
Replace "Where" in your query with "And"

Cheers
MIK
Go to Top of Page

wales321
Starting Member

27 Posts

Posted - 04/12/2013 :  11:20:15  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.23 seconds. Powered By: Snitz Forums 2000