DECLARE @Table1 TABLE
    (Month2Date VARCHAR(15), Yesterday VARCHAR(15), PrevMonth VARCHAR(15))

INSERT INTO @Table1
SELECT 'Month to Date', 'Yesterday', 'PrevMonth'

SELECT * FROM @Table1

--I am trying to get mtd, yesterday and previos month data in 1 table. 
--Can someone please help me..

--===== TEST DATA =========
declare @a table
 (Id int, OrderDate DateTime, OrderQty int );
 
INSERT INTO @a
 ( Id, OrderDate, OrderQty )
VALUES
 (1, '26 Aug 2012', 17),
 (1, '27 Aug 2012', 1),
 (1, '01 Sep 2012', 2),
 (1, '08 Sep 2012', 3),
 (1, '09 Sep 2012', 4),
 (1, '11 Sep 2012', 5),
 (1, '16 Sep 2012', 6),
 (1, '18 Sep 2012', 7),
 (1, '21 Sep 2012', 8),
 (1, '22 Sep 2012', 9),
 (2, '21 Sep 2012', 4),
 (2, '21 Sep 2012', 1),
 (2, '22 Sep 2012', 33);
 
--select * from @a 

--===== SPECULATIVE SOLUTION ===========
declare @today DateTime;
set @today = '22 Sep 2012';

select 
	Id,
	Yesterday = SUM(CASE WHEN OrderDate = x.Yesterday THEN OrderQty ELSE 0 END),
	mtd = SUM(CASE WHEN OrderDate >= y.FirstDayOfMonth THEN OrderQty ELSE 0 END),
	PrevMth = SUM(CASE WHEN OrderDate >= z.FirstDayOfPrevMonth AND OrderDate < y.FirstDayOfMonth THEN OrderQty ELSE 0 END)
from @a
cross apply
	(select DATEADD(DAY, -1, @today)) x (Yesterday)
cross apply
	(select DATEADD(MONTH,DATEDIFF(MONTH,0,@today),0)) y (FirstDayOfMonth)
cross apply
	(select DATEADD(MONTH, -1, y.FirstDayOfMonth)) z (FirstDayOfPrevMonth)
group by id;