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 ,)GOinsert 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_thingwhere (MONTH(create_date) = MONTH(GETDATE())) AND (YEAR(create_date) = YEAR(GETDATE()))UNIONselect count (*) as [Last Month]from date_thingwhere (MONTH(create_date) = (MONTH(GETDATE()))-1) AND (YEAR(create_date) = YEAR(GETDATE()))This query brings back:[This Month]34What I would like is:[This Month] | [Last Month]3 | 4Any 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 OptimizerTG |
|
|
|
|
|