How to return rows based on condition

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 2000 Forums

Transact-SQL (2000)

How to return rows based on condition

Author

Topic

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-03-07 : 22:57:24

The query below returns the rows which were added today. How can I edit the query to return all the rows and if there were not posts today then it will simply return 0

SELECT f.ForumID,COUNT(f.ForumID) AS 'Total Posts' FROM Forums f
LEFT OUTER JOIN Posts p ON f.ForumID = p.ForumID
WHERE CONVERT(varchar(10),p.PostDate,101) = CONVERT(varchar(10),GETDATE(),101)
GROUP BY f.ForumID

Mohammad Azam
www.azamsharp.net

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-03-07 : 23:01:00

remove the where clause for date check
and union that with you existing query?

--------------------
keeping it simple...

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-03-07 : 23:05:35

Thanks a lot. I made this query and its working correctly. thanks for your help!

SELECT f.ForumID,0 AS 'Total Posts' FROM Forums f
LEFT OUTER JOIN Posts p ON f.ForumID = p.ForumID
GROUP BY f.ForumID

UNION

SELECT f.ForumID,COUNT(f.ForumID) AS 'Total Posts' FROM Forums f
LEFT OUTER JOIN Posts p ON f.ForumID = p.ForumID
WHERE CONVERT(varchar(10),p.PostDate,101) = CONVERT(varchar(10),GETDATE(),101)
GROUP BY f.ForumID

Mohammad Azam
www.azamsharp.net

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-03-07 : 23:42:50

Ohh wait the above query selects a single forum twice. I guess I need to use the minus or EXecpte operator

Mohammad Azam
www.azamsharp.net

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-08 : 00:40:03

Try this :

select	f.ForumID, isnull(sum(post_count), 0) as 'Total Posts'
from	Forums f left join 
	(
		select	ForumID, count(*) as post_count
		from	Posts 
		where	PostDate	>= dateadd(day, 0, datediff(day, 0, getdate()))
		and	PostDate	<  dateadd(day, 0, datediff(day, -1, getdate()))
		group by ForumID
	) as p
	on	f.ForumID	= p.ForumID
group by f.ForumID

And also follow the method instead i used of CONVERT(varchar(10),p.PostDate,101) = CONVERT(varchar(10),GETDATE(),101). Just do a search in this site for datetime, and you will find the answer to why it is prefered this way.

----------------------------------
'KH'

azamsharp
Posting Yak Master

201 Posts

Posted - 2006-03-08 : 02:27:18

Thanks a million!

Mohammad Azam
www.azamsharp.net

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources