## How to find this year and past year compare in business

 Author Message shamshad.ali SSC-Enthusiastic Group: General Forum Members Points: 112 Visits: 418 We have customers who are new to this year (2014) and there same customers in last year (2013). also there are customers we have not received business this year but only last year. so there are 4 conditions.1) New customer (2014) - Customer(B)2) Old Regular customer (2013 and 2014) - Customer(A)3) Last Year (Lost) customer (2013) - Customer(C), no business received in year(2014) For example we have a transaction table:TransactionId, ReceivedDate, Customer1, 2-Dec-2013, A2, 3-Jan-2014, A3, 2-Mar-2014, B4, 25-Nov-2013, CI want results like Customer, Business (this year activity/last year activity)A, 1/1B, 1/0C, 0/1How can i show this for each year? I used to separate it month wise as below but it does not return applying year with each customer anyhow...`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` J Livingston SQL Hall of Fame Group: General Forum Members Points: 3445 Visits: 33000 maybe something like this will help ?`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` ________________________________________________________________you can lead a user to data....but you cannot make them think and remember....every day is a school day shamshad.ali SSC-Enthusiastic Group: General Forum Members Points: 112 Visits: 418 Yeah, I fixed it myself like following, Just wanted to share it so that others can get some help from code below:`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`