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
 How to do an hourbased report??		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shajimanjeri
Posting Yak Master

India
179 Posts
Posted - 11/21/2006 :  06:30:22  Show Profile  Reply with Quote
Hi all,

How to get records from database by hour base.
In brief,
I have a table with fied name 'messages'(varchar), 'msg_time(datetime)', port_number(int). Here I am recieving more than 250 sms messages per day. Now I want to know how many messages I recieved today. The thing is that the result should be by hourbased.
It means it should be like

Time<><><><><><><>PortNumber <><>Totalmessage received
00 to 01(hour) <><><><><> 8888 <><><><><> 4
01 to 02(hour) <><><><><> 8888 <><><><><> 7
02 to 03(hour) <><><><><> 8888 <><><><><> 5
03 to 04(hour) <><><><><> 8888 <><><><><> 4
04 to 05(hour) <><><><><> 8888 <><><><><> 6
..
..
..
..etc
21 to 22<><><><><> <><><> 8888 <><><><><> 3
22 to 23<><><><><> <><><> 8888 <><><><><> 2
23 to 00<><><><><> <><><> 8888 <><><><><>7

How to it.
Pls anybody can help me

shaji

SwePeso
Patron Saint of Lost Yaks

Sweden
29771 Posts
Posted - 11/21/2006 :  06:37:42  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Something like this

SELECT		w.FromHour,
		w.ToHour,
		r.PortNumber,
		COUNT(*)
FROM		(
			SELECT	Number FromHour,
				1 + Number ToHour
			FROM	master..spt_values
			WHERE	Name IS NULL
				AND Number BETWEEN 0 AND 23
		) w
LEFT JOIN	YourTable r ON r.YourHour >= w.FromHour AND r.YourHour < w.ToHour
GROUP BY	w.FromHour,
		w.ToHour,
		r.PortNumber

Peter Larsson
Helsingborg, Sweden
Edited by - SwePeso on 11/21/2006 06:43:14
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts
Posted - 11/21/2006 :  07:32:47  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
Small change in Peter's solution:

SELECT		w.FromHour,
		w.ToHour,
		r.PortNumber,
		COUNT(*)
FROM		(
			SELECT	Number FromHour,
				1 + Number ToHour
			FROM	master..spt_values
			WHERE	Name IS NULL
				AND Number BETWEEN 0 AND 23
		) w
LEFT JOIN	YourTable r 
ON datepart(hour,r.msg_time) >= w.FromHour AND datepart(hour,r.msg_time) < w.ToHour
GROUP BY	w.FromHour,
		w.ToHour,
		r.PortNumber


Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29771 Posts
Posted - 11/21/2006 :  12:40:53  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Make the query Harsh made to a VIEW, and then select from the view with WHERE Portnumber = 888x
when you need to!

Simple and effective. And you don't have to loop at all!


Peter Larsson
Helsingborg, Sweden
Edited by - SwePeso on 11/21/2006 12:41:31
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29771 Posts
Posted - 11/22/2006 :  05:23:04  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Have you tried this?
SELECT		t.FromHour,
		t.ToHour,
		SUM(CASE WHEN m.Port_ID = 1 THEN 1 ELSE 0 END) [Port1 (8888)],
		SUM(CASE WHEN m.Port_ID = 2 THEN 1 ELSE 0 END) [Port2 (9999)],
		SUM(CASE WHEN m.Port_ID = 3 THEN 1 ELSE 0 END) [Port3 (1134)],
		SUM(CASE WHEN m.Port_ID = 4 THEN 1 ELSE 0 END) [Port4 (9986)],
		SUM(1) [Total]
FROM		(
			SELECT	Number FromHour,
				1 + Number ToHour
			FROM	master..spt_values
			WHERE	Name IS NULL
				AND Number BETWEEN 0 AND 23
		) t
LEFT JOIN	(			
			SELECT		DATEPART(hour, Msg_Time) HourPart,
					Port_ID
			FROM		C20_Messages c
		) m ON m.HourPart >= t.FromHour AND m.HourPart < t.ToHour
GROUP BY	t.FromHour,
		t.ToHour
ORDER BY	t.FromHour
The only reason for this query to run slow is lack of proper indexes.


Peter Larsson
Helsingborg, Sweden
Edited by - SwePeso on 11/22/2006 05:27:39
Go to Top of Page

shajimanjeri
Posting Yak Master

India
179 Posts
Posted - 11/22/2006 :  05:48:32  Show Profile  Reply with Quote

Thanx Peter Larsson,

It's working.
But one problem the result not giving from which portID (ShortCode) I recieved the message. and how to get todays record only. In the given query where should I put it.

