SQLServerCentral » SQL Server 2012 » SQL Server 2012 - T-SQL » How to find this year and past year compare in businessInstantForum 2016-2 FinalSQLServerCentralhttps://www.sqlservercentral.com/Forums/SQLServerCentralSun, 30 Apr 2017 09:33:00 GMT20How to find this year and past year compare in businesshttps://www.sqlservercentral.com/Forums/FindPost1567458.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, Customer
1, 2-Dec-2013, A
2, 3-Jan-2014, A
3, 2-Mar-2014, B
4, 25-Nov-2013, 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...
[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.CustomerId
where t.customerId is not null
group by t.CustomerId, ReceivedDate
[/code]
Mon, 05 May 2014 23:40:21 GMTshamshad.aliRE: How to find this year and past year compare in businesshttps://www.sqlservercentral.com/Forums/FindPost1567779.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 = 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,
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 [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
[/code]Mon, 05 May 2014 23:40:21 GMTshamshad.aliRE: How to find this year and past year compare in businesshttps://www.sqlservercentral.com/Forums/FindPost1567485.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 lastyear
FROM TransData
GROUP BY Customer
[/code]Mon, 05 May 2014 06:29:35 GMTJ Livingston SQL