IF OBJECT_ID(N'tempdb..#Order', N'U') IS NOT NULL DROP TABLE #Order;
IF OBJECT_ID(N'tempdb..#OtherCharges', N'U') IS NOT NULL DROP TABLE #OtherCharges;
create table #Order (
CustomerAccountID varchar (10),ShipmentStatusID int ,EndDate date
);
create table #OtherCharges (
CustomerAccountID varchar (10),Charges numeric(14,2) ,EntryDate date
);
insert into #Order (CustomerAccountID,ShipmentStatusID,EndDate)
values
('C0001',1,'2018-02-02'),
('C0001',4,'2018-09-02'),
('C0002',1,'2018-05-01'),
('C0002',2,'2018-09-02'),
('C0001',5,'2018-12-02'),
('C0003',4,'2018-08-05'),
('C0002',4,'2018-12-30');
insert into #OtherCharges (CustomerAccountID,Charges,EntryDate)
values
('C0001',1,'2018-02-02'),
('C0001',5000,'2018-08-02'),
('C0002',4500,'2018-05-01'),
('C0002',6900,'2018-09-02'),
('C0001',7000,'2018-12-02'),
('C0003',12000,'2018-06-02'),
('C0002',8500,'2018-12-30');
DECLARE @UpdateLog table(NewColumn varchar (10) NOT NULL);
insert into @UpdateLog
SELECT derivedtable.NewColumn
FROM
(
SELECT CustomerAccountID as NewColumn
FROM #Order
where ShipmentStatusID=4 and EndDate>='2018-08-01' and EndDate<='2018-08-07'
UNION
SELECT CustomerAccountID as NewColumn FROM #Order
where ShipmentStatusID=5 and EndDate>='2018-08-01' and EndDate<='2018-08-07'
union
SELECT CustomerAccountID as NewColumn
FROM #OtherCharges where EntryDate>='2018-08-01' and EntryDate<='2018-08-07'
) derivedtable
WHERE derivedtable.NewColumn IS NOT NULL
order by NewColumn
select * from @UpdateLog;