Combine two tables based on FY year

• Papil

  SSCommitted

  Points: 1983

  More actions

  November 7, 2018 at 10:49 am

  #409297

  My sample data looks like below queries . mytable1 has data for all products for all quarters currently. I have shown only 2019 fiscal year data. Mytable2 has forecast data for all quarters for 2019. my Output table(mytableoutput) should combine and Q1,Q2,Q3 data from mytable1 and Q4 data from mytable2 -because the Financial year has not ended yet. FY starts from Feb 2018 till Jan 2019. 

  Once the financial year ends and Q1 starts then combined output will show Q1 from mytable1 and Q2,Q3,Q4 from mytable2. And so on .After Q2 starts of FY 2020 then we should get Q1,Q2 from mytable1 and Q2,Q3 from mytable2. please help.thanks in advance

  CREATE TABLE mytable1(
 Product VARCHAR(1) NOT NULL
,Qtr  VARCHAR(7) NOT NULL
,Value1 INTEGER NOT NULL
,Value2 INTEGER
);
INSERT INTO mytable1(Product,Qtr,Value1,Value2) VALUES ('A','Q3 2019',878,NULL);
INSERT INTO mytable1(Product,Qtr,Value1,Value2) VALUES ('A','Q1 2019',767,584);
INSERT INTO mytable1(Product,Qtr,Value1,Value2) VALUES ('A','Q2 2019',5656,NULL);
INSERT INTO mytable1(Product,Qtr,Value1,Value2) VALUES ('A','Q4 2019',55,NULL);
INSERT INTO mytable1(Product,Qtr,Value1,Value2) VALUES ('B','Q1 2019',7676,1234);
INSERT INTO mytable1(Product,Qtr,Value1,Value2) VALUES ('B','Q3 2019',8545,4088);
INSERT INTO mytable1(Product,Qtr,Value1,Value2) VALUES ('A','Q4 2019',434,565);
INSERT INTO mytable1(Product,Qtr,Value1,Value2) VALUES ('B','Q2 2019',4,NULL);
INSERT INTO mytable1(Product,Qtr,Value1,Value2) VALUES ('C','Q3 2019',5454,4088);
INSERT INTO mytable1(Product,Qtr,Value1,Value2) VALUES ('C','Q2 2019',6767,NULL);

  CREATE TABLE mytable2(
 Product VARCHAR(1) NOT NULL
,Qtr  VARCHAR(7) NOT NULL
,Value1 INTEGER NOT NULL
,Value2 INTEGER
);
INSERT INTO mytable2(Product,Qtr,Value1,Value2) VALUES ('A','Q3 2019',54,NULL);
INSERT INTO mytable2(Product,Qtr,Value1,Value2) VALUES ('A','Q1 2019',43,NULL);
INSERT INTO mytable2(Product,Qtr,Value1,Value2) VALUES ('A','Q2 2019',323,NULL);
INSERT INTO mytable2(Product,Qtr,Value1,Value2) VALUES ('A','Q4 2019',545,666);
INSERT INTO mytable2(Product,Qtr,Value1,Value2) VALUES ('B','Q3 2019',545,4234);
INSERT INTO mytable2(Product,Qtr,Value1,Value2) VALUES ('B','Q4 2019',7677,2920);
INSERT INTO mytable2(Product,Qtr,Value1,Value2) VALUES ('B','Q1 2019',878,NULL);
INSERT INTO mytable2(Product,Qtr,Value1,Value2) VALUES ('C','Q1 2019',545,77);
INSERT INTO mytable2(Product,Qtr,Value1,Value2) VALUES ('C','Q4 2019',44,56);

  CREATE TABLE mytableoutput(
 Product  VARCHAR(1) NOT NULL
,Qtr   VARCHAR(7) NOT NULL
,Value1  INTEGER NOT NULL
,Value2  INTEGER
);
INSERT INTO mytableoutput(Product,Qtr,Value1,Value2) VALUES ('A','Q3 2019',878,NULL);
INSERT INTO mytableoutput(Product,Qtr,Value1,Value2) VALUES ('A','Q1 2019',767,584);
INSERT INTO mytableoutput(Product,Qtr,Value1,Value2) VALUES ('A','Q2 2019',5656,NULL);
INSERT INTO mytableoutput(Product,Qtr,Value1,Value2) VALUES ('B','Q1 2019',7676,1234);
INSERT INTO mytableoutput(Product,Qtr,Value1,Value2) VALUES ('B','Q3 2019',8545,4088);
INSERT INTO mytableoutput(Product,Qtr,Value1,Value2) VALUES ('B','Q2 2019',4,NULL);
INSERT INTO mytableoutput(Product,Qtr,Value1,Value2) VALUES ('C','Q3 2019',5454,4088);
INSERT INTO mytableoutput(Product,Qtr,Value1,Value2) VALUES ('C','Q2 2019',6767,NULL);
INSERT INTO mytableoutput(Product,Qtr,Value1,Value2) VALUES ('A','Q4 2019',545,666);
INSERT INTO mytableoutput(Product,Qtr,Value1,Value2) VALUES ('B','Q4 2019',7677,2920);
INSERT INTO mytableoutput(Product,Qtr,Value1,Value2) VALUES ('C','Q4 2019',44,56);


• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 7, 2018 at 11:20 am

  #2012728

  Now, show use what you have tried to solve your problem. 
  Also, the following is a much better way to post the DDL, sample data, and expected results, which I am thankful for you have done.

  
