query to return zero value when no rows are found

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

query to return zero value when no rows are found

Author

Topic

phyxe
Starting Member

13 Posts

Posted - 2012-06-22 : 00:55:25

hi there!!!

i know that this has been somewhat of an old issue since when i tried searching the net i found lots of suggestions to take but trying some of them i cant seem to get the right thing for my needs.

basically i need to run a query on a transaction log wherein i need to get the transactions made on a specified span of time when i ran my query it always omit those fields that has no transaction. what i get always look like this

Date TOTAL Captured for the day
----------------------- --------------------------
2012-06-01 65
2012-06-04 58
2012-06-05 44

and what i need as a result is like this
Date TOTAL Captured for the day
----------------------- --------------------------
2012-06-01 65
2012-06-02 0
2012-06-03 0
2012-06-04 58
2012-06-05 44

so that the complete days of the week or month will be there
by the ways my query goes like this:

select dateadd(day, datediff(day, 0, txndate), 0) as [Date], count (*) as [TOTAL Captured for the day]
from Txnlog
where user_name ='user'
	and txnDate >= dateadd(MONTH,datediff(MONTH,0,getdate()),0)
	and txnDate <  dateadd(MONTH,datediff(MONTH,0,getdate())+1,0
order by dateadd(day, datediff(day, 0, txndate), 0) asc

thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-22 : 01:02:32

[code]
declare @min datetime,@max datetime

select @min = min(txndate),@max=max(txndate)
from Txnlog

select f.[date],coalesce([TOTAL Captured for the day],0)
from dbo.CalendarTable(@min,@max,0,0)f
left join
(
select dateadd(day, datediff(day, 0, txndate), 0) as [Date], count (*) as [TOTAL Captured for the day]
from Txnlog
where user_name ='user'
and txnDate >= dateadd(MONTH,datediff(MONTH,0,getdate()),0)
and txnDate < dateadd(MONTH,datediff(MONTH,0,getdate())+1,0
)t
on t.[date] = f.[date]
order by f.[date] asc
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-22 : 01:02:57

calendar table is here

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

phyxe
Starting Member

13 Posts

Posted - 2012-06-22 : 01:20:27

thanks for that quick reply! i'll try this one as soon as i got the time today :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-22 : 22:49:31

welcome
Lemme knw if you need any more help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources