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)
 Count Comparison by Date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jfslater98
Starting Member

7 Posts

Posted - 10/14/2005 :  16:16:21  Show Profile  Reply with Quote
Hello,

My apologies if this is a duplicate post, I messed up the first time before completing. I want to compare two counts by date and am close to a solution using UNION, but not sure where I need to go next. Here is the sample data:

CREATE TABLE [date_thing] (
[create_date] [datetime] NULL ,
[date_thing_id] [int] NOT NULL ,
[add_info] [varchar] (250) NULL ,
)
GO

insert date_thing (create_date, date_thing_id, add_info) values ('9/1/2005', 1, 'blah,blah')
insert date_thing (create_date, date_thing_id, add_info) values ('9/2/2005', 2, 'blah,blah')
insert date_thing (create_date, date_thing_id, add_info) values ('9/3/2005', 3, 'blah,blah')
insert date_thing (create_date, date_thing_id, add_info) values ('9/4/2005', 4, 'blah,blah')
insert date_thing (create_date, date_thing_id, add_info) values ('10/1/2005', 5, 'blah,blah,blah')
insert date_thing (create_date, date_thing_id, add_info) values ('10/2/2005', 6, 'blah,blah,blah')
insert date_thing (create_date, date_thing_id, add_info) values ('10/3/2005', 7, 'blah,blah')

Here's my lame swipe at a solution:

select count (*) as [This Month]
from date_thing
where (MONTH(create_date) = MONTH(GETDATE())) AND (YEAR(create_date) = YEAR(GETDATE()))
UNION
select count (*) as [Last Month]
from date_thing
where (MONTH(create_date) = (MONTH(GETDATE()))-1) AND (YEAR(create_date) = YEAR(GETDATE()))

This query brings back:
[This Month]
3
4

What I would like is:
[This Month] | [Last Month]
3 | 4

Any ideas would be much appreciated. Thanks.

John

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 10/14/2005 :  16:48:08  Show Profile  Reply with Quote
Here's an alternative:

select	sum(a) as [this month]
	,sum(b) as [last month]
from	(
	select 	a = case when datediff(month, create_date, getdate()) = 0 then 1 else 0 end
		,b = case when datediff(month, create_date, getdate()) = 1 then 1 else 0 end
	from	date_thing
	where	create_date >= dateadd(month, datediff(month, 0, getdate())-1, 0)
	and	create_date < dateadd(month, datediff(month, 0, getdate())+1, 0)
	) as derivedTable


Be One with the Optimizer
TG

Edited by - TG on 10/14/2005 16:59:43
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.06 seconds. Powered By: Snitz Forums 2000