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)
 Count Comparison by Date

Author  Topic 

jfslater98
Starting Member

7 Posts

Posted - 2005-10-14 : 16:16:21
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-14 : 16:48:08
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
Go to Top of Page
   

- Advertisement -