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.
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 likeTime<><><><><><><>PortNumber <><>Totalmessage received00 to 01(hour) <><><><><> 8888 <><><><><> 401 to 02(hour) <><><><><> 8888 <><><><><> 702 to 03(hour) <><><><><> 8888 <><><><><> 503 to 04(hour) <><><><><> 8888 <><><><><> 404 to 05(hour) <><><><><> 8888 <><><><><> 6........etc21 to 22<><><><><> <><><> 8888 <><><><><> 322 to 23<><><><><> <><><> 8888 <><><><><> 223 to 00<><><><><> <><><> 8888 <><><><><>7How to it.Pls anybody can help meshaji |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-21 : 06:37:42
|
Something like thisSELECT 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 ) wLEFT JOIN YourTable r ON r.YourHour >= w.FromHour AND r.YourHour < w.ToHourGROUP BY w.FromHour, w.ToHour, r.PortNumber Peter LarssonHelsingborg, 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 ) wLEFT JOIN YourTable r ON datepart(hour,r.msg_time) >= w.FromHour AND datepart(hour,r.msg_time) < w.ToHourGROUP BY w.FromHour, w.ToHour, r.PortNumber Harsh AthalyeIndia."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 = 888xwhen you need to!Simple and effective. And you don't have to loop at all!Peter LarssonHelsingborg, 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 ) tLEFT JOIN ( SELECT DATEPART(hour, Msg_Time) HourPart, Port_ID FROM C20_Messages c ) m ON m.HourPart >= t.FromHour AND m.HourPart < t.ToHourGROUP BY t.FromHour, t.ToHourORDER BY t.FromHour The only reason for this query to run slow is lack of proper indexes.Peter LarssonHelsingborg, 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 errorSELECT 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 ) tINNER JOIN ( SELECT DATEPART(hour, Msg_Time) HourPart, Port_ID FROM C20_Messages c) m ON m.HourPart >= t.FromHour AND m.HourPart < t.ToHourand 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 regardsShajiquote: 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 ) tLEFT JOIN ( SELECT DATEPART(hour, Msg_Time) HourPart, Port_ID FROM C20_Messages c ) m ON m.HourPart >= t.FromHour AND m.HourPart < t.ToHourGROUP BY t.FromHour, t.ToHourORDER BY t.FromHour The only reason for this query to run slow is lack of proper indexes.Peter LarssonHelsingborg, 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 LarssonHelsingborg, 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 ) tLEFT 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.ToHourGROUP BY t.FromHour, t.ToHourORDER BY t.FromHour Peter LarssonHelsingborg, 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 regardsShajiquote: Originally posted by Peso Todays' records only? But 11pm has not occurd yet? Surey you must mean yesterdays records?Peter LarssonHelsingborg, Sweden
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-22 : 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 ) tLEFT 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.ToHourGROUP BY t.FromHour, t.ToHourORDER 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 ) tLEFT 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.ToHourGROUP BY t.FromHour, t.ToHourORDER BY t.FromHour Peter LarssonHelsingborg, 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 hereSELECT DISTINCT Port_IDFROM C20_Messages cWHERE Msg_Time >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AND Msg_Time < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)SELECT DISTINCT HourPartFROM C20_Messages cWHERE Msg_Time >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AND Msg_Time < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1) Peter LarssonHelsingborg, Sweden |
|
|
shajimanjeri
Posting Yak Master
179 Posts |
Posted - 2006-11-22 : 06:43:20
|
Hi,The result for first query is Port_ID 1The result for Second query is Msg 207, Level 16, State 1, Line 1Invalid column name 'HourPart'.with regardsShajiquote: Originally posted by Peso Run these two queries and post back the results hereSELECT DISTINCT Port_IDFROM C20_Messages cWHERE Msg_Time >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AND Msg_Time < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)SELECT DISTINCT HourPartFROM C20_Messages cWHERE Msg_Time >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AND Msg_Time < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1) Peter LarssonHelsingborg, Sweden
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-22 : 06:50:24
|
Sorry. Should have beenSELECT Port_ID, COUNT(*)FROM C20_Messages cWHERE Msg_Time >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AND Msg_Time < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)GROUP BY Port_IDORDER BY Port_ID andSELECT DATEPART(hour, Msg_Time), COUNT(*)FROM C20_Messages cWHERE 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 LarssonHelsingborg, Sweden |
|
|
shajimanjeri
Posting Yak Master
179 Posts |
Posted - 2006-11-22 : 06:58:29
|
Hi,The first query resutlt is47 <><> 4The second query resutlt is0 <><> 4.Here the data type for Port_ID is : numeric only. Not INT.With regardsShajiquote: Originally posted by Peso Sorry. Should have beenSELECT Port_ID, COUNT(*)FROM C20_Messages cWHERE Msg_Time >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) AND Msg_Time < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)GROUP BY Port_IDORDER BY Port_ID andSELECT DATEPART(hour, Msg_Time), COUNT(*)FROM C20_Messages cWHERE 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 LarssonHelsingborg, 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 datadeclare @c20 table (ID int, Message varchar(100), Msg_Time datetime, Port_ID int)insert @c20select 100, 'Some text1', '11/22/2006 2:15:00 AM', 2 union allselect 101, 'Some text2', '11/22/2006 1:01:00 PM', 1 union allselect 102, 'Some text3', '11/22/2006 6:14:00 AM', 2 union allselect 103, 'Some text4', '11/22/2006 1:15:00 AM', 4 union allselect 104, 'Some text5', '11/22/2006 5:56:00 PM', 3 union allselect 105, 'Some text6', '11/22/2006 8:56:00 PM', 4-- do the workSELECT 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 ) tLEFT 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.ToHourGROUP BY t.FromHour, t.ToHourORDER BY t.FromHour Output isFromHourToHour Port1 Port2 Port3 Port4 Total0 1 0 0 0 0 11 2 0 0 0 1 12 3 0 1 0 0 13 4 0 0 0 0 14 5 0 0 0 0 15 6 0 0 0 0 16 7 0 1 0 0 17 8 0 0 0 0 18 9 0 0 0 0 19 10 0 0 0 0 110 11 0 0 0 0 111 12 0 0 0 0 112 13 0 0 0 0 113 14 1 0 0 0 114 15 0 0 0 0 115 16 0 0 0 0 116 17 0 0 0 0 117 18 0 0 1 0 118 19 0 0 0 0 119 20 0 0 0 0 120 21 0 0 0 1 121 22 0 0 0 0 122 23 0 0 0 0 123 24 0 0 0 0 1 Peter LarssonHelsingborg, 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 LarssonHelsingborg, 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 LarssonHelsingborg, 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 page3) Too much information or too many statistics on the pagePeter LarssonHelsingborg, Sweden |
|
|
|
|
|
|
|