Hello,

I am looking for some help on the following:

How to write a query that displays the result in two columns: Column 1 should contain a sum of all 'completed' months for this year. Column 2 should contain a sum for the rest of the year but for last year...

Example.

If I run the query today, it will show a sum in column 1 based on Jan-june (june beingthe last 'full' month). In column 2 it will show a sum for the months july-dec for LAST year.

This query is for planning purpose. Buy comparing the figures we will be able to tell how are plans are holding up.

Please share table structure with some sample data.

Please post sample data + desired result. Always state what version you are using.

SELECT 
SUM(
CASE
WHEN
DATE BETWEEN
CAST(DATEADD(MM,-1,DATEADD(dd(DAY(GETDATE())-1),GETDATE())) AS DATE) --First Day Of Previous Month
AND GETDATE()
THEN ColumnName
ELSE 0
END
) AS Column1,
SUM(
CASE
WHEN
DATE BETWEEN
CAST(DATEADD(yy,-1,DATEADD(dd,-(DAY(GETDATE())-1),GETDATE())) AS DATE) --First Day Of Last Year's Current Month
AND 
DATEADD(MILLISECOND, -3, DATEADD(YEAR,DATEDIFF(YEAR, 0, DATEADD(YEAR, -1, GETDATE())) + 1, 0)) -- Last of the Last Year
THEN ColumnName
ELSE 0
END
) AS Column2
FROm TABLENAME

Are your periods always 6 months in length starting on either January or June?  Or do you want periods that vary according to the current date (i.e., if you run the query in June it will show you totals for Dec ~ May and June ~ Nov?  Assuming the latter:

set nocount on;
create table #t1 (
  tdate date not null, 
  amount decimal(10,2) not null);
insert #t1(tdate, amount) values ('20120625', 10), ('20120805', 20), ('20121224', 5), 
('20130501', 10), ('20130701', 100), ('20120531', 1000), ('20130101', 2), ('20120701', 7); 
select * from #t1 order by tdate;
go
declare @target date; 
declare @t1 date, @t2 date;
set @target = CURRENT_TIMESTAMP;
set @t1 = dateadd(day, -(datepart(day, @target)) + 1, @target);
select @target, @t1;
/* If I run the query today, it will show a sum in column 1 based on Jan-june (june beingthe last 'full' month). 
In column 2 it will show a sum for the months july-dec for LAST year. */
select tdate, 
	case when tdate < @t1 and tdate >= dateadd(month, -6, @t1) then amount else 0 end as x, 
	case when tdate < dateadd(month, -6, @t1) and tdate >= dateadd(month, -12, @t1) then amount else 0 end as y 
from #t1 order by tdate; 
drop table #t1;

You might find a calendar table very useful for such queries.  Search in the forums for suggestions about creating one.

>> If I run the query today, it will show a sum in column 1 based on Jan-June (June being the last 'full' month). In column 2 it will show a sum for the months July-Dec for LAST year. <<

Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML. The report period table gives a name to a range of dates that is common to the entire enterprise. 

I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. The pattern for validation is '[12][0-9][0-9][0-9]-00-00' and '[12][0-9][0-9][0-9]-[01][0-9]-00'
CREATE TABLE Planning_Report_Periods
(planning_period_month CHAR(10) NOT NULL PRIMARY KEY
  CHECK (planning_period_month 
   LIKE '[12][0-9][0-9][0-9]-[01][0-9]-00'),
 current_report_start_date DATE NOT NULL,
 current_report_end_date DATE NOT NULL,
  CHECK (current_report_start_date <= current_report_end_date),
 prior_report_start_date DATE NOT NULL,
 prior_report_end_date DATE NOT NULL,
  CHECK (prior_report_start_date <= prior_report_end_date),
etc);

You will now load this table with many years of date ranges. Your example would be: 

INSERT INTO Planning_Report_Periods
VALUES 
'2013-06-00', --- June period
'2013-01-01', --- January start of current part of year
'2013-06-30', --- June end of current part of year
'2012-07-01', --- July start of prior part of year
'2012-12-31' --- June end of prior part of year
); 

The report then follows the usual idiom. 

SELECT @in_planning_period_month,
       SUM (CASE WHEN F.something_date 
            BETWEEN P.current_report_start_date 
                AND P.current_report_end_date)
       AS current_foobar_tot,
       SUM (CASE WHEN F.something_date 
            BETWEEN P.prior_report_start_date 
                AND P.prior_report_end_date)
       AS prior_foobar_tot
 FROM Foobar AS F, Planning_Report_Periods AS P
 WHERE P.planning_period_month = @in_planning_period_month;