| Author |
 Topic  |
|
|
shajimanjeri
Posting Yak Master
India
179 Posts |
 Posted - 11/21/2006 : 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
Sweden
30421 Posts |
Posted - 11/21/2006 : 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 |
Edited by - SwePeso on 11/21/2006 06:43:14 |
 |
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5581 Posts |
Posted - 11/21/2006 : 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
Sweden
30421 Posts |
Posted - 11/21/2006 : 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 |
Edited by - SwePeso on 11/21/2006 12:41:31 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 11/22/2006 : 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 |
Edited by - SwePeso on 11/22/2006 05:27:39 |
|
|
|
shajimanjeri
Posting Yak Master
India
179 Posts |
Posted - 11/22/2006 : 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
Sweden
30421 Posts |
Posted - 11/22/2006 : 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
Sweden
30421 Posts |
Posted - 11/22/2006 : 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
India
179 Posts |
Posted - 11/22/2006 : 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
Sweden
30421 Posts |
Posted - 11/22/2006 : 06:00:09
|
Yesterday's recordsSELECT 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 recordsSELECT 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
Sweden
30421 Posts |
Posted - 11/22/2006 : 06:33:11
|
Run these two queries and post back the results hereSELECT 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 |
|
|
|
shajimanjeri
Posting Yak Master
India
179 Posts |
Posted - 11/22/2006 : 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 hereSELECT 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
Sweden
30421 Posts |
Posted - 11/22/2006 : 06:50:24
|
Sorry. Should have beenSELECT 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 andSELECT 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 |
|
|
|
shajimanjeri
Posting Yak Master
India
179 Posts |
Posted - 11/22/2006 : 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 beenSELECT 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 andSELECT 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
Sweden
30421 Posts |
Posted - 11/22/2006 : 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 isFromHourToHour 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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts |
Posted - 11/22/2006 : 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
Sweden
30421 Posts |
Posted - 11/22/2006 : 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
Sweden
30421 Posts |
Posted - 11/22/2006 : 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 |
|
|
| |
Topic |
|
|
|
How to do an hourbased report??