SQLServerCentral.com / SQL Server 2012 / SQL Server 2012 - T-SQL / How to find this year and past year compare in business / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 30 Jul 2014 05:45:16 GMT20RE: How to find this year and past year compare in businesshttp://www.sqlservercentral.com/Forums/Topic1567458-3077-1.aspxYeah, I fixed it myself like following, Just wanted to share it so that others can get some help from code below:[code="sql"]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[/code]Mon, 05 May 2014 23:40:21 GMTshamshad.aliRE: How to find this year and past year compare in businesshttp://www.sqlservercentral.com/Forums/Topic1567458-3077-1.aspxmaybe something like this will help ?[code="sql"]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[/code]Mon, 05 May 2014 06:29:35 GMTJ Livingston SQLHow to find this year and past year compare in businesshttp://www.sqlservercentral.com/Forums/Topic1567458-3077-1.aspxWe 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...[code="sql"]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[/code]Mon, 05 May 2014 04:05:35 GMTshamshad.ali