locked
getting last year data RRS feed

  • Question

  • Hi,

    Could you please help me with the query?

    I have the below data in my table:

    StartDate,EndDate,LY_STARTING_DATE,LY_STARTING_DATE_Tss,Store_Number,Manufacturer,Brand,sales
    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,L,18.32616
    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,LAND,0.2556
    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,PC,0.16368
    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,RED,0.16368
    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,W,0.24101
    2019-03-11,2019-03-17,2018-03-11,2018-03-12,201,LIMITED1,L,12.54497
    2019-03-11,2019-03-17,2018-03-11,2018-03-12,201,LIMITED1,M,0.10406
    2019-03-11,2019-03-17,2018-03-11,2018-03-12,201,LIMITED1,PC,0.16368
    2019-03-11,2019-03-17,2018-03-11,2018-03-12,201,LIMITED1,RED,0.24552
    2019-03-11,2019-03-17,2018-03-11,2018-03-12,201,LIMITED1,W,0.18425

    I need to have the below result

    StartDate,EndDate,LY_STARTING_DATE,LY_STARTING_DATE_Tss,Store_Number,Manufacturer,Brand,sales,ly_sales
    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,L,18.32616,
    12.54497
    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,LAND,0.2556,0
    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,PC,0.16368,
    0.16368
    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,RED,0.16368,
    0.24552
    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,W,0.24101,
    0.18425


    zj

    Tuesday, April 28, 2020 10:03 PM

Answers

  • It would help if you gave us the definition of what is"lasts years sales".  Here is an attempt that produces the result you are looking for with this data.  Hopefully it is what you need or can at least get you started.

    Declare @Sample Table(StartDate date,EndDate date,LY_STARTING_DATE date,LY_STARTING_DATE_Tss date,Store_Number int,Manufacturer varchar(20),Brand varchar(10),sales decimal(9,5));
    Insert @Sample(StartDate,EndDate,LY_STARTING_DATE,LY_STARTING_DATE_Tss,Store_Number,Manufacturer,Brand,sales) Values
    ('2020-03-09','2020-03-15','2019-03-10','2019-03-11',201,'LIMITED1','L',18.32616),
    ('2020-03-09','2020-03-15','2019-03-10','2019-03-11',201,'LIMITED1','LAND',0.2556),
    ('2020-03-09','2020-03-15','2019-03-10','2019-03-11',201,'LIMITED1','PC',0.16368),
    ('2020-03-09','2020-03-15','2019-03-10','2019-03-11',201,'LIMITED1','RED',0.16368),
    ('2020-03-09','2020-03-15','2019-03-10','2019-03-11',201,'LIMITED1','W',0.24101),
    ('2019-03-11','2019-03-17','2018-03-11','2018-03-12',201,'LIMITED1','L',12.54497),
    ('2019-03-11','2019-03-17','2018-03-11','2018-03-12',201,'LIMITED1','M',0.10406),
    ('2019-03-11','2019-03-17','2018-03-11','2018-03-12',201,'LIMITED1','PC',0.16368),
    ('2019-03-11','2019-03-17','2018-03-11','2018-03-12',201,'LIMITED1','RED',0.24552),
    ('2019-03-11','2019-03-17','2018-03-11','2018-03-12',201,'LIMITED1','W',0.18425);
    
    Select cy.StartDate, cy.EndDate, cy.LY_STARTING_DATE,
     cy.LY_STARTING_DATE_Tss, cy.Store_Number, cy.Manufacturer, 
     cy.Brand, IsNull(cy.sales, 0) As sales, IsNull(ly.sales, 0) As ly_sales
    From @Sample cy
    Full Join @Sample ly On ly.StartDate <= DateAdd(year, -1, cy.EndDate) And ly.EndDate >= DateAdd(year, -1, cy.EndDate)
      And cy.Store_Number = ly.Store_Number And cy.Manufacturer = ly.Manufacturer And cy.Brand = ly.Brand
    Where Year(cy.StartDate) = Year(CURRENT_TIMESTAMP);
    Tom
    • Marked as answer by ZaraJ Thursday, May 7, 2020 5:27 PM
    Tuesday, April 28, 2020 11:26 PM
  • The reason I used a Full Join instead of a Left Join is that the Left Join will not find products that had sales last year, but no sales this year.  The full join will find and report those items. 

    However, you are correct that using the Full Join requires more ISNULL functions.  My query should have been

    Select IsNull(cy.StartDate, ly.StartDate) As StartDate,
      IsNull(cy.EndDate, ly.EndDate) As EndDate,
      IsNull(cy.LY_STARTING_DATE, ly.LY_STARTING_DATE) As LY_STARTING_DATE,
      IsNull(cy.LY_STARTING_DATE_Tss, ly.LY_STARTING_DATE_Tss) As LY_STARTING_DATE_Tss,
      IsNull(cy.Store_Number, ly.Store_Number) As Store_Number,
      IsNull(cy.Manufacturer, ly.Manufacturer) As Manufacturer, 
      IsNull(cy.Brand, ly.Brand) As Brand,
      IsNull(cy.sales, 0) As sales,
      IsNull(ly.sales, 0) As ly_sales
    From @Sample cy
    Full Join @Sample ly On ly.StartDate <= DateAdd(year, -1, cy.EndDate) And ly.EndDate >= DateAdd(year, -1, cy.EndDate)
      And cy.Store_Number = ly.Store_Number And cy.Manufacturer = ly.Manufacturer And cy.Brand = ly.Brand
    Where Year(cy.StartDate) = Year(CURRENT_TIMESTAMP);
    Tom
    • Edited by Tom Cooper Wednesday, April 29, 2020 5:46 AM
    • Marked as answer by ZaraJ Thursday, May 7, 2020 5:27 PM
    Wednesday, April 29, 2020 5:34 AM
  • Then maybe

    ;With cte As
    (Select Top 1 cy.StartDate, cy.EndDate, cy.LY_STARTING_DATE, cy.LY_STARTING_DATE_Tss
    From @Sample cy
    Inner Join @Sample ly On cy.LY_STARTING_DATE_Tss = ly.StartDate
    Order By cy.StartDate)
    Select IsNull(cy.StartDate, cte.StartDate) As StartDate,
      IsNull(cy.EndDate, cte.EndDate) As EndDate,
      IsNull(cy.LY_STARTING_DATE, cte.LY_STARTING_DATE) As LY_STARTING_DATE,
      IsNull(cy.LY_STARTING_DATE_Tss, cte.LY_STARTING_DATE_Tss) As LY_STARTING_DATE_Tss,
      IsNull(cy.Store_Number, ly.Store_Number) As Store_Number,
      IsNull(cy.Manufacturer, ly.Manufacturer) As Manufacturer, 
      IsNull(cy.Brand, ly.Brand) As Brand,
      IsNull(cy.sales, 0) As sales,
      IsNull(ly.sales, 0) As ly_sales
    From @Sample cy
    Full Join @Sample ly On ly.StartDate <= DateAdd(year, -1, cy.EndDate) And ly.EndDate >= DateAdd(year, -1, cy.EndDate)
      And cy.Store_Number = ly.Store_Number And cy.Manufacturer = ly.Manufacturer And cy.Brand = ly.Brand
    Left Join cte On cte.LY_STARTING_DATE_Tss = ly.StartDate
    Where Year(cy.StartDate) = Year(CURRENT_TIMESTAMP) Or Year(ly.StartDate) = Year(CURRENT_TIMESTAMP) - 1;
    Tom

    • Marked as answer by ZaraJ Wednesday, May 13, 2020 2:41 PM
    Friday, May 8, 2020 5:24 PM

All replies

  • It would help if you gave us the definition of what is"lasts years sales".  Here is an attempt that produces the result you are looking for with this data.  Hopefully it is what you need or can at least get you started.

    Declare @Sample Table(StartDate date,EndDate date,LY_STARTING_DATE date,LY_STARTING_DATE_Tss date,Store_Number int,Manufacturer varchar(20),Brand varchar(10),sales decimal(9,5));
    Insert @Sample(StartDate,EndDate,LY_STARTING_DATE,LY_STARTING_DATE_Tss,Store_Number,Manufacturer,Brand,sales) Values
    ('2020-03-09','2020-03-15','2019-03-10','2019-03-11',201,'LIMITED1','L',18.32616),
    ('2020-03-09','2020-03-15','2019-03-10','2019-03-11',201,'LIMITED1','LAND',0.2556),
    ('2020-03-09','2020-03-15','2019-03-10','2019-03-11',201,'LIMITED1','PC',0.16368),
    ('2020-03-09','2020-03-15','2019-03-10','2019-03-11',201,'LIMITED1','RED',0.16368),
    ('2020-03-09','2020-03-15','2019-03-10','2019-03-11',201,'LIMITED1','W',0.24101),
    ('2019-03-11','2019-03-17','2018-03-11','2018-03-12',201,'LIMITED1','L',12.54497),
    ('2019-03-11','2019-03-17','2018-03-11','2018-03-12',201,'LIMITED1','M',0.10406),
    ('2019-03-11','2019-03-17','2018-03-11','2018-03-12',201,'LIMITED1','PC',0.16368),
    ('2019-03-11','2019-03-17','2018-03-11','2018-03-12',201,'LIMITED1','RED',0.24552),
    ('2019-03-11','2019-03-17','2018-03-11','2018-03-12',201,'LIMITED1','W',0.18425);
    
    Select cy.StartDate, cy.EndDate, cy.LY_STARTING_DATE,
     cy.LY_STARTING_DATE_Tss, cy.Store_Number, cy.Manufacturer, 
     cy.Brand, IsNull(cy.sales, 0) As sales, IsNull(ly.sales, 0) As ly_sales
    From @Sample cy
    Full Join @Sample ly On ly.StartDate <= DateAdd(year, -1, cy.EndDate) And ly.EndDate >= DateAdd(year, -1, cy.EndDate)
      And cy.Store_Number = ly.Store_Number And cy.Manufacturer = ly.Manufacturer And cy.Brand = ly.Brand
    Where Year(cy.StartDate) = Year(CURRENT_TIMESTAMP);
    Tom
    • Marked as answer by ZaraJ Thursday, May 7, 2020 5:27 PM
    Tuesday, April 28, 2020 11:26 PM
  • Hi ZaraJ,

    I will not advice you to use FULL JOIN , If you use FULL JOIN , you might need to use more ISNULL(). Please change it into LEFT JOIN . It also can get your result . Could you please share us  your logic more clearly . 

    Select cy.StartDate, cy.EndDate, cy.LY_STARTING_DATE,
     cy.LY_STARTING_DATE_Tss, cy.Store_Number, cy.Manufacturer, 
     cy.Brand, IsNull(cy.sales, 0) As sales, IsNull(ly.sales, 0) As ly_sales
    From @Sample cy
    left Join @Sample ly On ly.StartDate <= DateAdd(year, -1, cy.EndDate) And ly.EndDate >= DateAdd(year, -1, cy.EndDate)
      And cy.Store_Number = ly.Store_Number And cy.Manufacturer = ly.Manufacturer And cy.Brand = ly.Brand
    Where Year(cy.StartDate) = Year(CURRENT_TIMESTAMP);

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 29, 2020 3:18 AM
  • The reason I used a Full Join instead of a Left Join is that the Left Join will not find products that had sales last year, but no sales this year.  The full join will find and report those items. 

    However, you are correct that using the Full Join requires more ISNULL functions.  My query should have been

    Select IsNull(cy.StartDate, ly.StartDate) As StartDate,
      IsNull(cy.EndDate, ly.EndDate) As EndDate,
      IsNull(cy.LY_STARTING_DATE, ly.LY_STARTING_DATE) As LY_STARTING_DATE,
      IsNull(cy.LY_STARTING_DATE_Tss, ly.LY_STARTING_DATE_Tss) As LY_STARTING_DATE_Tss,
      IsNull(cy.Store_Number, ly.Store_Number) As Store_Number,
      IsNull(cy.Manufacturer, ly.Manufacturer) As Manufacturer, 
      IsNull(cy.Brand, ly.Brand) As Brand,
      IsNull(cy.sales, 0) As sales,
      IsNull(ly.sales, 0) As ly_sales
    From @Sample cy
    Full Join @Sample ly On ly.StartDate <= DateAdd(year, -1, cy.EndDate) And ly.EndDate >= DateAdd(year, -1, cy.EndDate)
      And cy.Store_Number = ly.Store_Number And cy.Manufacturer = ly.Manufacturer And cy.Brand = ly.Brand
    Where Year(cy.StartDate) = Year(CURRENT_TIMESTAMP);
    Tom
    • Edited by Tom Cooper Wednesday, April 29, 2020 5:46 AM
    • Marked as answer by ZaraJ Thursday, May 7, 2020 5:27 PM
    Wednesday, April 29, 2020 5:34 AM
  • Hi Tom,

    Thank you for your help.

    the Where clause doesn't work correctly, because I have lots of data from 2016 for the StartDate column, here I put sample data from 2020 and 2019.


    <g class="gr_ gr_9 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" data-gr-id="9" id="9">zj</g>



    • Edited by ZaraJ Tuesday, May 5, 2020 6:02 PM
    Tuesday, May 5, 2020 5:54 PM
  • Hi Rachel

    Thank you for your help.

    the Where clause doesn't work correctly, because I have lots of data from 2016 for the StartDate column, here I put sample data from 2020 and 2019.


    <g class="gr_ gr_31 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" data-gr-id="31" id="31">zj</g>



    • Edited by ZaraJ Tuesday, May 5, 2020 6:02 PM
    Tuesday, May 5, 2020 5:55 PM
  • thanks, Tom

    As I mentioned above, the Where clause doesn't work correctly, because I have lots of data from 2016 for the StartDate column, here I put sample data from 2020 and 2019.


    <g class="gr_ gr_5 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" data-gr-id="5" id="5">zj</g>



    • Edited by ZaraJ Tuesday, May 5, 2020 6:02 PM
    Tuesday, May 5, 2020 5:56 PM
  • It is a good practice to post DDL, Inserting data statement and the output you expect. I pasted Tom's SQL statements to create the table and insert your sample data. Try this:

    SELECT s1.*, ISNULL(s2.sales, 0) AS ly_sale
    FROM @Sample AS s1
    LEFT JOIN @Sample AS s2 
    	ON s2.StartDate BETWEEN s1.LY_STARTING_DATE AND  s1.LY_STARTING_DATE_Tss
    	AND s2.Store_Number = s1.Store_Number AND s2.Manufacturer = s1.Manufacturer AND s2.Brand = s1.Brand
    ORDER BY s1.StartDate DESC;
     

    A Fan of SSIS, SSRS and SSAS

    Tuesday, May 5, 2020 8:45 PM
  • Hi Guys

    Thank you for your help. Could you please help me with the below request?

    Please consider these sample data is about two dates, but I have more data from different years and dates.

    If I have a Brand from last year that doesn't exist in this year, I want to show it and show value sales for it as 0 for this year, because that brand exists in this year. please see the below sample data. e.g Brand=FAN

    Declare @Sample Table(StartDate date,EndDate date,LY_STARTING_DATE date,LY_STARTING_DATE_Tss date,Store_Number int,Manufacturer varchar(20),Brand varchar(10),sales decimal(9,5));
    Insert @Sample(StartDate,EndDate,LY_STARTING_DATE,LY_STARTING_DATE_Tss,Store_Number,Manufacturer,Brand,sales) Values
    ('2020-03-09','2020-03-15','2019-03-10','2019-03-11',201,'LIMITED1','L',18.32616),
    ('2020-03-09','2020-03-15','2019-03-10','2019-03-11',201,'LIMITED1','LAND',0.2556),
    ('2020-03-09','2020-03-15','2019-03-10','2019-03-11',201,'LIMITED1','PC',0.16368),
    ('2020-03-09','2020-03-15','2019-03-10','2019-03-11',201,'LIMITED1','RED',0.16368),
    ('2020-03-09','2020-03-15','2019-03-10','2019-03-11',201,'LIMITED1','W',0.24101),
    ('2019-03-11','2019-03-17','2018-03-11','2018-03-12',201,'LIMITED1','L',12.54497),
    ('2019-03-11','2019-03-17','2018-03-11','2018-03-12',201,'LIMITED1','M',0.10406),
    ('2019-03-11','2019-03-17','2018-03-11','2018-03-12',201,'LIMITED1','PC',0.16368),
    ('2019-03-11','2019-03-17','2018-03-11','2018-03-12',201,'LIMITED1','RED',0.24552),
    ('2019-03-11','2019-03-17','2018-03-11','2018-03-12',201,'LIMITED1','W',0.18425),

    ('2019-03-11','2019-03-17','2018-03-11','2018-03-12',201,'LIMITED1','FAN',12.12);

    the result should be below:

    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,L,18.32616,12.54497
    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,LAND,0.2556,0
    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,PC,0.16368,
    0.16368
    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,RED,0.16368,
    0.24552
    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,W,0.24101,
    0.18425

    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,FAN,0,12.12


    zj

    Thursday, May 7, 2020 5:38 PM
  • Use

    Select IsNull(cy.StartDate, ly.StartDate) As StartDate,
      IsNull(cy.EndDate, ly.EndDate) As EndDate,
      IsNull(cy.LY_STARTING_DATE, ly.LY_STARTING_DATE) As LY_STARTING_DATE,
      IsNull(cy.LY_STARTING_DATE_Tss, ly.LY_STARTING_DATE_Tss) As LY_STARTING_DATE_Tss,
      IsNull(cy.Store_Number, ly.Store_Number) As Store_Number,
      IsNull(cy.Manufacturer, ly.Manufacturer) As Manufacturer, 
      IsNull(cy.Brand, ly.Brand) As Brand,
      IsNull(cy.sales, 0) As sales,
      IsNull(ly.sales, 0) As ly_sales
    From @Sample cy
    Full Join @Sample ly On ly.StartDate <= DateAdd(year, -1, cy.EndDate) And ly.EndDate >= DateAdd(year, -1, cy.EndDate)
      And cy.Store_Number = ly.Store_Number And cy.Manufacturer = ly.Manufacturer And cy.Brand = ly.Brand
    Where Year(cy.StartDate) = Year(CURRENT_TIMESTAMP) Or Year(ly.StartDate) = Year(CURRENT_TIMESTAMP) - 1;
    

    I see that you are not showing trailing zeros.  The above code will always show 5 decimal places (that is, it will show 12.12 as 12.00000).  If you want to remove trailing zeros, you can use the FORMAT function or you can do it in the front end instead of doing it in T-SQL.  The front end is generally a better idea, But if you want to do it in T-SQL, then use

    Select IsNull(cy.StartDate, ly.StartDate) As StartDate,
      IsNull(cy.EndDate, ly.EndDate) As EndDate,
      IsNull(cy.LY_STARTING_DATE, ly.LY_STARTING_DATE) As LY_STARTING_DATE,
      IsNull(cy.LY_STARTING_DATE_Tss, ly.LY_STARTING_DATE_Tss) As LY_STARTING_DATE_Tss,
      IsNull(cy.Store_Number, ly.Store_Number) As Store_Number,
      IsNull(cy.Manufacturer, ly.Manufacturer) As Manufacturer, 
      IsNull(cy.Brand, ly.Brand) As Brand,
      Format(IsNull(cy.sales, 0), '0.#####') As sales,
      Format(IsNull(ly.sales, 0), '0.#####') As ly_sales
    From @Sample cy
    Full Join @Sample ly On ly.StartDate <= DateAdd(year, -1, cy.EndDate) And ly.EndDate >= DateAdd(year, -1, cy.EndDate)
      And cy.Store_Number = ly.Store_Number And cy.Manufacturer = ly.Manufacturer And cy.Brand = ly.Brand
    Where Year(cy.StartDate) = Year(CURRENT_TIMESTAMP) Or Year(ly.StartDate) = Year(CURRENT_TIMESTAMP) - 1;

    Tom

    Thursday, May 7, 2020 7:57 PM
  • Hi Tom

    Thanks for your help.

    Your query doesn't give the correct result for Brand=FAN and Brand=M. I need the below result

    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,L,18.32616,12.54497
    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,LAND,0.2556,0
    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,PC,0.16368,
    0.16368
    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,RED,0.16368,
    0.24552
    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,W,0.24101,
    0.18425

    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,M,0,0.10406

    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,FAN,0,12.12

    while your query result is 

    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,L,18.32616,12.54497
    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,LAND,0.2556,0
    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,PC,0.16368,
    0.16368
    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,RED,0.16368,
    0.24552
    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,W,0.24101,
    0.18425

    2019-03-11,2019-03-17,2018-03-11,2018-03-12,201,LIMITED1,FAN,12.12


    zj



    • Edited by ZaraJ Friday, May 8, 2020 3:21 PM
    Friday, May 8, 2020 1:40 PM
  • SELECT s1.StartDate, s1.EndDate, s1.LY_STARTING_DATE, s1.LY_STARTING_DATE_Tss, s1.Store_Number, s1.Manufacturer, s1.Brand, s1.sales, ISNULL(s2.sales, 0) AS ly_sale
    FROM @Sample AS s1
    LEFT JOIN @Sample AS s2 
    	ON s2.StartDate BETWEEN s1.LY_STARTING_DATE AND  s1.LY_STARTING_DATE_Tss
    	AND s2.Store_Number = s1.Store_Number AND s2.Manufacturer = s1.Manufacturer AND s2.Brand = s1.Brand
    WHERE YEAR(s1.StartDate) = YEAR(GETDATE())
    
    UNION ALL 
    
    SELECT s1.StartDate, s1.EndDate, s1.LY_STARTING_DATE, s1.LY_STARTING_DATE_Tss, s1.Store_Number, s1.Manufacturer, s1.Brand, 0 AS Sales, s1.Sales AS ly_sale
    FROM @Sample AS s1
    --INNER JOIN @Sample AS s2 ON s2.Store_Number = s1.Store_Number AND s2.Manufacturer = s1.Manufacturer AND s2.Brand <> s1.Brand 
    WHERE YEAR(s1.StartDate) < YEAR(GETDATE())
    AND s1.Brand NOT IN (SELECT Brand FROM @Sample WHERE YEAR(StartDate) = YEAR(GETDATE()) AND Store_Number = s1.Store_Number AND Manufacturer = s1.Manufacturer);


    A Fan of SSIS, SSRS and SSAS

    Friday, May 8, 2020 2:28 PM
  • Hi Yuan,

    Thanks for your help.

    The result of your query is not correct for Brand=FAN andBrand=M

    I'm looking for the Brand from last year that had sales but this year it doesn't have sales., so I need to show it this year's sale is zero and the sales for last year is 12.12., please see the raw for Brand=FAN


    I'm looking for the below result, Please see the row for Brand=FAN or Brand=M, I need it

    StartDate,EndDate,LY_STARTING_DATE,LY_STARTING_DATE_Tss,Store_Number,Manufacturer,Brand,ThisYear_sales,LastYear_Sales

    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,L,18.32616,12.54497
    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,LAND,0.2556,0
    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,PC,0.16368,
    0.16368
    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,RED,0.16368,
    0.24552
    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,W,0.24101,
    0.18425

    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,M,0,0.10406

    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,FAN,0,12.12

    but your query is showing the below, it's not correct

    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,L,18.32616,12.54497
    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,LAND,0.2556,0
    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,PC,0.16368,
    0.16368
    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,RED,0.16368,
    0.24552
    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,W,0.24101,
    0.18425

    2019-03-11,2019-03-17,2018-03-11,2018-03-12,201,LIMITED1,FAN,12.12


    zj





    • Edited by ZaraJ Friday, May 8, 2020 3:41 PM
    Friday, May 8, 2020 2:57 PM
  • Hi Tom

    Thanks for your help.

    Your query doesn't give the correct result for Brand=FAN and Brand=M. I need the below result

    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,FAN,0,12.12

    while your query result is 

    2019-03-11,2019-03-17,2018-03-11,2018-03-12,201,LIMITED1,FAN,12.12


    zj

    So the question is how do decide what dates to show for StartDate and EndDate columns when there is no sales for the current year.  In this case you want 2019-03-09 and 2019-03-15.  What is you rule you want to use to get those dates?

    Tom

    Friday, May 8, 2020 3:41 PM
  • Hi Tom,

    Thank you for your attention.

    You are right, but I'm looking for the Brand from last year that had sales but this year it doesn't have sales., so I need to show it this year's sale is zero and the sales for last year is 12.12., please see the raw for Brand=FAN

    this result that I'm looking for them 

    StartDate,EndDate,LY_STARTING_DATE,LY_STARTING_DATE_Tss,Store_Number,Manufacturer,Brand,ThisYear_sales,LastYear_Sales

    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,M,0,0.10406

    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,FAN,0,12.12 

    It has  LY_STARTING_DATE_Tss=2019-03-11 and the [StartDate] column from last year has the same  date[2019-03-11], it's the only relation that we can make it for getting the correct result, I think


    <g class="gr_ gr_33 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" data-gr-id="33" id="33">zj</g>




    • Edited by ZaraJ Friday, May 8, 2020 3:55 PM
    Friday, May 8, 2020 3:53 PM
  • Here is the screenshot of the output from my query:


    A Fan of SSIS, SSRS and SSAS


    Friday, May 8, 2020 3:59 PM
  • yes, I know Brand=Fan has the startDate=2019-03-11, but I need the StartDate2020-03-09, EndDate=2020-03-15, sale=0, ly_Sales=12.12  for Brand=Fan. because I want to compare it from this year week with the brand from last year week and show it in this year even this year doesn't have that brand, but it had sales last year week

    please see the below result

    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,M,0,0.10406

    2020-03-09,2020-03-15,2019-03-10,2019-03-11,201,LIMITED1,FAN,0,12.12


    <g class="gr_ gr_145 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" data-gr-id="145" id="145">zj</g>



    • Edited by ZaraJ Friday, May 8, 2020 4:11 PM
    Friday, May 8, 2020 4:06 PM
  • Then maybe

    ;With cte As
    (Select Top 1 cy.StartDate, cy.EndDate, cy.LY_STARTING_DATE, cy.LY_STARTING_DATE_Tss
    From @Sample cy
    Inner Join @Sample ly On cy.LY_STARTING_DATE_Tss = ly.StartDate
    Order By cy.StartDate)
    Select IsNull(cy.StartDate, cte.StartDate) As StartDate,
      IsNull(cy.EndDate, cte.EndDate) As EndDate,
      IsNull(cy.LY_STARTING_DATE, cte.LY_STARTING_DATE) As LY_STARTING_DATE,
      IsNull(cy.LY_STARTING_DATE_Tss, cte.LY_STARTING_DATE_Tss) As LY_STARTING_DATE_Tss,
      IsNull(cy.Store_Number, ly.Store_Number) As Store_Number,
      IsNull(cy.Manufacturer, ly.Manufacturer) As Manufacturer, 
      IsNull(cy.Brand, ly.Brand) As Brand,
      IsNull(cy.sales, 0) As sales,
      IsNull(ly.sales, 0) As ly_sales
    From @Sample cy
    Full Join @Sample ly On ly.StartDate <= DateAdd(year, -1, cy.EndDate) And ly.EndDate >= DateAdd(year, -1, cy.EndDate)
      And cy.Store_Number = ly.Store_Number And cy.Manufacturer = ly.Manufacturer And cy.Brand = ly.Brand
    Left Join cte On cte.LY_STARTING_DATE_Tss = ly.StartDate
    Where Year(cy.StartDate) = Year(CURRENT_TIMESTAMP) Or Year(ly.StartDate) = Year(CURRENT_TIMESTAMP) - 1;
    Tom

    • Marked as answer by ZaraJ Wednesday, May 13, 2020 2:41 PM
    Friday, May 8, 2020 5:24 PM
  • DECLARE @StartDate date,
    		@EndDate date,
    		@LY_STARTING_DATE date,
    		@LY_STARTING_DATE_Tss date;
    
    SELECT TOP 1 @StartDate = StartDate, @EndDate = EndDate, @LY_STARTING_DATE = LY_STARTING_DATE, @LY_STARTING_DATE_Tss = LY_STARTING_DATE_Tss
    FROM @Sample
    WHERE YEAR(StartDate) = YEAR(GETDATE());
    
    WITH CTE_Current_Year AS (
    	SELECT StartDate, EndDate, LY_STARTING_DATE, LY_STARTING_DATE_Tss, Store_Number, Manufacturer, Brand, Sales
    	FROM @Sample
    	WHERE YEAR(StartDate) = YEAR(GETDATE())
    	UNION ALL
    	SELECT @StartDate AS StartDate, @EndDate AS EndDate, @LY_STARTING_DATE AS LY_STARTING_DATE, @LY_STARTING_DATE_Tss AS LY_STARTING_DATE_Tss, s.Store_Number, s.Manufacturer, s.Brand, 0 AS Sales
    	FROM @Sample AS s
    	WHERE YEAR(s.StartDate) < YEAR(GETDATE())
    	AND s.Brand NOT IN (SELECT Brand FROM @Sample WHERE YEAR(StartDate) = YEAR(GETDATE()) AND Store_Number = s.Store_Number AND Manufacturer = s.Manufacturer)
    ),
    CTE_Previous_Year AS (
    	SELECT StartDate, EndDate, LY_STARTING_DATE, LY_STARTING_DATE_Tss, Store_Number, Manufacturer, Brand, Sales
    	FROM @Sample
    	WHERE YEAR(StartDate) < YEAR(GETDATE())
    )
    
    SELECT c.*, ISNULL(p.Sales, 0) AS ly_Sales
    FROM CTE_Current_Year AS c
    LEFT JOIN CTE_Previous_Year AS p ON c.Store_Number = p.Store_Number AND p.Manufacturer = p.Manufacturer AND c.Brand = p.Brand;

    Output:


    A Fan of SSIS, SSRS and SSAS


    Friday, May 8, 2020 6:44 PM
  • Hi, Tom

    Thank you so much for your help.

    your query works for short data.

    I have large data from 2018 until now. while I ran the query it took 2 days and I stopped it. I set a filter for specific Store_Number and date, it took 17 hours even.

    Could you please advise me how can I handle it?

    I think the query works as a matrix that's why it takes time and doesn't work for me :(




    <g class="gr_ gr_24 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" data-gr-id="24" id="24">zj</g>



    • Edited by ZaraJ Wednesday, May 13, 2020 2:56 PM
    Wednesday, May 13, 2020 2:49 PM