sql - Current month and last month data in single row

Question

The following table has datatypes as follows:

ItemName varchar, Date Date, Amt varchar, status varchar

ItemName        Date        Amt     

    ABC       1/02/2012     500     
    ABC       8/03/2012     250     

Expected Result

ItemName    LastMonthAmt    CurrentMonthAmt

ABC          500             250

Please help me to write the query for this problem.

Thanks in advance.

What i have tried is as follows:

declare @T table
(
  id varchar(2),
  [Date] datetime,
  [Amt] varchar(5),
  [Status] varchar(5)
)

insert into @T
select '01',   '1/02/12',  '125',   'LM' union all
--select '01',   '1/02/12',  '200',   'Exit' union all
select '01',   '2/02/12',  '250',   'CM' union all
--select '01',   '2/02/12',  '150',  'CM' union all
select '02',   '1/02/12',  '300',   'LM' union all
--select '02',   '1/02/12',  '350',   'CM' union all
--select '02',   '2/02/12',  '220',   'LM' union all
select '02',   '2/02/12',  '140',  'CM'

select id,
       --convert(varchar(8), [Date], 1) as [Date],
       [Amt] as [CM],
      [Amt] as [LM]
from
  (
    select distinct id,
           [Amt],
           row_number() over(partition by id order by [Date] asc) as rn1,
           row_number() over(partition by id order by [Date] desc) as rn2
    from @T
  ) as T
where T.rn1 = 1 or
      T.rn2 = 1
group by id, [Amt]

Answer 1

You can use an aggregate function with a CASE expression similar to this:

select itemname,
  sum(case when month(date) = month(getdate()) -1 then amt else 0 end) LastMonthAmt,
  sum(case when month(date) = month(getdate()) then amt else 0 end) CurrentMonthAmt
from yourtable
group by itemname;

See SQL Fiddle with Demo