January 16, 2008 at 4:53 pm
I need help with some SQL query, may be really simple for some of you out there.
I have a table with several transactions and i want to count # of transactions at each hour
StartTime, EndTime, Transaction_UserID, ServerID
1/1/2008 8.05AM,1/1/2008 11.05AM,xx,10
1/1/2008 8.10AM,1/1/2008 9.55AM,yy,10
1/1/2008 9.15AM,1/1/2008 11.05AM,zz,10
Query should return hourly counts at each hour, but should include the user if the transaction hasnt ended yet. for example user xx should be included in 8AM, 9AM, 10AM and 11AM counts as his transaction hasnt ended till 11.15AM. AND yy should be included in 8AM, 9AM counts
The query has to check someway if the transaction ended or not and if it didnt include it in that hour count.
The table is big with several thousand records.
Looking forward for your help
January 16, 2008 at 5:18 pm
Hi Mike,
Theres a snippet of code in the scripts section that counts lines of code if there is a primary key.
SELECT so.name, CurrentCount = coalesce(si.rows,'N/A')
FROM DB..sysobjects so
LEFT JOIN sysindexes si ON si.id = so.id
WHERE so.xtype = 'U' AND (si.indid=1 or si.indid is null)
GROUP BY so.Name, si.rows
ORDER BY so.Name Asc
Where DB is the database with stats you are after. Couple of points. First off if the statistics aren't up to date the record counts will be off.
Secondly if the table has no primary key...no stats either. Thats caught me on a couple of rogue tables.
I've played with various dbcc and update commands to ensure the stats are correct.
DBCC UPDATEUSEAGE (0) WITH COUNT_ROWS, NO_INFOMSGS
DBCC UPDATEUSEAGE ( DB ,Table ) WITH COUNT_ROWS, NO_INFOMSGS
and
UPDATE STATISTICS Table WITH FULLSCAN, ALL
all of which don't guarantee success.
To be sure [qupte]select count (*) from xxx[/quote] is still my favourite.
Cheers
Jamie
January 16, 2008 at 5:34 pm
OK having re read I realise I've answered the wrong question...
I have a table with several transactions and i want to count # of transactions at each hour
StartTime, EndTime, Transaction_UserID, ServerID
1/1/2008 8.05AM,1/1/2008 11.05AM,xx,10
1/1/2008 8.10AM,1/1/2008 9.55AM,yy,10
1/1/2008 9.15AM,1/1/2008 11.05AM,zz,10
Query should return hourly counts at each hour, but should include the user if the transaction hasnt ended yet. for example user xx should be included in 8AM, 9AM, 10AM and 11AM counts as his transaction hasnt ended till 11.15AM. AND yy should be included in 8AM, 9AM counts
Would this work(pseudo)?
SELECT COUNT(*), DATEPART(hh,StartTime), Transaction_UserID
FROM Table
WHERE StartTime>='20080117'
GROUP BY DATEPART(hh,StartTime),Transaction_UserID
This would give you every trans started in a given hour grouped by user. You'd need to specify a date otherwise 3am is going to show yesterdays count as well as todays etc.
January 16, 2008 at 6:29 pm
Create a table with your intervals:
StartTime, EndTime
1/1/2008 00:00, 1/1/2208 01:00
1/1/2008 01:00, 1/1/2208 02:00
....
Don't worry, it's just about 85k rows per 10 years, not a big deal for SQL Server.
Disk size required: 85k * (8+8) bytes = 1.4M (if you use datetime datatype, 700k if smalldatetime).
I guess your server can carry it. Even in memory. For 10 years.
Just don't forget to index it.
Then do this:
[Code]
SELECT I.StartTime AS IntervalStart, I.EndTime AS IntervalEnd,
T.Transaction_UserID, COUNT(*)
FROM dbo.Intervals I
INNER JOIN dbo.Transactions T ON T.StartTime « I.EndTime AND T.EndTime »= I.StartTime
-- Replace « » with proper operators here, it's just because of web site
WHERE I.StartTime BETWEEN {enter the time interval you are interested in}
GROUP BY I.StartTime, I.EndTime, T.Transaction_UserID
[/Code]
This will return all intervals having at least on transaction.
If you want to see intervals without transaction as well replace INNER JOIN with LEFT JOIN.
_____________
Code for TallyGenerator
January 16, 2008 at 6:58 pm
Jamie and Sergiey
Thank u for quick reply, i am going to try these solutions and report back.
January 25, 2008 at 4:02 pm
the following query gives me user count as of 11AM, if transaction hasn't ended as of 11AM (and they are logged in system from whatever time say 9AM in the morning)
Declare @sampleday SMALLDATETIME
set @varday='01/01/2008 11:00:00'
select count(pk_TRANSACTIONID) from TRANSACTION_DB
where @varday between StartTime and EndTime
Now, is there a way to expand this to timestamps from 6AM to 6PM daily and then further to a week or month... Looking for a way to change the @varday times automatically instead of hard-coding
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply