SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Counting zeros
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Shivas Irons
Starting Member

Canada
1 Posts

Posted - 11/11/2002 :  17:44:01  Show Profile  Reply with Quote
I have a table with transactional data in it. Each row represents one transaction and has a field (dCreated) which identifies when it was created.

With the following syntax I can count how many transactions occurred per minute:

SELECT COUNT(*) AS [count],
DAY(dCreated) AS d,
{ fn HOUR(dCreated) } AS h,
{ fn MINUTE(dCreated) } AS m
FROM tblAuditUploadAppProfile
GROUP BY DAY(dCreated), { fn HOUR(dCreated) }, { fn MINUTE(dCreated) }
ORDER BY DAY(dCreated), { fn HOUR(dCreated) }, { fn MINUTE(dCreated) }

But if a minute passes with no transactions it doesn't show up here. How do I 'show the zeros'?

Thanks,

Cam

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 11/11/2002 :  19:46:49  Show Profile  Visit nr's Homepage  Reply with Quote
You will have to create a table with all the minutes in it and outer join from it.

You can use a derived table too.

Also you could put this result into a temp table then fill in the gaps with 0's.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000