Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 How to find this year and past year compare in business Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, May 5, 2014 4:05 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Tuesday, November 29, 2016 3:25 AM 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`
Post #1567458
 Posted Monday, May 5, 2014 6:29 AM
 Hall of Fame Group: General Forum Members Last Login: Yesterday @ 10:45 PM Points: 3,314, Visits: 32,575
 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
Post #1567485
 Posted Monday, May 5, 2014 11:40 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Tuesday, November 29, 2016 3:25 AM 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`
Post #1567779

 Permissions

 Copyright © 2002-2016 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.