RE: How to find this year and past year compare in business

shamshad.ali

Hall of Fame

Points: 3850

May 5, 2014 at 11:40 pm

#1710923

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) = @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 ) 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,