if object_id('[dbo].[mytable1]','U') is not null
drop table [dbo].[mytable1];

  create table [dbo].[mytable1]
(
  [Product] varchar(1) not null
, [Qtr] varchar(7) not null
, [Value1] integer not null
, [Value2] integer
);

  insert into [dbo].[mytable1]
(
  [Product]
, [Qtr]
, [Value1]
, [Value2]
)
values
  ('A', 'Q3 2019', 878, null)
, ('A', 'Q1 2019', 767, 584)
, ('A', 'Q2 2019', 5656, null)
, ('A', 'Q4 2019', 55, null)
, ('B', 'Q1 2019', 7676, 1234)
, ('B', 'Q3 2019', 8545, 4088)
, ('A', 'Q4 2019', 434, 565)
, ('B', 'Q2 2019', 4, null)
, ('C', 'Q3 2019', 5454, 4088)
, ('C', 'Q2 2019', 6767, null);
go

  create table [dbo].[mytable2]
(
  [Product] varchar(1) not null
, [Qtr] varchar(7) not null
, [Value1] integer not null
, [Value2] integer
);

  insert into [dbo].[mytable2]
(
  [Product]
, [Qtr]
, [Value1]
, [Value2]
)
values
  ('A', 'Q3 2019', 54, null)
, ('A', 'Q1 2019', 43, null)
, ('A', 'Q2 2019', 323, null)
, ('A', 'Q4 2019', 545, 666)
, ('B', 'Q3 2019', 545, 4234)
, ('B', 'Q4 2019', 7677, 2920)
, ('B', 'Q1 2019', 878, null)
, ('C', 'Q1 2019', 545, 77)
, ('C', 'Q4 2019', 44, 56);
go
if object_id('[dbo].[mytableoutput]','U') is not null
drop table [dbo].[mytableoutput];

  create table [dbo].[mytableoutput]
(
  [Product] varchar(1) not null
, [Qtr] varchar(7) not null
, [Value1] integer not null
, [Value2] integer
);
insert into [dbo].[mytableoutput]
(
  [Product]
, [Qtr]
, [Value1]
, [Value2]
)
values
('A', 'Q3 2019', 878, null)
, ('A', 'Q1 2019', 767, 584)
, ('A', 'Q2 2019', 5656, null)
, ('B', 'Q1 2019', 7676, 1234)
, ('B', 'Q3 2019', 8545, 4088)
, ('B', 'Q2 2019', 4, null)
, ('C', 'Q3 2019', 5454, 4088)
, ('C', 'Q2 2019', 6767, null)
, ('A', 'Q4 2019', 545, 666)
, ('B', 'Q4 2019', 7677, 2920)
, ('C', 'Q4 2019', 44, 56);

  go
select * from [dbo].[mytable1]
order by [Qtr];
go
select * from [dbo].[mytable2]
order by [Qtr];
go
select * from [dbo].[mytableoutput]
order by [Qtr];
go

  -- Clean up the Sandbox database
if object_id('[dbo].[mytable1]','U') is not null
drop table [dbo].[mytable1];

  if object_id('[dbo].[mytable2]','U') is not null
drop table [dbo].[mytable2];

  if object_id('[dbo].[mytableoutput]','U') is not null
drop table [dbo].[mytableoutput];


• Papil

  SSCommitted

  Points: 1983

  More actions

  November 7, 2018 at 11:29 am

  #2012729

  Lynn Pettis - Wednesday, November 7, 2018 11:20 AM

  Now, show use what you have tried to solve your problem. 
  Also, the following is a much better way to post the DDL, sample data, and expected results, which I am thankful for you have done.

  
if object_id('[dbo].[mytable1]','U') is not null
drop table [dbo].[mytable1];

  create table [dbo].[mytable1]
(
  [Product] varchar(1) not null
, [Qtr] varchar(7) not null
, [Value1] integer not null
, [Value2] integer
);

  insert into [dbo].[mytable1]
(
  [Product]
, [Qtr]
, [Value1]
, [Value2]
)
values
  ('A', 'Q3 2019', 878, null)
, ('A', 'Q1 2019', 767, 584)
, ('A', 'Q2 2019', 5656, null)
, ('A', 'Q4 2019', 55, null)
, ('B', 'Q1 2019', 7676, 1234)
, ('B', 'Q3 2019', 8545, 4088)
, ('A', 'Q4 2019', 434, 565)
, ('B', 'Q2 2019', 4, null)
, ('C', 'Q3 2019', 5454, 4088)
, ('C', 'Q2 2019', 6767, null);
go

  create table [dbo].[mytable2]
(
  [Product] varchar(1) not null
, [Qtr] varchar(7) not null
, [Value1] integer not null
, [Value2] integer
);

  insert into [dbo].[mytable2]
(
  [Product]
, [Qtr]
, [Value1]
, [Value2]
)
values
  ('A', 'Q3 2019', 54, null)
, ('A', 'Q1 2019', 43, null)
, ('A', 'Q2 2019', 323, null)
, ('A', 'Q4 2019', 545, 666)
, ('B', 'Q3 2019', 545, 4234)
, ('B', 'Q4 2019', 7677, 2920)
, ('B', 'Q1 2019', 878, null)
, ('C', 'Q1 2019', 545, 77)
, ('C', 'Q4 2019', 44, 56);
go
if object_id('[dbo].[mytableoutput]','U') is not null
drop table [dbo].[mytableoutput];

  create table [dbo].[mytableoutput]
(
  [Product] varchar(1) not null
, [Qtr] varchar(7) not null
, [Value1] integer not null
, [Value2] integer
);
insert into [dbo].[mytableoutput]
(
  [Product]
, [Qtr]
, [Value1]
, [Value2]
)
values
('A', 'Q3 2019', 878, null)
, ('A', 'Q1 2019', 767, 584)
, ('A', 'Q2 2019', 5656, null)
, ('B', 'Q1 2019', 7676, 1234)
, ('B', 'Q3 2019', 8545, 4088)
, ('B', 'Q2 2019', 4, null)
, ('C', 'Q3 2019', 5454, 4088)
, ('C', 'Q2 2019', 6767, null)
, ('A', 'Q4 2019', 545, 666)
, ('B', 'Q4 2019', 7677, 2920)
, ('C', 'Q4 2019', 44, 56);

  go
select * from [dbo].[mytable1]
order by [Qtr];
go
select * from [dbo].[mytable2]
order by [Qtr];
go
select * from [dbo].[mytableoutput]
order by [Qtr];
go

  -- Clean up the Sandbox database
if object_id('[dbo].[mytable1]','U') is not null
drop table [dbo].[mytable1];

  if object_id('[dbo].[mytable2]','U') is not null
drop table [dbo].[mytable2];

  if object_id('[dbo].[mytableoutput]','U') is not null
drop table [dbo].[mytableoutput];


  Thanks for replying. Actually mytable1 and mytable2 has 2019,2018 etc FY data. But i want to dynamically take the 2019 data because currently 2019 FY is running. And Q4 should come from Mytable2 only for now because Q4 has just started. Once the FY 2019 ends in Jan end. Then the query should show Q1 from mytable1 and Q2,Q3,Q4 from Mytable2. Is there any dynamic way to do this?

• pietlinden

  SSC Guru

  Points: 63428

  More actions

  November 7, 2018 at 11:31 am

  #2012730

  Union and then query? What did you try?

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 7, 2018 at 11:48 am

  #2012733

  Now, show use what you have tried to solve your problem. 
  Also, the following is a much better way to post the DDL, sample data, and expected results, which I am thankful for you have done.

  
if object_id('[dbo].[mytable1]','U') is not null
drop table [dbo].[mytable1];

  create table [dbo].[mytable1]
(
  [Product] varchar(1) not null
, [Qtr] varchar(7) not null
, [Value1] integer not null
, [Value2] integer
);

  insert into [dbo].[mytable1]
(
  [Product]
, [Qtr]
, [Value1]
, [Value2]
)
values
  ('A', 'Q3 2019', 878, null)
, ('A', 'Q1 2019', 767, 584)
, ('A', 'Q2 2019', 5656, null)
, ('A', 'Q4 2019', 55, null)
, ('B', 'Q1 2019', 7676, 1234)
, ('B', 'Q3 2019', 8545, 4088)
, ('A', 'Q4 2019', 434, 565)
, ('B', 'Q2 2019', 4, null)
, ('C', 'Q3 2019', 5454, 4088)
, ('C', 'Q2 2019', 6767, null);
go

  create table [dbo].[mytable2]
(
  [Product] varchar(1) not null
, [Qtr] varchar(7) not null
, [Value1] integer not null
, [Value2] integer
);

  insert into [dbo].[mytable2]
(
  [Product]
, [Qtr]
, [Value1]
, [Value2]
)
values
  ('A', 'Q3 2019', 54, null)
, ('A', 'Q1 2019', 43, null)
, ('A', 'Q2 2019', 323, null)
, ('A', 'Q4 2019', 545, 666)
, ('B', 'Q3 2019', 545, 4234)
, ('B', 'Q4 2019', 7677, 2920)
, ('B', 'Q1 2019', 878, null)
, ('C', 'Q1 2019', 545, 77)
, ('C', 'Q4 2019', 44, 56);
go
if object_id('[dbo].[mytableoutput]','U') is not null
drop table [dbo].[mytableoutput];

  create table [dbo].[mytableoutput]
(
  [Product] varchar(1) not null
, [Qtr] varchar(7) not null
, [Value1] integer not null
, [Value2] integer
);
insert into [dbo].[mytableoutput]
(
  [Product]
, [Qtr]
, [Value1]
, [Value2]
)
values
('A', 'Q3 2019', 878, null)
, ('A', 'Q1 2019', 767, 584)
, ('A', 'Q2 2019', 5656, null)
, ('B', 'Q1 2019', 7676, 1234)
, ('B', 'Q3 2019', 8545, 4088)
, ('B', 'Q2 2019', 4, null)
, ('C', 'Q3 2019', 5454, 4088)
, ('C', 'Q2 2019', 6767, null)
, ('A', 'Q4 2019', 545, 666)
, ('B', 'Q4 2019', 7677, 2920)
, ('C', 'Q4 2019', 44, 56);

  go
select * from [dbo].[mytable1]
order by [Qtr];
go
select * from [dbo].[mytable2]
order by [Qtr];
go
select * from [dbo].[mytableoutput]
order by [Qtr];
go

  -- Clean up the Sandbox database
if object_id('[dbo].[mytable1]','U') is not null
drop table [dbo].[mytable1];

  if object_id('[dbo].[mytable2]','U') is not null
drop table [dbo].[mytable2];

  if object_id('[dbo].[mytableoutput]','U') is not null
drop table [dbo].[mytableoutput];


  In mytable1 you have 2 entries for Product A in Q4 2019, is this correct?

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 7, 2018 at 11:49 am

  #2012734

  Papil - Wednesday, November 7, 2018 11:29 AM

  Lynn Pettis - Wednesday, November 7, 2018 11:20 AM

  Now, show use what you have tried to solve your problem. 
  Also, the following is a much better way to post the DDL, sample data, and expected results, which I am thankful for you have done.

  
if object_id('[dbo].[mytable1]','U') is not null
drop table [dbo].[mytable1];

  create table [dbo].[mytable1]
(
  [Product] varchar(1) not null
, [Qtr] varchar(7) not null
, [Value1] integer not null
, [Value2] integer
);

  insert into [dbo].[mytable1]
(
  [Product]
, [Qtr]
, [Value1]
, [Value2]
)
values
  ('A', 'Q3 2019', 878, null)
, ('A', 'Q1 2019', 767, 584)
, ('A', 'Q2 2019', 5656, null)
, ('A', 'Q4 2019', 55, null)
, ('B', 'Q1 2019', 7676, 1234)
, ('B', 'Q3 2019', 8545, 4088)
, ('A', 'Q4 2019', 434, 565)
, ('B', 'Q2 2019', 4, null)
, ('C', 'Q3 2019', 5454, 4088)
, ('C', 'Q2 2019', 6767, null);
go

  create table [dbo].[mytable2]
(
  [Product] varchar(1) not null
, [Qtr] varchar(7) not null
, [Value1] integer not null
, [Value2] integer
);

  insert into [dbo].[mytable2]
(
  [Product]
, [Qtr]
, [Value1]
, [Value2]
)
values
  ('A', 'Q3 2019', 54, null)
, ('A', 'Q1 2019', 43, null)
, ('A', 'Q2 2019', 323, null)
, ('A', 'Q4 2019', 545, 666)
, ('B', 'Q3 2019', 545, 4234)
, ('B', 'Q4 2019', 7677, 2920)
, ('B', 'Q1 2019', 878, null)
, ('C', 'Q1 2019', 545, 77)
, ('C', 'Q4 2019', 44, 56);
go
if object_id('[dbo].[mytableoutput]','U') is not null
drop table [dbo].[mytableoutput];

  create table [dbo].[mytableoutput]
(
  [Product] varchar(1) not null
, [Qtr] varchar(7) not null
, [Value1] integer not null
, [Value2] integer
);
insert into [dbo].[mytableoutput]
(
  [Product]
, [Qtr]
, [Value1]
, [Value2]
)
values
('A', 'Q3 2019', 878, null)
, ('A', 'Q1 2019', 767, 584)
, ('A', 'Q2 2019', 5656, null)
, ('B', 'Q1 2019', 7676, 1234)
, ('B', 'Q3 2019', 8545, 4088)
, ('B', 'Q2 2019', 4, null)
, ('C', 'Q3 2019', 5454, 4088)
, ('C', 'Q2 2019', 6767, null)
, ('A', 'Q4 2019', 545, 666)
, ('B', 'Q4 2019', 7677, 2920)
, ('C', 'Q4 2019', 44, 56);

  go
select * from [dbo].[mytable1]
order by [Qtr];
go
select * from [dbo].[mytable2]
order by [Qtr];
go
select * from [dbo].[mytableoutput]
order by [Qtr];
go

  -- Clean up the Sandbox database
if object_id('[dbo].[mytable1]','U') is not null
drop table [dbo].[mytable1];

  if object_id('[dbo].[mytable2]','U') is not null
drop table [dbo].[mytable2];

  if object_id('[dbo].[mytableoutput]','U') is not null
drop table [dbo].[mytableoutput];


  Thanks for replying. Actually mytable1 and mytable2 has 2019,2018 etc FY data. But i want to dynamically take the 2019 data because currently 2019 FY is running. And Q4 should come from Mytable2 only for now because Q4 has just started. Once the FY 2019 ends in Jan end. Then the query should show Q1 from mytable1 and Q2,Q3,Q4 from Mytable2. Is there any dynamic way to do this?

  So, once more with feeling, what have you tried?  Do you actually expect us to just do your work for you?

  We are volunteers, not paid employees.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 7, 2018 at 11:57 am

  #2012736

  Lynn Pettis - Wednesday, November 7, 2018 11:48 AM

  Now, show use what you have tried to solve your problem. 
  Also, the following is a much better way to post the DDL, sample data, and expected results, which I am thankful for you have done.

  
if object_id('[dbo].[mytable1]','U') is not null
drop table [dbo].[mytable1];

  create table [dbo].[mytable1]
(
  [Product] varchar(1) not null
, [Qtr] varchar(7) not null
, [Value1] integer not null
, [Value2] integer
);

  insert into [dbo].[mytable1]
(
  [Product]
, [Qtr]
, [Value1]
, [Value2]
)
values
  ('A', 'Q3 2019', 878, null)
, ('A', 'Q1 2019', 767, 584)
, ('A', 'Q2 2019', 5656, null)
, ('A', 'Q4 2019', 55, null)
, ('B', 'Q1 2019', 7676, 1234)
, ('B', 'Q3 2019', 8545, 4088)
, ('A', 'Q4 2019', 434, 565)
, ('B', 'Q2 2019', 4, null)
, ('C', 'Q3 2019', 5454, 4088)
, ('C', 'Q2 2019', 6767, null);
go

  create table [dbo].[mytable2]
(
  [Product] varchar(1) not null
, [Qtr] varchar(7) not null
, [Value1] integer not null
, [Value2] integer
);

  insert into [dbo].[mytable2]
(
  [Product]
, [Qtr]
, [Value1]
, [Value2]
)
values
  ('A', 'Q3 2019', 54, null)
, ('A', 'Q1 2019', 43, null)
, ('A', 'Q2 2019', 323, null)
, ('A', 'Q4 2019', 545, 666)
, ('B', 'Q3 2019', 545, 4234)
, ('B', 'Q4 2019', 7677, 2920)
, ('B', 'Q1 2019', 878, null)
, ('C', 'Q1 2019', 545, 77)
, ('C', 'Q4 2019', 44, 56);
go
if object_id('[dbo].[mytableoutput]','U') is not null
drop table [dbo].[mytableoutput];

  create table [dbo].[mytableoutput]
(
  [Product] varchar(1) not null
, [Qtr] varchar(7) not null
, [Value1] integer not null
, [Value2] integer
);
insert into [dbo].[mytableoutput]
(
  [Product]
, [Qtr]
, [Value1]
, [Value2]
)
values
('A', 'Q3 2019', 878, null)
, ('A', 'Q1 2019', 767, 584)
, ('A', 'Q2 2019', 5656, null)
, ('B', 'Q1 2019', 7676, 1234)
, ('B', 'Q3 2019', 8545, 4088)
, ('B', 'Q2 2019', 4, null)
, ('C', 'Q3 2019', 5454, 4088)
, ('C', 'Q2 2019', 6767, null)
, ('A', 'Q4 2019', 545, 666)
, ('B', 'Q4 2019', 7677, 2920)
, ('C', 'Q4 2019', 44, 56);

  go
select * from [dbo].[mytable1]
order by [Qtr];
go
select * from [dbo].[mytable2]
order by [Qtr];
go
select * from [dbo].[mytableoutput]
order by [Qtr];
go

  -- Clean up the Sandbox database
if object_id('[dbo].[mytable1]','U') is not null
drop table [dbo].[mytable1];

  if object_id('[dbo].[mytable2]','U') is not null
drop table [dbo].[mytable2];

  if object_id('[dbo].[mytableoutput]','U') is not null
drop table [dbo].[mytableoutput];


  In mytable1 you have 2 entries for Product A in Q4 2019, is this correct?

  You also do not have an entry for Product C Q1 2019 in mytable1.  Is that correct and if so do you want what is in mytable2 for that product?

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 7, 2018 at 12:00 pm

  #2012737

  Lynn Pettis - Wednesday, November 7, 2018 11:57 AM

  Lynn Pettis - Wednesday, November 7, 2018 11:48 AM

  Now, show use what you have tried to solve your problem. 
  Also, the following is a much better way to post the DDL, sample data, and expected results, which I am thankful for you have done.

  
if object_id('[dbo].[mytable1]','U') is not null
drop table [dbo].[mytable1];

  create table [dbo].[mytable1]
(
  [Product] varchar(1) not null
, [Qtr] varchar(7) not null
, [Value1] integer not null
, [Value2] integer
);

  insert into [dbo].[mytable1]
(
  [Product]
, [Qtr]
, [Value1]
, [Value2]
)
values
  ('A', 'Q3 2019', 878, null)
, ('A', 'Q1 2019', 767, 584)
, ('A', 'Q2 2019', 5656, null)
, ('A', 'Q4 2019', 55, null)
, ('B', 'Q1 2019', 7676, 1234)
, ('B', 'Q3 2019', 8545, 4088)
, ('A', 'Q4 2019', 434, 565)
, ('B', 'Q2 2019', 4, null)
, ('C', 'Q3 2019', 5454, 4088)
, ('C', 'Q2 2019', 6767, null);
go

  create table [dbo].[mytable2]
(
  [Product] varchar(1) not null
, [Qtr] varchar(7) not null
, [Value1] integer not null
, [Value2] integer
);

  insert into [dbo].[mytable2]
(
  [Product]
, [Qtr]
, [Value1]
, [Value2]
)
values
  ('A', 'Q3 2019', 54, null)
, ('A', 'Q1 2019', 43, null)
, ('A', 'Q2 2019', 323, null)
, ('A', 'Q4 2019', 545, 666)
, ('B', 'Q3 2019', 545, 4234)
, ('B', 'Q4 2019', 7677, 2920)
, ('B', 'Q1 2019', 878, null)
, ('C', 'Q1 2019', 545, 77)
, ('C', 'Q4 2019', 44, 56);
go
if object_id('[dbo].[mytableoutput]','U') is not null
drop table [dbo].[mytableoutput];

  create table [dbo].[mytableoutput]
(
  [Product] varchar(1) not null
, [Qtr] varchar(7) not null
, [Value1] integer not null
, [Value2] integer
);
insert into [dbo].[mytableoutput]
(
  [Product]
, [Qtr]
, [Value1]
, [Value2]
)
values
('A', 'Q3 2019', 878, null)
, ('A', 'Q1 2019', 767, 584)
, ('A', 'Q2 2019', 5656, null)
, ('B', 'Q1 2019', 7676, 1234)
, ('B', 'Q3 2019', 8545, 4088)
, ('B', 'Q2 2019', 4, null)
, ('C', 'Q3 2019', 5454, 4088)
, ('C', 'Q2 2019', 6767, null)
, ('A', 'Q4 2019', 545, 666)
, ('B', 'Q4 2019', 7677, 2920)
, ('C', 'Q4 2019', 44, 56);

  go
select * from [dbo].[mytable1]
order by [Qtr];
go
select * from [dbo].[mytable2]
order by [Qtr];
go
select * from [dbo].[mytableoutput]
order by [Qtr];
go

  -- Clean up the Sandbox database
if object_id('[dbo].[mytable1]','U') is not null
drop table [dbo].[mytable1];

  if object_id('[dbo].[mytable2]','U') is not null
drop table [dbo].[mytable2];

  if object_id('[dbo].[mytableoutput]','U') is not null
drop table [dbo].[mytableoutput];


  In mytable1 you have 2 entries for Product A in Q4 2019, is this correct?

  You also do not have an entry for Product C Q1 2019 in mytable1.  Is that correct and if so do you want what is in mytable2 for that product?

  In fact, your expected results doesn't even match your sample data.  You should revisit your sample data and expected results to be sure they match.

• Papil

  SSCommitted

  Points: 1983

  More actions

  November 7, 2018 at 1:55 pm

  #2012750

  Lynn Pettis - Wednesday, November 7, 2018 12:00 PM

  Lynn Pettis - Wednesday, November 7, 2018 11:57 AM

  Lynn Pettis - Wednesday, November 7, 2018 11:48 AM

  Now, show use what you have tried to solve your problem. 
  Also, the following is a much better way to post the DDL, sample data, and expected results, which I am thankful for you have done.

  
if object_id('[dbo].[mytable1]','U') is not null
drop table [dbo].[mytable1];

  create table [dbo].[mytable1]
(
  [Product] varchar(1) not null
, [Qtr] varchar(7) not null
, [Value1] integer not null
, [Value2] integer
);

  insert into [dbo].[mytable1]
(
  [Product]
, [Qtr]
, [Value1]
, [Value2]
)
values
  ('A', 'Q3 2019', 878, null)
, ('A', 'Q1 2019', 767, 584)
, ('A', 'Q2 2019', 5656, null)
, ('A', 'Q4 2019', 55, null)
, ('B', 'Q1 2019', 7676, 1234)
, ('B', 'Q3 2019', 8545, 4088)
, ('A', 'Q4 2019', 434, 565)
, ('B', 'Q2 2019', 4, null)
, ('C', 'Q3 2019', 5454, 4088)
, ('C', 'Q2 2019', 6767, null);
go

  create table [dbo].[mytable2]
(
  [Product] varchar(1) not null
, [Qtr] varchar(7) not null
, [Value1] integer not null
, [Value2] integer
);

  insert into [dbo].[mytable2]
(
  [Product]
, [Qtr]
, [Value1]
, [Value2]
)
values
  ('A', 'Q3 2019', 54, null)
, ('A', 'Q1 2019', 43, null)
, ('A', 'Q2 2019', 323, null)
, ('A', 'Q4 2019', 545, 666)
, ('B', 'Q3 2019', 545, 4234)
, ('B', 'Q4 2019', 7677, 2920)
, ('B', 'Q1 2019', 878, null)
, ('C', 'Q1 2019', 545, 77)
, ('C', 'Q4 2019', 44, 56);
go
if object_id('[dbo].[mytableoutput]','U') is not null
drop table [dbo].[mytableoutput];

  create table [dbo].[mytableoutput]
(
  [Product] varchar(1) not null
, [Qtr] varchar(7) not null
, [Value1] integer not null
, [Value2] integer
);
insert into [dbo].[mytableoutput]
(
  [Product]
, [Qtr]
, [Value1]
, [Value2]
)
values
('A', 'Q3 2019', 878, null)
, ('A', 'Q1 2019', 767, 584)
, ('A', 'Q2 2019', 5656, null)
, ('B', 'Q1 2019', 7676, 1234)
, ('B', 'Q3 2019', 8545, 4088)
, ('B', 'Q2 2019', 4, null)
, ('C', 'Q3 2019', 5454, 4088)
, ('C', 'Q2 2019', 6767, null)
, ('A', 'Q4 2019', 545, 666)
, ('B', 'Q4 2019', 7677, 2920)
, ('C', 'Q4 2019', 44, 56);

  go
select * from [dbo].[mytable1]
order by [Qtr];
go
select * from [dbo].[mytable2]
order by [Qtr];
go
select * from [dbo].[mytableoutput]
order by [Qtr];
go

  -- Clean up the Sandbox database
if object_id('[dbo].[mytable1]','U') is not null
drop table [dbo].[mytable1];

  if object_id('[dbo].[mytable2]','U') is not null
drop table [dbo].[mytable2];

  if object_id('[dbo].[mytableoutput]','U') is not null
drop table [dbo].[mytableoutput];


  In mytable1 you have 2 entries for Product A in Q4 2019, is this correct?

  You also do not have an entry for Product C Q1 2019 in mytable1.  Is that correct and if so do you want what is in mytable2 for that product?

  In fact, your expected results doesn't even match your sample data.  You should revisit your sample data and expected results to be sure they match.

  It is possible that there wont be entry for all quarters in table 1 but there will be alteast one quarter value present. 
  Yes Product A can have 2 rows but in my final output i am not considering the Q4 from table1 at all .I want it from table2

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 7, 2018 at 2:02 pm

  #2012752

  Papil - Wednesday, November 7, 2018 1:55 PM

  Lynn Pettis - Wednesday, November 7, 2018 12:00 PM

  Lynn Pettis - Wednesday, November 7, 2018 11:57 AM

  Lynn Pettis - Wednesday, November 7, 2018 11:48 AM

  Now, show use what you have tried to solve your problem. 
  Also, the following is a much better way to post the DDL, sample data, and expected results, which I am thankful for you have done.

  
if object_id('[dbo].[mytable1]','U') is not null
drop table [dbo].[mytable1];

  create table [dbo].[mytable1]
(
  [Product] varchar(1) not null
, [Qtr] varchar(7) not null
, [Value1] integer not null
, [Value2] integer
);

  insert into [dbo].[mytable1]
(
  [Product]
, [Qtr]
, [Value1]
, [Value2]
)
values
  ('A', 'Q3 2019', 878, null)
, ('A', 'Q1 2019', 767, 584)
, ('A', 'Q2 2019', 5656, null)
, ('A', 'Q4 2019', 55, null)
, ('B', 'Q1 2019', 7676, 1234)
, ('B', 'Q3 2019', 8545, 4088)
, ('A', 'Q4 2019', 434, 565)
, ('B', 'Q2 2019', 4, null)
, ('C', 'Q3 2019', 5454, 4088)
, ('C', 'Q2 2019', 6767, null);
go

  create table [dbo].[mytable2]
(
  [Product] varchar(1) not null
, [Qtr] varchar(7) not null
, [Value1] integer not null
, [Value2] integer
);

  insert into [dbo].[mytable2]
(
  [Product]
, [Qtr]
, [Value1]
, [Value2]
)
values
  ('A', 'Q3 2019', 54, null)
, ('A', 'Q1 2019', 43, null)
, ('A', 'Q2 2019', 323, null)
, ('A', 'Q4 2019', 545, 666)
, ('B', 'Q3 2019', 545, 4234)
, ('B', 'Q4 2019', 7677, 2920)
, ('B', 'Q1 2019', 878, null)
, ('C', 'Q1 2019', 545, 77)
, ('C', 'Q4 2019', 44, 56);
go
if object_id('[dbo].[mytableoutput]','U') is not null
drop table [dbo].[mytableoutput];

  create table [dbo].[mytableoutput]
(
  [Product] varchar(1) not null
, [Qtr] varchar(7) not null
, [Value1] integer not null
, [Value2] integer
);
insert into [dbo].[mytableoutput]
(
  [Product]
, [Qtr]
, [Value1]
, [Value2]
)
values
('A', 'Q3 2019', 878, null)
, ('A', 'Q1 2019', 767, 584)
, ('A', 'Q2 2019', 5656, null)
, ('B', 'Q1 2019', 7676, 1234)
, ('B', 'Q3 2019', 8545, 4088)
, ('B', 'Q2 2019', 4, null)
, ('C', 'Q3 2019', 5454, 4088)
, ('C', 'Q2 2019', 6767, null)
, ('A', 'Q4 2019', 545, 666)
, ('B', 'Q4 2019', 7677, 2920)
, ('C', 'Q4 2019', 44, 56);

  go
select * from [dbo].[mytable1]
order by [Qtr];
go
select * from [dbo].[mytable2]
order by [Qtr];
go
select * from [dbo].[mytableoutput]
order by [Qtr];
go

  -- Clean up the Sandbox database
if object_id('[dbo].[mytable1]','U') is not null
drop table [dbo].[mytable1];

  if object_id('[dbo].[mytable2]','U') is not null
drop table [dbo].[mytable2];

  if object_id('[dbo].[mytableoutput]','U') is not null
drop table [dbo].[mytableoutput];


  In mytable1 you have 2 entries for Product A in Q4 2019, is this correct?

  You also do not have an entry for Product C Q1 2019 in mytable1.  Is that correct and if so do you want what is in mytable2 for that product?

  In fact, your expected results doesn't even match your sample data.  You should revisit your sample data and expected results to be sure they match.

  It is possible that there wont be entry for all quarters in table 1 but there will be alteast one quarter value present. 
  Yes Product A can have 2 rows but in my final output i am not considering the Q4 from table1 at all .I want it from table2

  Doesn't really answer the question but since you don't seem to care all that much, I will work with it.  You get what you get and you have to support it.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 7, 2018 at 4:09 pm

  #2012767

  Per your specs:
  
if object_id('[dbo].[mytable]','U') is not null
drop table [dbo].[mytable]; -- clean up my SANDBOX DATABASE!!!!!

  create table [dbo].[mytable]
(
[StartDate] date not null primary key
, [CalendarMonth] as datepart(month,[StartDate]) persisted
, [CalendarYear] as datepart(year,[StartDate]) persisted
, [fy_year]  as datepart(year,dateadd(month,11,[StartDate])) persisted
, [fy_qtr]  as datepart(quarter,dateadd(month,11,[StartDate])) persisted
, [fy_month]  as datepart(month,dateadd(month,11,[StartDate])) persisted
, [QuarterFy] as 'Q' + cast(datepart(quarter,dateadd(month,11,[StartDate])) as char(1)) + ' ' + cast(datepart(year,dateadd(month,11,[StartDate])) as char(4)) persisted
);
go
INSERT INTO mytable(StartDate)
values
('2018-02-01'),
('2018-03-01'),
('2018-04-01'),
('2018-05-01'),
('2018-06-01'),
('2018-07-01'),
('2018-08-01'),
('2018-09-01'),
('2018-10-01'),
('2018-11-01'),
('2018-12-01'),
('2019-01-01'),
('2019-02-01'),
('2019-03-01'),
('2019-04-01'),
('2019-05-01'),
('2019-06-01'),
('2019-07-01'),
('2019-08-01'),
('2019-09-01'),
('2019-10-01'),
('2019-11-01'),
('2019-12-01'),
('2020-01-01');
go

  if object_id('[dbo].[mytable1]','U') is not null
drop table [dbo].[mytable1];

  create table [dbo].[mytable1]
(
  [Product] varchar(1) not null
, [Qtr] varchar(7) not null
, [Value1] integer not null
, [Value2] integer
);

  insert into [dbo].[mytable1]
(
  [Product]
, [Qtr]
, [Value1]
, [Value2]
)
values
  ('A', 'Q3 2019', 878, null)
, ('A', 'Q1 2019', 767, 584)
, ('A', 'Q2 2019', 5656, null)
, ('A', 'Q4 2019', 55, null)
, ('B', 'Q1 2019', 7676, 1234)
, ('B', 'Q3 2019', 8545, 4088)
, ('A', 'Q4 2019', 434, 565)
, ('B', 'Q2 2019', 4, null)
, ('C', 'Q3 2019', 5454, 4088)
, ('C', 'Q2 2019', 6767, null);
go

  if object_id('[dbo].[mytable2]','U') is not null
drop table [dbo].[mytable2];

  create table [dbo].[mytable2]
(
  [Product] varchar(1) not null
, [Qtr] varchar(7) not null
, [Value1] integer not null
, [Value2] integer
);

  insert into [dbo].[mytable2]
(
  [Product]
, [Qtr]
, [Value1]
, [Value2]
)
values
  ('A', 'Q3 2019', 54, null)
, ('A', 'Q1 2019', 43, null)
, ('A', 'Q2 2019', 323, null)
, ('A', 'Q4 2019', 545, 666)
, ('B', 'Q3 2019', 545, 4234)
, ('B', 'Q4 2019', 7677, 2920)
, ('B', 'Q1 2019', 878, null)
, ('C', 'Q1 2019', 545, 77)
, ('C', 'Q4 2019', 44, 56);
go
if object_id('[dbo].[mytableoutput]','U') is not null
drop table [dbo].[mytableoutput];

  create table [dbo].[mytableoutput]
(
  [Product] varchar(1) not null
, [Qtr] varchar(7) not null
, [Value1] integer not null
, [Value2] integer
);
insert into [dbo].[mytableoutput]
(
  [Product]
, [Qtr]
, [Value1]
, [Value2]
)
values
('A', 'Q3 2019', 878, null)
, ('A', 'Q1 2019', 767, 584)
, ('A', 'Q2 2019', 5656, null)
, ('B', 'Q1 2019', 7676, 1234)
, ('B', 'Q3 2019', 8545, 4088)
, ('B', 'Q2 2019', 4, null)
, ('C', 'Q3 2019', 5454, 4088)
, ('C', 'Q2 2019', 6767, null)
, ('A', 'Q4 2019', 545, 666)
, ('B', 'Q4 2019', 7677, 2920)
, ('C', 'Q4 2019', 44, 56);

  go
--select * from [dbo].[mytable];
--go
--select * from [dbo].[mytable1]
--order by [Qtr];
--go
--select * from [dbo].[mytable2]
--order by [Qtr];
--go
--select * from [dbo].[mytableoutput]
--order by [Qtr];
--go

  with BaseQuarter as (
select
[qt] = max([QuarterFy])
, [fy] = max([fy_year])
from
[dbo].[mytable]
where
StartDate <= getdate()
), BaseDate as (
select
[CutDate] = min([mt].[StartDate])
, [fy] = min([bq].[fy])
, [qt] = min([bq].[qt])
from
[dbo].[mytable] [mt]
inner join [BaseQuarter] [bq]
  on [mt].[QuarterFy] = [bq].[qt]
)
--select * from [BaseDate]
select
[mt1].*
from
[dbo].[mytable1] [mt1]
cross join [BaseDate] [bd]
where
[mt1].[Qtr] like '%' + cast([bd].[fy] as char(4))
and [mt1].[Qtr] < [bd].[qt]
union all
select
[mt2].*
from
[dbo].[mytable2] [mt2]
cross join [BaseDate] [bd]
where
[mt2].[Qtr] like '%' + cast([bd].[fy] as char(4))
and [mt2].[Qtr] >= [bd].[qt]
order by
[mt1].[Qtr]
, [mt1].[Product];

  -- Clean up the Sandbox database
if object_id('[dbo].[mytable1]','U') is not null
drop table [dbo].[mytable1];

  if object_id('[dbo].[mytable2]','U') is not null
drop table [dbo].[mytable2];

  if object_id('[dbo].[mytableoutput]','U') is not null
drop table [dbo].[mytableoutput];

  if object_id('[dbo].[mytable]','U') is not null
drop table [dbo].[mytable]; -- clean up my SANDBOX DATABASE!!!!!
go