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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy