title: Customer Sales Trend Query
body: <p>I got a issue on with my query where I need to make sure the salesQty2012 period is same as SalesQty2011 simply because 2012 sales still not over and my comparison will be wrong.
  My question is how I can write a query to bring salesqty 2011 as per current period of time . my orignal query to fetch above data :</p>
  
  <pre><code>    Status,CustomerGroupCode,CustomerGroup,CustomerCode,CustomerName,DivisionCode,DivisionName,SalesLocation,RouteManagerCode,RouteManager,ChannelCode,Channel,CustomerCategoryCode,CustomerCategory,InvRoute,CustomerPeymentTermsCode,CustomerPeymentTerms,CreditDays,CreditLimit,LastInvoiceDate,SalesQty2012,SalesQty2011
  ACTIVE,NULL,NO GROUP                           ,CA001  ,A L S Super Market                           ,1,DUBAI               ,Deria Dubai                        ,SDD11,SAJI M ABRAHAM,SDD11,IMPULSE CUSTOMERS             ,G    ,Groceries                          ,36,CS,CASH,0,0,27/10/2012 0:00,160,187
  ACTIVE,NULL,NO GROUP                           ,CA003  ,A.M.M Super Market.  0                       ,2,ABU DHABI           ,Abu Dhabi                          ,SDD11,SAJI M ABRAHAM,SDD11,IMPULSE CUSTOMERS             ,G    ,Groceries                          ,4,CS,CASH,0,0,06/11/2012 0:00,69.625,96.4583333
  ACTIVE,NULL,NO GROUP                           ,CA005  ,Aalian Super Market                          ,2,ABU DHABI           ,Abu Dhabi                          ,SDD11,SAJI M ABRAHAM,SDD11,IMPULSE CUSTOMERS             ,G    ,Groceries                          ,12,CS,CASH,0,0,27/08/2012 0:00,118.125,259.25
  ACTIVE,NULL,NO GROUP                           ,CA006  ,Aawan S/M.                                   ,2,ABU DHABI           ,Abu Dhabi                          ,SDD20,NULL,SDD11,IMPULSE CUSTOMERS             ,G    ,Groceries                          ,99,CS,CASH,0,0,NULL,NULL,NULL
  ACTIVE,NULL,NO GROUP                           ,CA008  ,Abbas Bin Moideen Super Market               ,2,ABU DHABI           ,Abu Dhabi                          ,SDD11,SAJI M ABRAHAM,SDD11,IMPULSE CUSTOMERS             ,G    ,Groceries                          ,4,CS,CASH,0,0,07/11/2012 0:00,185.625,188
  ACTIVE,NULL,NO GROUP                           ,CA013  ,Abdu Grocery   0                             ,1,DUBAI               ,Bur Dubai                          ,SDD11,SAJI M ABRAHAM,SDD11,IMPULSE CUSTOMERS             ,G    ,Groceries                          ,44,CS,CASH,0,0,24/04/2011 0:00,NULL,10
  ACTIVE,NULL,NO GROUP                           ,CA016  ,Abdu Rahiman Al Haj                          ,1,DUBAI               ,Dubai                              ,SDD11,SAJI M ABRAHAM,SDD11,IMPULSE CUSTOMERS             ,G    ,Groceries                          ,28,CS,CASH,0,0,29/10/2012 0:00,306.625,351.875
  ACTIVE,NULL,NO GROUP                           ,CA017  ,Abdu Rahman  Khaleefa0                       ,1,DUBAI               ,Deria Dubai                        ,SDD11,SAJI M ABRAHAM,SDD11,IMPULSE CUSTOMERS             ,G    ,Groceries                          ,36,CS,CASH,0,0,14/10/2012 0:00,60,133.2499999
  ACTIVE,NULL,NO GROUP                           ,CA018  ,Abdul  Khalfan Mohd Grocery   0              ,1,DUBAI               ,Sharjah                            ,SDD11,SAJI M ABRAHAM,SDD11,IMPULSE CUSTOMERS             ,G    ,Groceries                          ,37,CS,CASH,0,0,25/08/2011 0:00,NULL,21
  ACTIVE,NULL,NO GROUP                           ,CA019  ,Abdul Aziz Super Market                      ,2,ABU DHABI           ,Abu Dhabi                          ,SDD11,SAJI M ABRAHAM,SDD11,IMPULSE CUSTOMERS             ,G    ,Groceries                          ,4,CS,CASH,0,0,08/10/2012 0:00,179.25,134.875
  ACTIVE,NULL,NO GROUP                           ,CA020  ,Abdul Aziz Mohd.Sadiq Food Stuff             ,1,DUBAI               ,Sharjah                            ,SDD11,SAJI M ABRAHAM,SDD11,IMPULSE CUSTOMERS             ,G    ,Groceries                          ,41,CS,CASH,0,0,27/08/2012 0:00,10,2
  ACTIVE,NULL,NO GROUP                           ,CA021  ,Abdul Gafoor Bava   0                        ,2,ABU DHABI           ,Abu Dhabi                          ,SDD11,SAJI M ABRAHAM,SDD11,IMPULSE CUSTOMERS             ,G    ,Groceries                          ,12,CS,CASH,0,0,24/09/2012 0:00,40,87.1666666
  ACTIVE,NULL,NO GROUP                           ,CA022  ,Happy Down  Gr                               ,1,DUBAI               ,Dubai                              ,SDD11,SAJI M ABRAHAM,SDD11,IMPULSE CUSTOMERS             ,G    ,Groceries                          ,36,CS,CASH,0,0,31/10/2012 0:00,80,162.2083332
  ACTIVE,NULL,NO GROUP                           ,CA025  ,Abdul Hamid Abdul Karim Grocery  0           ,1,DUBAI               ,Dubai                              ,SDD20,NULL,SDD11,IMPULSE CUSTOMERS             ,G    ,Groceries                          ,99,CS,CASH,0,0,12/10/2008 0:00,NULL,NULL
  ACTIVE,NULL,NO GROUP                           ,CA027  ,Abdul Majid S/M - Baniyas                    ,2,ABU DHABI           ,Abu Dhabi                          ,SDD11,SAJI M ABRAHAM,SDD11,IMPULSE CUSTOMERS             ,G    ,Groceries                          ,5,CS,CASH,0,0,30/10/2012 0:00,750,824.125
  ACTIVE,NULL,NO GROUP                           ,CA029  ,Yasin Ali Reza                               ,1,DUBAI               ,Deria Dubai                        ,SDD11,SAJI M ABRAHAM,SDD11,IMPULSE CUSTOMERS             ,G    ,Groceries                          ,22,CS,CASH,0,0,29/10/2012 0:00,264.125,373.9999999
  ACTIVE,NULL,NO GROUP                           ,CA030  ,Abdul Rahman Abdulla Al Haj                  ,1,DUBAI               ,Dubai                              ,SDD20,NULL,SDD11,IMPULSE CUSTOMERS             ,GR   ,Group                              ,99,CS,CASH,0,0,NULL,NULL,NULL
  ACTIVE,NULL,NO GROUP                           ,CA032  ,Al Zajal Gr#2                                ,3,Al Ain              ,Al Ain                             ,SDD11,SAJI M ABRAHAM,SDD11,IMPULSE CUSTOMERS             ,G    ,Groceries                          ,10,CS,CASH,0,0,25/10/2012 0:00,98,112.2083333
  ACTIVE,NULL,NO GROUP                           ,CA034  ,Abdul Rasool Waswasi Grocery                 ,1,DUBAI               ,Dubai                              ,SDD20,NULL,SDD11,IMPULSE CUSTOMERS             ,GR   ,Group                              ,99,CS,CASH,0,0,NULL,NULL,NULL
  ACTIVE,NULL,NO GROUP                           ,CA036  ,Abdul Raza Ghanim Grocery0                   ,1,DUBAI               ,Deria Dubai                        ,SDD11,SAJI M ABRAHAM,SDD11,IMPULSE CUSTOMERS             ,G    ,Groceries                          ,22,CS,CASH,0,0,04/03/2008 0:00,NULL,NULL
  </code></pre>
  
  <p>here the query</p>
  
  <pre><code>    Select top 20 Status=(CASE WHEN a.LOCK=0 THEN 'ACTIVE'
  WHEN a.LOCK=1 THEN 'INACTIVE' END), 
  CustomerGroupCode=ISNULL((SELECT TOP 1 ITcode FROM BCGMAST WHERE ITTAG='U' AND ITCODE=a.CGPCODE),null), 
  CustomerGroup=ISNULL((SELECT TOP 1 ITDESC FROM BCGMAST WHERE ITTAG='U' AND ITCODE=a.CGPCODE),'NO GROUP'),                               
  a.accode as CustomerCode,
  a.name as CustomerName,
  DivisionCode=ISNULL((SELECT ccode FROM CCMAST WHERE CCMAST.CCODE=a.CCODE),NULL),    
  DivisionName=ISNULL((SELECT TOP 1 CCDES FROM CCMAST WHERE CCMAST.CCODE=a.CCODE),'NO DIVISION'),    
  SalesLocation=ISNULL((SELECT TOP 1 ITDESC FROM BCGMAST WHERE BCGMAST.ITTAG='L' AND BCGMAST.ITCODE=a.SLLOC),'NO SALES LOCATION'),                              
  RouteManagerCode=ISNULL(a.ROUTEMANAGER,NULL),
  RouteManager=(CASE WHEN a.ROUTEMANAGER='SDD11'  THEN 'SAJI M ABRAHAM'   
  WHEN a.ROUTEMANAGER='SDD01' THEN 'EHSAAN JAMEEL' 
  WHEN a.ROUTEMANAGER='SDD12' THEN 'DANISH KHAN'  END), 
  ChannelCode=ISNULL((SELECT TOP 1 GRPCD FROM ACGROUP WHERE ACGROUP.GRPCD=a.GRPCD),NULL),
  Channel=ISNULL((SELECT TOP 1 GPSNM FROM ACGROUP WHERE ACGROUP.GRPCD=a.GRPCD),'NO CHANNEL'),
  CustomerCategoryCode=ISNULL((SELECT TOP 1 ITcode FROM BCGMAST WHERE BCGMAST.ITTAG='Z' AND BCGMAST.ITCODE=a.custcate),null), 
  CustomerCategory=ISNULL((SELECT TOP 1 ITDESC FROM BCGMAST WHERE BCGMAST.ITTAG='Z' AND BCGMAST.ITCODE=a.custcate),'NO CATEGORY'), 
  InvRoute=a.Route,
  CustomerPeymentTermsCode=a.SEQ1,
  CustomerPeymentTerms=(CASE WHEN a.SEQ1='CS' THEN 'CASH'    
  WHEN a.SEQ1='CR' THEN 'CREDIT'    
  WHEN a.SEQ1='CB' THEN 'BILL TO BILL' END),    
  a.crdays as CreditDays,
  a.crlimit as CreditLimit,
  LastInvoiceDate=(SELECT max(S.InvoiceDate)from Sales_Data08 S where S.PartyCode= a.ACCODE),
  SalesQty2012=(SELECT Sum(S.SALESQTY)from Sales_Data S where S.PartyCode = a.ACCODE and InvoiceDate BETWEEN '2012-01-01' AND '2012-12-31'),
  SalesQty2011=(SELECT Sum(S.SALESQTY)from [GRP022011].[dbo].[Sales_Data]  S where S.PartyCode = a.ACCODE and InvoiceDate BETWEEN '2011-01-01' AND '2011-12-31')
  --TotalSalesAMT= (SELECT max(STHEAD.BILLDATE)from sthead where STHEAD.CLSUP = a.ACCODE),
  
  From ACCMST a full join accsub b on    a.accode=b.accode where  a.GRPCD LIKE 'SDD%' and a.LOCK=0
  </code></pre>
  
  <p>from above view I use data to show customer sales trend from below query</p>
  
  <pre><code>SELECT
         [PartyCode]
        ,[PartyName]
        ,[DivisionName]
        ,[SalesLocation]
        ,[InvRoute]
        ,[RouteManager]
        ,[ChannelName]
        ,[CustomerGroupName]
        ,[CustomerCategory]
        ,[InvoiceDate]
        ,[BrandName]
        ,[CATEGORY]
        ,[ItemCode]
        ,[ItemName]
        ,[SALESQTY]
        ,[SALESAMT]
        ,[NetSales]
    CustomerTrend= 
      (
      CASE
        WHEN SQ.SalesQty2011 = 0 and SQ.SalesQty2012 &gt; 0 THEN 'NEW'
        WHEN SQ.SalesQty2011 &gt; 0 and SQ.SalesQty2012 &lt;= 0 THEN 'Lost'
        WHEN SQ.SalesQty2011 = 0 and SQ.SalesQty2012 = 0 THEN 'Inactive'
        WHEN SQ.SalesQty2012 &gt; SQ.SalesQty2011 THEN 'Increasing'
        WHEN SQ.SalesQty2012 &lt;= SQ.SalesQty2011 THEN 'Decreasing'
  
        ELSE 'Nothing' 
      END
  from [Sales_Data10] WHERE InvoiceDate BETWEEN '2011-01-01' AND '2012-12-31'
  
    CROSS APPLY (
                SELECT ISNULL(SV.SalesQty2011, 0) AS SalesQty2011, 
                       ISNULL(SV.SalesQty2012, 0) AS SalesQty2012
                ) SQ
  </code></pre>
  
  <p>To Summarize I need to write query that ensures sales Qty for 2012 and 2011 are in same period of time.</p>
  
  <p>Thanks
  Ali</p>