Getting the Promise dates across weeks for an order

Transact-SQL
1

Hi All,

Hope you are doing well!...I am trying to pull the current promise date and the promise date 7 days ago, promise date 14 days ago, promise date 28 days ago with respect to the field date_value...

The promise date corresponding to the latest date_value is the current promise date...Please find the DDL below (sample for one order)

The recdate is the latest date_value, rec1date is date_value-7, rec2date is date_value-14, rec3date is date_value-28

Input

create table ##input
(order varchar(20),
model varchar(40),
orddate date,
date_value date.
prmdate date)

insert into ##input values
('B67189','mcx2134','9/24/2021','6/7/2022','6/9/2022'),
('B67189','mcx2134','9/24/2021','6/5/2022','6/9/2022'),
('B67189','mcx2134','9/24/2021','6/4/2022','6/8/2022'),
('B67189','mcx2134','9/24/2021','6/3/2022','6/8/2022'),
('B67189','mcx2134','9/24/2021','6/2/2022','6/8/2022'),
('B67189','mcx2134','9/24/2021','5/31/2022','6/7/2022'),
('B67189','mcx2134','9/24/2021','5/27/2022','6/7/2022'),
('B67189','mcx2134','9/24/2021','5/25/2022','6/7/2022'),
('B67189','mcx2134','9/24/2021','5/24/2022','6/7/2022'),
('B67189','mcx2134','9/24/2021','5/20/2022','6/5/2022'),
('B67189','mcx2134','9/24/2021','5/18/2022','6/5/2022'),
('B67189','mcx2134','9/24/2021','5/15/2022','6/5/2022'),
('B67189','mcx2134','9/24/2021','5/10/2022','6/5/2022')

output

create table ##output
(order varchar(20),
model varchar(40),
orddate date,
prmdate date,
recdate date,
rec1date date,
rec2date date,
rec3date date,
prmdate-7d date,
prmdate-14d date,
prmdate-28d date)

insert into ##output values
('B67189','mcx2134','9/24/2021','6/9/2022','6/7/2022',05/31/2022','05/24/2022','05/10/2022','6/7/2022','6/7/2022','6/5/2022')

Thanks,
Arun

2

I can't follow. There are not enough values in the INSERT statement for ##output.

3

Hi @ScottPletcher : I have edited the output table and removed the extra field date_value ...So in my output table prmdate is the date corresponding to the latest date_value (which in the output table is recdate) rec1 date is date_value-7, rec2date is date_value-14, rec3date is date_value-28 and the correspoding prmdates should also be extracted which are prmdate-7d,prmdate-14d ,prmdate-28d