I modified the query like this for todays record, but showing error
SELECT t.FromHour,
t.ToHour,
SUM(CASE WHEN m.Port_ID = 1 THEN 1 ELSE 0 END) [Port1 (88881)],
SUM(CASE WHEN m.Port_ID = 2 THEN 1 ELSE 0 END) [Port2 (9999)],
SUM(CASE WHEN m.Port_ID = 3 THEN 1 ELSE 0 END) [Port3 (1134)],
SUM(CASE WHEN m.Port_ID = 4 THEN 1 ELSE 0 END) [Port4 (9986)],
SUM(1) [Total]
FROM (
SELECT Number FromHour,
1 + Number ToHour
FROM master..spt_values
WHERE Name IS NULL
AND Number BETWEEN 0 AND 23
) t
INNER JOIN (
SELECT DATEPART(hour, Msg_Time) HourPart, Port_ID FROM C20_Messages c) m ON m.HourPart >= t.FromHour AND m.HourPart < t.ToHour
and convert(datetime,floor(CONVERT(FLOAT, Msg_Time)))= convert(datetime,floor(CONVERT(FLOAT,GETDATE())))

GROUP BY t.FromHour, t.ToHour ORDER BY t.FromHour

But showing error.

With regards
Shaji


quote:
Originally posted by Peso

Have you tried this?
SELECT		t.FromHour,
		t.ToHour,
		SUM(CASE WHEN m.Port_ID = 1 THEN 1 ELSE 0 END) [Port1 (8888)],
		SUM(CASE WHEN m.Port_ID = 2 THEN 1 ELSE 0 END) [Port2 (9999)],
		SUM(CASE WHEN m.Port_ID = 3 THEN 1 ELSE 0 END) [Port3 (1134)],
		SUM(CASE WHEN m.Port_ID = 4 THEN 1 ELSE 0 END) [Port4 (9986)],
		SUM(1) [Total]
FROM		(
			SELECT	Number FromHour,
				1 + Number ToHour
			FROM	master..spt_values
			WHERE	Name IS NULL
				AND Number BETWEEN 0 AND 23
		) t
LEFT JOIN	(			
			SELECT		DATEPART(hour, Msg_Time) HourPart,
					Port_ID
			FROM		C20_Messages c
		) m ON m.HourPart >= t.FromHour AND m.HourPart < t.ToHour
GROUP BY	t.FromHour,
		t.ToHour
ORDER BY	t.FromHour
The only reason for this query to run slow is lack of proper indexes.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29771 Posts
Posted - 11/22/2006 :  05:51:55  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Todays' records only? But 11pm has not occurd yet?
Surey you must mean yesterdays records?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29771 Posts
Posted - 11/22/2006 :  05:56:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Of course the query below shows you which port that received the message!
Look at the column headers...
SELECT		t.FromHour,
		t.ToHour,
		SUM(CASE WHEN m.Port_ID = 1 THEN 1 ELSE 0 END) [Port1 (8888)],
		SUM(CASE WHEN m.Port_ID = 2 THEN 1 ELSE 0 END) [Port2 (9999)],
		SUM(CASE WHEN m.Port_ID = 3 THEN 1 ELSE 0 END) [Port3 (1134)],
		SUM(CASE WHEN m.Port_ID = 4 THEN 1 ELSE 0 END) [Port4 (9986)],
		SUM(1) [Total]
FROM		(
			SELECT	Number FromHour,
				1 + Number ToHour
			FROM	master..spt_values
			WHERE	Name IS NULL
				AND Number BETWEEN 0 AND 23
		) t
LEFT JOIN	(			
			SELECT		DATEPART(hour, Msg_Time) HourPart,
					Port_ID
			FROM		C20_Messages c
			WHERE		Msg_Time >= DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0)
					AND Msg_Time < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
		) m ON m.HourPart >= t.FromHour AND m.HourPart < t.ToHour
GROUP BY	t.FromHour,
		t.ToHour
ORDER BY	t.FromHour


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

shajimanjeri
Posting Yak Master

India
179 Posts
Posted - 11/22/2006 :  05:58:19  Show Profile  Reply with Quote
No, Todays record only.
Pls ignore such values. Those are some dummy values.
If you can provide yesterdays records, thats also fine.

with regards
Shaji
quote:
Originally posted by Peso

Todays' records only? But 11pm has not occurd yet?
Surey you must mean yesterdays records?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29771 Posts
Posted - 11/22/2006 :  06:00:09  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Yesterday's records
SELECT		t.FromHour,
		t.ToHour,
		SUM(CASE WHEN m.Port_ID = 1 THEN 1 ELSE 0 END) [Port1 (8888)],
		SUM(CASE WHEN m.Port_ID = 2 THEN 1 ELSE 0 END) [Port2 (9999)],
		SUM(CASE WHEN m.Port_ID = 3 THEN 1 ELSE 0 END) [Port3 (1134)],
		SUM(CASE WHEN m.Port_ID = 4 THEN 1 ELSE 0 END) [Port4 (9986)],
		SUM(1) [Total]
FROM		(
			SELECT	Number FromHour,
				1 + Number ToHour
			FROM	master..spt_values
			WHERE	Name IS NULL
				AND Number BETWEEN 0 AND 23
		) t
LEFT JOIN	(			
			SELECT		DATEPART(hour, Msg_Time) HourPart,
					Port_ID
			FROM		C20_Messages c
			WHERE		Msg_Time >= DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0)
					AND Msg_Time < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
		) m ON m.HourPart >= t.FromHour AND m.HourPart < t.ToHour
GROUP BY	t.FromHour,
		t.ToHour
ORDER BY	t.FromHour
Today's records
SELECT		t.FromHour,
		t.ToHour,
		SUM(CASE WHEN m.Port_ID = 1 THEN 1 ELSE 0 END) [Port1 (8888)],
		SUM(CASE WHEN m.Port_ID = 2 THEN 1 ELSE 0 END) [Port2 (9999)],
		SUM(CASE WHEN m.Port_ID = 3 THEN 1 ELSE 0 END) [Port3 (1134)],
		SUM(CASE WHEN m.Port_ID = 4 THEN 1 ELSE 0 END) [Port4 (9986)],
		SUM(1) [Total]
FROM		(
			SELECT	Number FromHour,
				1 + Number ToHour
			FROM	master..spt_values
			WHERE	Name IS NULL
				AND Number BETWEEN 0 AND 23
		) t
LEFT JOIN	(			
			SELECT		DATEPART(hour, Msg_Time) HourPart,
					Port_ID
			FROM		C20_Messages c
			WHERE		Msg_Time >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
					AND Msg_Time < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)
		) m ON m.HourPart >= t.FromHour AND m.HourPart < t.ToHour
GROUP BY	t.FromHour,
		t.ToHour
ORDER BY	t.FromHour


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29771 Posts
Posted - 11/22/2006 :  06:33:11  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Run these two queries and post back the results here
SELECT DISTINCT	Port_ID
FROM		C20_Messages c
WHERE		Msg_Time >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
		AND Msg_Time < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)

SELECT DISTINCT	HourPart
FROM		C20_Messages c
WHERE		Msg_Time >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
		AND Msg_Time < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)

Peter Larsson
Helsingborg, Sweden
Edited by - SwePeso on 11/22/2006 06:35:37
Go to Top of Page

shajimanjeri
Posting Yak Master

India
179 Posts
Posted - 11/22/2006 :  06:43:20  Show Profile  Reply with Quote
Hi,

The result for first query is
Port_ID
1

The result for Second query is
Msg 207, Level 16, State 1, Line 1
Invalid column name 'HourPart'.

with regards
Shaji

quote:
Originally posted by Peso

Run these two queries and post back the results here
SELECT DISTINCT	Port_ID
FROM		C20_Messages c
WHERE		Msg_Time >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
		AND Msg_Time < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)

SELECT DISTINCT	HourPart
FROM		C20_Messages c
WHERE		Msg_Time >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
		AND Msg_Time < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29771 Posts
Posted - 11/22/2006 :  06:50:24  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Sorry. Should have been
SELECT		Port_ID,
		COUNT(*)
FROM		C20_Messages c
WHERE		Msg_Time >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
		AND Msg_Time < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)
GROUP BY	Port_ID
ORDER BY	Port_ID
and
SELECT		DATEPART(hour, Msg_Time),
		COUNT(*)
FROM		C20_Messages c
WHERE		Msg_Time >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
		AND Msg_Time < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)
GROUP BY	DATEPART(hour, Msg_Time)
ORDER BY	DATEPART(hour, Msg_Time)
Run these two queries and post back the result here.
But already now I can see that you have at least on record for first column (Port 1) that should be calculated with my query.
What data type is Port_ID? INT? You wrote NUMERIC...


Peter Larsson
Helsingborg, Sweden
Edited by - SwePeso on 11/22/2006 06:52:18
Go to Top of Page

shajimanjeri
Posting Yak Master

India
179 Posts
Posted - 11/22/2006 :  06:58:29  Show Profile  Reply with Quote
Hi,

The first query resutlt is
47 <><> 4

The second query resutlt is
0 <><> 4
.

Here the data type for Port_ID is : numeric only. Not INT.

With regards
Shaji

quote:
Originally posted by Peso

Sorry. Should have been
SELECT		Port_ID,
		COUNT(*)
FROM		C20_Messages c
WHERE		Msg_Time >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
		AND Msg_Time < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)
