February 16, 2009 at 11:42 pm
I have a table like this,
MachineName Varchar(50),
EntryTime DateTime
within one minutes around 500 records get inserted to this table., so I need to get
count of records inserted last min,count of record inserted last hour, count of record inserted within last 24 hours as a one table., records need to be grouped by MachineName
Select MachineName, count(*) from table1 where
EntryTime >= DateAdd(hh, -1, getDate())
group by MachineName;
using this query I can get each group by, but I need all in one table like following
MachineName| RecordCountForLastMin| RecordCountForLastHour| RecordCountForLast24Hours
Sql Query or or Stored procedure will be ok.., I'm trying to do this help, ideas appreciate...
February 17, 2009 at 1:03 am
manjulaatapattu (2/16/2009)
I have a table like this,MachineName Varchar(50),
EntryTime DateTime
within one minutes around 500 records get inserted to this table., so I need to get
count of records inserted last min,count of record inserted last hour, count of record inserted within last 24 hours as a one table., records need to be grouped by MachineName
Select MachineName, count(*) from table1 where
EntryTime >= DateAdd(hh, -1, getDate())
group by MachineName;
using this query I can get each group by, but I need all in one table like following
MachineName| RecordCountForLastMin| RecordCountForLastHour| RecordCountForLast24Hours
Sql Query or or Stored procedure will be ok.., I'm trying to do this help, ideas appreciate...
select
MachineName,
(select count(*) from table1 where MachineName= T.MachineName and EntryTime >= DateAdd(mi, -1, getDate()) ) LastMin,
(select count(*) from table1 where MachineName= T.MachineName and EntryTime >= DateAdd(hh, -1, getDate()) ) LastHour,
(select count(*) from table1 where MachineName= T.MachineName and EntryTime >= DateAdd(dd, -1, getDate()) ) Last24H
from table1 T
group by MachineName
February 17, 2009 at 1:31 am
Thank you, this your query is simple, clear and nice..
February 17, 2009 at 1:31 am
This should perform better than the previous one...
SELECTMachineName,
SUM( ( CASE WHEN EntryTime >= DATEADD( MINUTE, -1, GETDATE() ) THEN 1 ELSE 0 END ) ) LastMin,
SUM( ( CASE WHEN EntryTime >= DATEADD( HOUR, -1, GETDATE() ) THEN 1 ELSE 0 END ) ) LastHour,
SUM( ( CASE WHEN EntryTime >= DATEADD( DAY, -1, GETDATE() ) THEN 1 ELSE 0 END ) ) Last24Hours
FROMtable1 T
WHEREEntryTime >= DATEADD( DAY, -1, GETDATE() )
GROUP BY MachineName
--Ramesh
February 17, 2009 at 7:00 am
I highly recommend going with the solution provided by Ramesh as it will only access the table once while the one proposed by Vic will access the table 4 times.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 17, 2009 at 1:42 pm
regarding performance...
I ran both queries on my table.
Ramesh query runs around 01 min +/- seconds
VIC-K query runs around 03min and 40 +/- seconds
must consider about performance..!
thankx..
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply