How to do an hourbased report?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How to do an hourbased report??

Author

Topic

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2006-11-21 : 06:30:22

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

30421 Posts

Posted - 2006-11-21 : 06:37:42

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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-21 : 07:32:47

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"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-21 : 12:40:53

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 05:23:04

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

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2006-11-22 : 05:48:32

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 05:51:55

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

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 05:56:33

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

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2006-11-22 : 05:58:19

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 06:00:09

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 06:33:11

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

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2006-11-22 : 06:43:20

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 06:50:24

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

shajimanjeri
Posting Yak Master

179 Posts

Posted - 2006-11-22 : 06:58:29

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 06:59:26

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 07:02:13

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 07:31:44

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 07:50:20

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources