Remove From My Forums

getting last year data

Question
0

Sign in to vote

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

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

0

Sign in to vote
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

0

Sign in to vote
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
0

Sign in to vote
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
0

Sign in to vote
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
0

Sign in to vote
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
- Proposed as answer by Rachel_WangMicrosoft contingent staff Wednesday, May 6, 2020 7:41 AM
Tuesday, May 5, 2020 8:45 PM
0

Sign in to vote

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

0

Sign in to vote
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

0

Sign in to vote
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
0

Sign in to vote

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
0

Sign in to vote
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
0

Sign in to vote
Here is the screenshot of the output from my query:

A Fan of SSIS, SSRS and SSAS
- Edited by Guoxiong Yuan Friday, May 8, 2020 4:02 PM
Friday, May 8, 2020 3:59 PM
0

Sign in to vote
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

Edited by Guoxiong Yuan Friday, May 8, 2020 6:46 PM

Friday, May 8, 2020 6:44 PM

0

Sign in to vote
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

Answered by:

getting last year data

Question

Answers

All replies