

SSC Rookie
Group: General Forum Members
Last Login: Monday, May 19, 2014 7:48 AM
Points: 39,
Visits: 113


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, Customer 1, 2Dec2013, A 2, 3Jan2014, A 3, 2Mar2014, B 4, 25Nov2013, C
I want results like
Customer, Business (this year activity/last year activity) A, 1/1 B, 1/0 C, 0/1
How 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.CustomerId where t.customerId is not null group by t.CustomerId, ReceivedDate




SSCommitted
Group: General Forum Members
Last Login: 2 days ago @ 4:06 AM
Points: 1,917,
Visits: 19,596


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 lastyear FROM TransData GROUP BY Customer
______________________________________________________________ you can lead a user to data....but you cannot make them think and remember....every day is a school day




SSC Rookie
Group: General Forum Members
Last Login: Monday, May 19, 2014 7:48 AM
Points: 39,
Visits: 113


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 = null AS  exec CustomerMonthlyActivityComparison @Year = 2013, @Location = '10,12,13,14,15, 19,20', @CustomerId = 12162 BEGIN SET NOCOUNT ON; if @Year = 0 set @Year = YEAR(GetDate()) if @Location = '' or @Location is null set @Location = '10,12,13,14,15,19,20' set @Location = '12,15' set @CustomerId = 10903679  set @CustomerId = 10903680 if @CustomerId = 0 SET @CustomerId = null
select customerId, cst_shortName, CustType, Rt_RForLocation, ST_SiteName, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, [Dec], CurrentYearTotal, LastYearTotal From ( 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) = @Year1 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) = @Year1 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) = @Year1 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) = @Year1 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) = @Year1 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) = @Year1 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) = @Year1 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) = @Year1 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) = @Year1 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) = @Year1 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) = @Year1 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) = @Year1 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 ) LastYearTotal
from RootTable rt inner join dbo.Split(@Location,',') loc on rt.RT_RForLocation = loc.Data WHERE rt.RT_CustomerID IS NOT NULL AND rt.Rt_void = 0 AND rt.RT_BillTo = 0 GROUP BY rt.RT_RForLocation, rt.RT_CustomerID
union
select 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) = @Year1 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) = @Year1 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) = @Year1 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) = @Year1 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) = @Year1 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) = @Year1 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) = @Year1 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) = @Year1 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) = @Year1 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) = @Year1 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) = @Year1 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) = @Year1 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 ) LastYearTotal
from RootTable rt inner join [Master] m on m.M_MasterID = rt.RT_MasterID inner join dbo.Split(@Location,',') loc on rt.RT_RForLocation = loc.Data WHERE M_CustomerID IS NOT NULL and rt.Rt_void = 0 AND rt.RT_BillTo = 1 GROUP BY rt.RT_RForLocation, M_CustomerID)x inner join Customer C on c.CST_CustomerID = x.CustomerId and c.cst_CustomerId = case when @CustomerId is null then c.CST_CustomerID else @CustomerId end inner 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 != 0 order by CST_ShortName CST_CustomerID END



