I am trying to get the values from the recent and the recent previous date...

So in the table below the most recent date is 08/26/2022 and the recent previous date is 08/19/2022..So I need to pull the values corresponding to 08/26/2022 as current and those pertaining to 08/19/2022 as previous...

Input

Output

Create table ##input1
(date_value date,
segment varchar(30),
model varchar(20),
FC1 int,
FC2 int,
FC3 int,
FC4 int)

insert into ##input1 values
('8/26/2022','Halo ','MJK12','54','19','43','134'),
('8/26/2022','Halo ','JKIO34','70','9','67','117'),
('8/26/2022','Kalo','JK123','75','107','6','6'),
('8/26/2022','Belo','OPWE1','5','101','106','102'),
('8/26/2022','Halo ','KLWE1','35','35','108','9'),
('8/19/2022','Halo ','MJK12','58','19','43','138'),
('8/19/2022','Halo ','JKIO34','74','9','78','121'),
('8/19/2022','Kalo','JK123','79','119','6','8'),
('8/19/2022','Belo','OPWE1','8','101','111','104'),
('8/19/2022','Halo ','KLWE1','39','35','108','11'),
('8/12/2022','Halo ','MJK12','60','18','46','139'),
('8/12/2022','Halo ','JKIO34','76','8','81','122'),
('8/12/2022','Kalo','JK123','81','118','9','9'),
('8/12/2022','Belo','OPWE1','10','100','114','105'),
('8/12/2022','Halo ','KLWE1','41','34','111','12')

Create table ##output1
(segment varchar(20),
model varchar(30),
FC1-current int,
FC1-previous int,
FC2-current int,
FC2-previous int,
FC3-current int,
FC3-previous int,
FC4-current int,
FC4-previous int)

insert into ##output1 values
('Halo ','MJK12','54','58','19','19','43','43','134','138'),
('Halo ','JKIO34','70','74','9','9','67','78','117','121'),
('Kalo','JK123','75','79','107','119','6','6','6','8'),
('Belo','OPWE1','5','8','101','101','106','111','102','104'),
('Halo ','KLWE1','35','39','35','35','108','108','9','11')

Query Tried:

    WITH CTE AS
  (
   SELECT *,DENSE_RANK()OVER(ORDER BY "DATE_VALUE" DESC) AS RNum
       FROM input
      
      )
      SELECT c1.rnum, C1.SEGMENT,C1.MODEL,C1.FC1 as FC1current,c2.FC1 as FC1old,C1.FC2 as FC2current,c2.FC2 as FC2old,C1.FC3 as FC3current,c2.FC3 as FC3old,C1.FC4 as FC4current,c2.FC4 as FC4old,C1.FC5 as FC5current,c2.FC5 as FC5old,
      
       C1.FC6 as FC6current,c2.FC6 as FC6old


  FROM CTE C1 LEFT JOIN CTE C2 ON C1.RNum  = C2.RNum   1 AND trim(C1.segment)=trim(C2.segment) AND trim(C1.model)=trim(C2.model)
      WHERE C1.RNum IN (1,2) 
  
 

CodePudding user response：

    WITH CTE1 AS
  (
      SELECT *,
       row_number() OVER (partition by segment,model order by date_value desc) AS RNum
       FROM ##input1
      
      )
    
select 

cte1.date_value,
cte1.segment,
cte1.model,
cte1.fc1,
cte2.fc1 prevfc1,
cte1.fc2,
cte2.fc2 prevfc2,
cte1.fc3,
cte2.fc3 prevfc3,
cte1.fc4,
cte2.fc4 prevfc4

from CTE1  

left join CTE1 cte2 on cte2.segment = cte1.segment
and cte2.model = cte1.model
and cte1.RNum = cte2.rnum-1
 order by 1 desc