select t.customerId, YEAR(Receiveddate),sum(case month(ReceivedDate) when 1 then 1 else 0 end )as Jan,sum(case month(ReceivedDate) when 2 then 1 else 0 end )as Feb,sum(case month(ReceivedDate) when 3 then 1 else 0 end )as Mar,sum(case month(ReceivedDate) when 4 then 1 else 0 end )as Apr,sum(case month(ReceivedDate) when 5 then 1 else 0 end )as May,sum(case month(ReceivedDate) when 6 then 1 else 0 end )as Jun,sum(case month(ReceivedDate) when 7 then 1 else 0 end )as Jul,sum(case month(ReceivedDate) when 8 then 1 else 0 end )as Aug,sum(case month(ReceivedDate) when 9 then 1 else 0 end )as Sep,sum(case month(ReceivedDate) when 10 then 1 else 0 end )as Oct,sum(case month(ReceivedDate) when 11 then 1 else 0 end )as Nov,sum(case month(ReceivedDate) when 12 then 1 else 0 end )as [Dec]from TransTable t right join (select customerId from transTable group by customerId ) cc on cc.customerId = t.CustomerIdwhere t.customerId is not nullgroup by t.CustomerId, ReceivedDate

SELECT Customer,MAX(case when YEAR(ReceivedDate) = 2014 then 1 else 0 end ) as thisyear,MAX(case when YEAR(ReceivedDate) = 2013 then 1 else 0 end ) as lastyearFROM TransDataGROUP BY Customer

Alter Proc CustomerMonthlyActivityComparison @Year int, @Location varchar(50), @CustomerId int = nullAS-- exec CustomerMonthlyActivityComparison @Year = 2013, @Location = '10,12,13,14,15, 19,20', @CustomerId = 12162BEGINSET NOCOUNT ON;if @Year = 0 set @Year = YEAR(GetDate())if @Location = '' or @Location is nullset @Location = '10,12,13,14,15,19,20'--set @Location = '12,15'--set @CustomerId = 10903679-- set @CustomerId = 10903680if @CustomerId = 0 SET @CustomerId = nullselect customerId, cst_shortName, CustType, Rt_RForLocation, ST_SiteName,Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, [Dec], CurrentYearTotal, LastYearTotalFrom (select RT_CustomerID CustomerId, 'Forwarder' AS CustType, RT_RForLocation,CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 1 and year(rt.RT_ReceivedDate) = @Year then 1 else 0 end )) + '/' + CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 1 and year(rt.RT_ReceivedDate) = @Year-1 then 1 else 0 end )) Jan,CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 2 and year(rt.RT_ReceivedDate) = @Year then 1 else 0 end )) + '/' + CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 2 and year(rt.RT_ReceivedDate) = @Year-1 then 1 else 0 end )) Feb,CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 3 and year(rt.RT_ReceivedDate) = @Year then 1 else 0 end )) + '/' + CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 3 and year(rt.RT_ReceivedDate) = @Year-1 then 1 else 0 end )) Mar,CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 4 and year(rt.RT_ReceivedDate) = @Year then 1 else 0 end )) + '/' + CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 4 and year(rt.RT_ReceivedDate) = @Year-1 then 1 else 0 end )) Apr,CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 5 and year(rt.RT_ReceivedDate) = @Year then 1 else 0 end )) + '/' + CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 5 and year(rt.RT_ReceivedDate) = @Year-1 then 1 else 0 end )) May,CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 6 and year(rt.RT_ReceivedDate) = @Year then 1 else 0 end )) + '/' + CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 6 and year(rt.RT_ReceivedDate) = @Year-1 then 1 else 0 end )) Jun,CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 7 and year(rt.RT_ReceivedDate) = @Year then 1 else 0 end )) + '/' + CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 7 and year(rt.RT_ReceivedDate) = @Year-1 then 1 else 0 end )) Jul,CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 8 and year(rt.RT_ReceivedDate) = @Year then 1 else 0 end )) + '/' + CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 8 and year(rt.RT_ReceivedDate) = @Year-1 then 1 else 0 end )) Aug,CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 9 and year(rt.RT_ReceivedDate) = @Year then 1 else 0 end )) + '/' + CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 9 and year(rt.RT_ReceivedDate) = @Year-1 then 1 else 0 end )) Sep,CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 10 and year(rt.RT_ReceivedDate) = @Year then 1 else 0 end )) + '/' + CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 10 and year(rt.RT_ReceivedDate) = @Year-1 then 1 else 0 end )) Oct,CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 11 and year(rt.RT_ReceivedDate) = @Year then 1 else 0 end )) + '/' + CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 11 and year(rt.RT_ReceivedDate) = @Year-1 then 1 else 0 end )) Nov,CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 12 and year(rt.RT_ReceivedDate) = @Year then 1 else 0 end )) + '/' + CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 12 and year(rt.RT_ReceivedDate) = @Year-1 then 1 else 0 end )) [Dec],sum(case when year(rt.RT_ReceivedDate) = @Year then 1 else 0 end ) CurrentYearTotal,sum(case when year(rt.RT_ReceivedDate) = @Year - 1 then 1 else 0 end ) LastYearTotalfrom RootTable rt inner join dbo.Split(@Location,',') loc on rt.RT_RForLocation = loc.DataWHERE rt.RT_CustomerID IS NOT NULL AND rt.Rt_void = 0 AND rt.RT_BillTo = 0GROUP BY rt.RT_RForLocation, rt.RT_CustomerIDunionselect M_CustomerID CustomerId, 'Shipper' AS CustType, RT_RForLocation,CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 1 and year(rt.RT_ReceivedDate) = @Year then 1 else 0 end )) + '/' + CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 1 and year(rt.RT_ReceivedDate) = @Year-1 then 1 else 0 end )) Jan,CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 2 and year(rt.RT_ReceivedDate) = @Year then 1 else 0 end )) + '/' + CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 2 and year(rt.RT_ReceivedDate) = @Year-1 then 1 else 0 end )) Feb,CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 3 and year(rt.RT_ReceivedDate) = @Year then 1 else 0 end )) + '/' + CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 3 and year(rt.RT_ReceivedDate) = @Year-1 then 1 else 0 end )) Mar,CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 4 and year(rt.RT_ReceivedDate) = @Year then 1 else 0 end )) + '/' + CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 4 and year(rt.RT_ReceivedDate) = @Year-1 then 1 else 0 end )) Apr,CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 5 and year(rt.RT_ReceivedDate) = @Year then 1 else 0 end )) + '/' + CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 5 and year(rt.RT_ReceivedDate) = @Year-1 then 1 else 0 end )) May,CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 6 and year(rt.RT_ReceivedDate) = @Year then 1 else 0 end )) + '/' + CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 6 and year(rt.RT_ReceivedDate) = @Year-1 then 1 else 0 end )) Jun,CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 7 and year(rt.RT_ReceivedDate) = @Year then 1 else 0 end )) + '/' + CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 7 and year(rt.RT_ReceivedDate) = @Year-1 then 1 else 0 end )) Jul,CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 8 and year(rt.RT_ReceivedDate) = @Year then 1 else 0 end )) + '/' + CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 8 and year(rt.RT_ReceivedDate) = @Year-1 then 1 else 0 end )) Aug,CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 9 and year(rt.RT_ReceivedDate) = @Year then 1 else 0 end )) + '/' + CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 9 and year(rt.RT_ReceivedDate) = @Year-1 then 1 else 0 end )) Sep,CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 10 and year(rt.RT_ReceivedDate) = @Year then 1 else 0 end )) + '/' + CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 10 and year(rt.RT_ReceivedDate) = @Year-1 then 1 else 0 end )) Oct,CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 11 and year(rt.RT_ReceivedDate) = @Year then 1 else 0 end )) + '/' + CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 11 and year(rt.RT_ReceivedDate) = @Year-1 then 1 else 0 end )) Nov,CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 12 and year(rt.RT_ReceivedDate) = @Year then 1 else 0 end )) + '/' + CONVERT(varchar(10), sum(case when month(rt.RT_ReceivedDate) = 12 and year(rt.RT_ReceivedDate) = @Year-1 then 1 else 0 end )) [Dec],sum(case when year(rt.RT_ReceivedDate) = @Year then 1 else 0 end ) CurrentYearTotal,sum(case when year(rt.RT_ReceivedDate) = @Year - 1 then 1 else 0 end ) LastYearTotalfrom RootTable rt inner join [Master] m on m.M_MasterID = rt.RT_MasterID inner join dbo.Split(@Location,',') loc on rt.RT_RForLocation = loc.DataWHERE M_CustomerID IS NOT NULL and rt.Rt_void = 0 AND rt.RT_BillTo = 1GROUP BY rt.RT_RForLocation, M_CustomerID)xinner join Customer C on c.CST_CustomerID = x.CustomerId and c.cst_CustomerId = case when @CustomerId is null then c.CST_CustomerID else @CustomerId endinner join Location Loc on x.RT_RForLocation = Loc.LOC_LocationID inner join [Site] st on Loc.Loc_SiteID = st.ST_SiteID where CurrentYearTotal !=0 or LastYearTotal != 0order by CST_ShortName --CST_CustomerID END