GROUP BY	Port_ID
ORDER BY	Port_ID
and
SELECT		DATEPART(hour, Msg_Time),
		COUNT(*)
FROM		C20_Messages c
WHERE		Msg_Time >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
		AND Msg_Time < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)
GROUP BY	DATEPART(hour, Msg_Time)
ORDER BY	DATEPART(hour, Msg_Time)
Run these two queries and post back the result here.
But already now I can see that you have at least on record for first column (Port 1) that should be calculated with my query.
What data type is Port_ID? INT? You wrote NUMERIC...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29771 Posts
Posted - 11/22/2006 :  06:59:26  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Look here!

I used the sample data you provided earlier and tested with my code.
And the query works beautiful 
-- prepare test data
declare @c20 table (ID int, Message varchar(100), Msg_Time datetime, Port_ID int)

insert	@c20
select	100, 'Some text1', '11/22/2006 2:15:00 AM', 2 union all
select	101, 'Some text2', '11/22/2006 1:01:00 PM', 1 union all
select	102, 'Some text3', '11/22/2006 6:14:00 AM', 2 union all
select	103, 'Some text4', '11/22/2006 1:15:00 AM', 4 union all
select	104, 'Some text5', '11/22/2006 5:56:00 PM', 3 union all
select	105, 'Some text6', '11/22/2006 8:56:00 PM', 4

-- do the work
SELECT		t.FromHour,
		t.ToHour,
		SUM(CASE WHEN m.Port_ID = 1 THEN 1 ELSE 0 END) [Port1 (8888)],
		SUM(CASE WHEN m.Port_ID = 2 THEN 1 ELSE 0 END) [Port2 (9999)],
		SUM(CASE WHEN m.Port_ID = 3 THEN 1 ELSE 0 END) [Port3 (1134)],
		SUM(CASE WHEN m.Port_ID = 4 THEN 1 ELSE 0 END) [Port4 (9986)],
		SUM(1) [Total]
FROM		(
			SELECT	Number FromHour,
				1 + Number ToHour
			FROM	master..spt_values
			WHERE	Name IS NULL
				AND Number BETWEEN 0 AND 23
		) t
LEFT JOIN	(			
			SELECT		DATEPART(hour, Msg_Time) HourPart,
					Port_ID
			FROM		@c20 C20_Messages
			WHERE		Msg_Time >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
					AND Msg_Time < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)
		) m ON m.HourPart >= t.FromHour AND m.HourPart < t.ToHour
GROUP BY	t.FromHour,
		t.ToHour
ORDER BY	t.FromHour
Output is
FromHourToHour  Port1   Port2   Port3   Port4   Total
0	1	0	0	0	0	1
1	2	0	0	0	1	1
2	3	0	1	0	0	1
3	4	0	0	0	0	1
4	5	0	0	0	0	1
5	6	0	0	0	0	1
6	7	0	1	0	0	1
7	8	0	0	0	0	1
8	9	0	0	0	0	1
9	10	0	0	0	0	1
10	11	0	0	0	0	1
11	12	0	0	0	0	1
12	13	0	0	0	0	1
13	14	1	0	0	0	1
14	15	0	0	0	0	1
15	16	0	0	0	0	1
16	17	0	0	0	0	1
17	18	0	0	1	0	1
18	19	0	0	0	0	1
19	20	0	0	0	0	1
20	21	0	0	0	1	1
21	22	0	0	0	0	1
22	23	0	0	0	0	1
23	24	0	0	0	0	1

Peter Larsson
Helsingborg, Sweden
Edited by - SwePeso on 11/22/2006 07:05:46
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29771 Posts
Posted - 11/22/2006 :  07:02:13  Show Profile  Visit SwePeso's Homepage  Reply with Quote
The first query takes all ports and calculates the number of records associated with that port.
And it seems that the port in use is #47 and there are 4 records with that port.
You only provided four ports, named 1 to 4.

The second query takes all hours and calculates the number of records associated with that hour.
And it seems that the hour in use is 00-01 and there are 4 records with that hour.

That result is expected and obviously there are only four records stored for today.

SEE MY EXAMPLE ABOVE...



Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29771 Posts
Posted - 11/22/2006 :  07:31:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
It takes too long to download because you have not the proper indexes.
I faked 200 000 values for today in my test table and the query took about 0.5 sec to run.

How long time does the query take on your machine?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29771 Posts
Posted - 11/22/2006 :  07:50:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
No. Don't.
There can be so many reasons for this if the difference between local 4-6 seconds and live 30-40 seconds.

1) Network congestion.
2) Badly written statistics page
3) Too much information or too many statistics on the page


Peter Larsson
Helsingborg, Sweden
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.17 seconds. Powered By: Snitz Forums 2000