SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) 
	Oct  8 2022 05:58:25 
	Copyright (C) 2022 Microsoft Corporation
	Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE sales_data 
(
    CustomerCode	INT,
    InvoiceNumber	VARCHAR(512),
    IsReturn	VARCHAR(512),
    InvoiceDate	VARCHAR(512),
    ApplicationID	VARCHAR(512),
    ItemCode	VARCHAR(512),
    ColorCode	VARCHAR(512),
    ItemDim1Code	VARCHAR(512),
    Qty	INT,
    Revenue	Money
);

INSERT INTO sales_data (CustomerCode, InvoiceNumber, IsReturn, InvoiceDate, ApplicationID, ItemCode, ColorCode, ItemDim1Code, Qty, Revenue) VALUES
	('15656858', '193-R-7-373740', '1', '11.06.2022', 'DB3A6DCB-7A8C-415C-AF35-AEA200D5FE7F', '4A1400000002', 'SYH', '50', '-1', -699.99),
	('15656858', '193-R-7-372193', '0', '27.05.2022', 'DB3A6DCB-7A8C-415C-AF35-AEA200D5FE7F', '4A1400000002', 'SYH', '50', '2', 1399.98),
	('15656858', '193-R-7-373781', '1', '12.06.2022', 'D7BD6F42-F1F3-4EC4-9599-AEB000A7CB70', '4A2022200036', 'YVZ', 'XL', '-1', -240.28),
	('15656858', '193-R-7-373502', '0', '10.06.2022', 'D7BD6F42-F1F3-4EC4-9599-AEB000A7CB70', '4A2022200036', 'YVZ', 'XL', '1', 240.28),
	('15656858', '193-R-7-372193', '0', '27.05.2022', 'DB3A6DCB-7A8C-415C-AF35-AEA200D5FE7F', '4A7020000001', 'NAT', 'STN', '1', 26.95),
	('15656858', '193-R-7-373782', '0', '12.06.2022', 'D832836F-36BB-42ED-B64C-AEB200B6B19F', '4A2022200001', 'BYD', 'L', '1', 299.99),
	('15656858', '193-R-7-373782', '0', '12.06.2022', 'D832836F-36BB-42ED-B64C-AEB200B6B19F', '4A2022200034', 'HAK', 'XL', '1', 299.99),
	('15656858', '193-R-7-373742', '0', '11.06.2022', '00CB6240-D073-4E06-8CA9-AEB10133DE90', '4A1400000001', 'SYH', '50', '1', 699.99),
	('15656858', '193-R-7-373742', '0', '11.06.2022', '00CB6240-D073-4E06-8CA9-AEB10133DE90', '4A1400000001', 'ATR', '52', '1', 699.99),
	('21069880', '406-R-7-121023', '0', '4.05.2023', '67732FA9-E3CA-4BB1-8C13-AFF800F89C60', '4A0121100002', 'YSL', '52', '1', 199.99),
	('21069880', '406-R-7-120741', '0', '26.04.2023', '284DE832-8CE7-41D1-9FCE-AE31011A4AA8', '4A1400000001', 'SYH', '56', '1', 379.99),
	('21069880', '1-R-7-16894399', '1', '26.04.2023', '284DE832-8CE7-41D1-9FCE-AE31011A4AA8', '4A1400000001', 'SYH', '56', '-1', -379.99),
	('21069880', '406-R-7-120740', '0', '26.04.2023', '9E1674D1-8B70-4BCF-9A82-AFF00112E876', '4A1400000001', 'SYH', '52', '1', 879.99),
	('21069880', '1-R-7-16853468', '0', '3.09.2022', 'F045ED37-E17C-4690-83D9-AF0500A17621', '4A2000000013', 'amv', 'XL', '3', 1999.86),
	('21069880', '1-R-7-16894396', '1', '7.09.2022', 'F045ED37-E17C-4690-83D9-AF0500A17621', '4A2000000013', 'amv', 'XL', '-1', -666.62),
	('21069880', '1-R-7-16853468', '0', '3.09.2022', 'F045ED37-E17C-4690-83D9-AF0500A17621', '4A3022100145', 'GSY', '52', '1', 1587.20),
	('21069880', '406-R-7-120740', '0', '4.05.2023', '8C5D271E-30D2-497E-BB0C-AFF800F9EE0B', '4A9000000001', '', '', '', 0);

--verify example records
SELECT * FROM sales_data


CustomerCode
InvoiceNumber
IsReturn
InvoiceDate
ApplicationID
ItemCode
ColorCode
ItemDim1Code
Qty
Revenue


15656858
193-R-7-373740
1
11.06.2022
DB3A6DCB-7A8C-415C-AF35-AEA200D5FE7F
4A1400000002
SYH
50
-1
-699.9900

15656858
193-R-7-372193
0
27.05.2022
DB3A6DCB-7A8C-415C-AF35-AEA200D5FE7F
4A1400000002
SYH
50
2
1399.9800

15656858
193-R-7-373781
1
12.06.2022
D7BD6F42-F1F3-4EC4-9599-AEB000A7CB70
4A2022200036
YVZ
XL
-1
-240.2800

15656858
193-R-7-373502
0
10.06.2022
D7BD6F42-F1F3-4EC4-9599-AEB000A7CB70
4A2022200036
YVZ
XL
1
240.2800

15656858
193-R-7-372193
0
27.05.2022
DB3A6DCB-7A8C-415C-AF35-AEA200D5FE7F
4A7020000001
NAT
STN
1
26.9500

15656858
193-R-7-373782
0
12.06.2022
D832836F-36BB-42ED-B64C-AEB200B6B19F
4A2022200001
BYD
L
1
299.9900

15656858
193-R-7-373782
0
12.06.2022
D832836F-36BB-42ED-B64C-AEB200B6B19F
4A2022200034
HAK
XL
1
299.9900

15656858
193-R-7-373742
0
11.06.2022
00CB6240-D073-4E06-8CA9-AEB10133DE90
4A1400000001
SYH
50
1
699.9900

15656858
193-R-7-373742
0
11.06.2022
00CB6240-D073-4E06-8CA9-AEB10133DE90
4A1400000001
ATR
52
1
699.9900

21069880
406-R-7-121023
0
4.05.2023
67732FA9-E3CA-4BB1-8C13-AFF800F89C60
4A0121100002
YSL
52
1
199.9900

21069880
406-R-7-120741
0
26.04.2023
284DE832-8CE7-41D1-9FCE-AE31011A4AA8
4A1400000001
SYH
56
1
379.9900

21069880
1-R-7-16894399
1
26.04.2023
284DE832-8CE7-41D1-9FCE-AE31011A4AA8
4A1400000001
SYH
56
-1
-379.9900

21069880
406-R-7-120740
0
26.04.2023
9E1674D1-8B70-4BCF-9A82-AFF00112E876
4A1400000001
SYH
52
1
879.9900

21069880
1-R-7-16853468
0
3.09.2022
F045ED37-E17C-4690-83D9-AF0500A17621
4A2000000013
amv
XL
3
1999.8600

21069880
1-R-7-16894396
1
7.09.2022
F045ED37-E17C-4690-83D9-AF0500A17621
4A2000000013
amv
XL
-1
-666.6200

21069880
1-R-7-16853468
0
3.09.2022
F045ED37-E17C-4690-83D9-AF0500A17621
4A3022100145
GSY
52
1
1587.2000

21069880
406-R-7-120740
0
4.05.2023
8C5D271E-30D2-497E-BB0C-AFF800F9EE0B
4A9000000001


0
0.0000


--List with items that were purchased and returned
With ReturnedItems  as
(  
--Items that have been returned  
SELECT CustomerCode,ItemCode,ApplicationID 
FROM sales_data 
GROUP BY CustomerCode,ItemCode,ApplicationID  	
HAVING MAX(IsReturn)=1
)
SELECT s.* 
FROM sales_data s
INNER JOIN ReturnedItems r ON r.CustomerCode=s.CustomerCode 
                         and r.ItemCode=s.ItemCode
                         and r.ApplicationID=s.ApplicationID  



CustomerCode
InvoiceNumber
IsReturn
InvoiceDate
ApplicationID
ItemCode
ColorCode
ItemDim1Code
Qty
Revenue


15656858
193-R-7-373740
1
11.06.2022
DB3A6DCB-7A8C-415C-AF35-AEA200D5FE7F
4A1400000002
SYH
50
-1
-699.9900

15656858
193-R-7-372193
0
27.05.2022
DB3A6DCB-7A8C-415C-AF35-AEA200D5FE7F
4A1400000002
SYH
50
2
1399.9800

15656858
193-R-7-373781
1
12.06.2022
D7BD6F42-F1F3-4EC4-9599-AEB000A7CB70
4A2022200036
YVZ
XL
-1
-240.2800

15656858
193-R-7-373502
0
10.06.2022
D7BD6F42-F1F3-4EC4-9599-AEB000A7CB70
4A2022200036
YVZ
XL
1
240.2800

21069880
406-R-7-120741
0
26.04.2023
284DE832-8CE7-41D1-9FCE-AE31011A4AA8
4A1400000001
SYH
56
1
379.9900

21069880
1-R-7-16894399
1
26.04.2023
284DE832-8CE7-41D1-9FCE-AE31011A4AA8
4A1400000001
SYH
56
-1
-379.9900

21069880
1-R-7-16853468
0
3.09.2022
F045ED37-E17C-4690-83D9-AF0500A17621
4A2000000013
amv
XL
3
1999.8600

21069880
1-R-7-16894396
1
7.09.2022
F045ED37-E17C-4690-83D9-AF0500A17621
4A2000000013
amv
XL
-1
-666.6200


--Items that have been returned  
SELECT CustomerCode,ItemCode,ApplicationID, SUM(cast(qty as int)) as NewQty, 
  Sum(Revenue) as NewRevenue
FROM sales_data 
GROUP BY CustomerCode,ItemCode,ApplicationID  	
HAVING MAX(IsReturn)=1


CustomerCode
ItemCode
ApplicationID
NewQty
NewRevenue


15656858
4A1400000002
DB3A6DCB-7A8C-415C-AF35-AEA200D5FE7F
1
699.9900

15656858
4A2022200036
D7BD6F42-F1F3-4EC4-9599-AEB000A7CB70
0
0.0000

21069880
4A1400000001
284DE832-8CE7-41D1-9FCE-AE31011A4AA8
0
0.0000

21069880
4A2000000013
F045ED37-E17C-4690-83D9-AF0500A17621
2
1333.2400


With ReturnedItems  as
(  
--Items that have been returned  
SELECT CustomerCode,ItemCode,ApplicationID, SUM(cast(qty as int)) as NewQty, 
  Sum(Revenue) as NewRevenue
FROM sales_data 
GROUP BY CustomerCode,ItemCode,ApplicationID  	
HAVING MAX(IsReturn)=1
)
SELECT s.*, r.NewQty,r.NewRevenue
FROM sales_data s
LEFT JOIN ReturnedItems r ON r.CustomerCode=s.CustomerCode 
                         and r.ItemCode=s.ItemCode
                         and r.ApplicationID=s.ApplicationID  
WHERE IsReturn=0 AND (r.NewQty<>0 or r.NewQty IS NULL )


CustomerCode
InvoiceNumber
IsReturn
InvoiceDate
ApplicationID
ItemCode
ColorCode
ItemDim1Code
Qty
Revenue
NewQty
NewRevenue


15656858
193-R-7-372193
0
27.05.2022
DB3A6DCB-7A8C-415C-AF35-AEA200D5FE7F
4A1400000002
SYH
50
2
1399.9800
1
699.9900

15656858
193-R-7-372193
0
27.05.2022
DB3A6DCB-7A8C-415C-AF35-AEA200D5FE7F
4A7020000001
NAT
STN
1
26.9500
null
null

15656858
193-R-7-373782
0
12.06.2022
D832836F-36BB-42ED-B64C-AEB200B6B19F
4A2022200001
BYD
L
1
299.9900
null
null

15656858
193-R-7-373782
0
12.06.2022
D832836F-36BB-42ED-B64C-AEB200B6B19F
4A2022200034
HAK
XL
1
299.9900
null
null

15656858
193-R-7-373742
0
11.06.2022
00CB6240-D073-4E06-8CA9-AEB10133DE90
4A1400000001
SYH
50
1
699.9900
null
null

15656858
193-R-7-373742
0
11.06.2022
00CB6240-D073-4E06-8CA9-AEB10133DE90
4A1400000001
ATR
52
1
699.9900
null
null

21069880
406-R-7-121023
0
4.05.2023
67732FA9-E3CA-4BB1-8C13-AFF800F89C60
4A0121100002
YSL
52
1
199.9900
null
null

21069880
406-R-7-120740
0
26.04.2023
9E1674D1-8B70-4BCF-9A82-AFF00112E876
4A1400000001
SYH
52
1
879.9900
null
null

21069880
1-R-7-16853468
0
3.09.2022
F045ED37-E17C-4690-83D9-AF0500A17621
4A2000000013
amv
XL
3
1999.8600
2
1333.2400

21069880
1-R-7-16853468
0
3.09.2022
F045ED37-E17C-4690-83D9-AF0500A17621
4A3022100145
GSY
52
1
1587.2000
null
null

21069880
406-R-7-120740
0
4.05.2023
8C5D271E-30D2-497E-BB0C-AFF800F9EE0B
4A9000000001


0
0.0000
null
null


With ReturnedItems  as
(  
--Items that have been returned  
SELECT CustomerCode,ItemCode,ApplicationID, SUM(cast(qty as int)) as NewQty, 
  Sum(Revenue) as NewRevenue
FROM sales_data 
GROUP BY CustomerCode,ItemCode,ApplicationID  	
HAVING MAX(IsReturn)=1
)
SELECT s.CustomerCode, 
       s.InvoiceNumber, 
       s.IsReturn, 
       s.InvoiceDate, 
       s.ApplicationID, 
       s.ItemCode, 
       s.ColorCode, 
       s.ItemDim1Code,  
       Isnull(r.NewQty, s.Qty) as QTY, 
       ISNULL(r.NewRevenue,s.Revenue) as Revenue
FROM sales_data s
LEFT JOIN ReturnedItems r ON r.CustomerCode=s.CustomerCode 
                         and r.ItemCode=s.ItemCode
                         and r.ApplicationID=s.ApplicationID  
WHERE IsReturn=0 AND (r.NewQty<>0 or r.NewQty IS NULL )


CustomerCode
InvoiceNumber
IsReturn
InvoiceDate
ApplicationID
ItemCode
ColorCode
ItemDim1Code
QTY
Revenue


15656858
193-R-7-372193
0
27.05.2022
DB3A6DCB-7A8C-415C-AF35-AEA200D5FE7F
4A1400000002
SYH
50
1
699.9900

15656858
193-R-7-372193
0
27.05.2022
DB3A6DCB-7A8C-415C-AF35-AEA200D5FE7F
4A7020000001
NAT
STN
1
26.9500

15656858
193-R-7-373782
0
12.06.2022
D832836F-36BB-42ED-B64C-AEB200B6B19F
4A2022200001
BYD
L
1
299.9900

15656858
193-R-7-373782
0
12.06.2022
D832836F-36BB-42ED-B64C-AEB200B6B19F
4A2022200034
HAK
XL
1
299.9900

15656858
193-R-7-373742
0
11.06.2022
00CB6240-D073-4E06-8CA9-AEB10133DE90
4A1400000001
SYH
50
1
699.9900

15656858
193-R-7-373742
0
11.06.2022
00CB6240-D073-4E06-8CA9-AEB10133DE90
4A1400000001
ATR
52
1
699.9900

21069880
406-R-7-121023
0
4.05.2023
67732FA9-E3CA-4BB1-8C13-AFF800F89C60
4A0121100002
YSL
52
1
199.9900

21069880
406-R-7-120740
0
26.04.2023
9E1674D1-8B70-4BCF-9A82-AFF00112E876
4A1400000001
SYH
52
1
879.9900

21069880
1-R-7-16853468
0
3.09.2022
F045ED37-E17C-4690-83D9-AF0500A17621
4A2000000013
amv
XL
2
1333.2400

21069880
1-R-7-16853468
0
3.09.2022
F045ED37-E17C-4690-83D9-AF0500A17621
4A3022100145
GSY
52
1
1587.2000

21069880
406-R-7-120740
0
4.05.2023
8C5D271E-30D2-497E-BB0C-AFF800F9EE0B
4A9000000001


0
0.0000

CustomerCode	InvoiceNumber	IsReturn	InvoiceDate	ApplicationID	ItemCode	ColorCode	ItemDim1Code	Qty	Revenue
15656858	193-R-7-373740	1	11.06.2022	DB3A6DCB-7A8C-415C-AF35-AEA200D5FE7F	4A1400000002	SYH	50	-1	-699.9900
15656858	193-R-7-372193	0	27.05.2022	DB3A6DCB-7A8C-415C-AF35-AEA200D5FE7F	4A1400000002	SYH	50	2	1399.9800
15656858	193-R-7-373781	1	12.06.2022	D7BD6F42-F1F3-4EC4-9599-AEB000A7CB70	4A2022200036	YVZ	XL	-1	-240.2800
15656858	193-R-7-373502	0	10.06.2022	D7BD6F42-F1F3-4EC4-9599-AEB000A7CB70	4A2022200036	YVZ	XL	1	240.2800
15656858	193-R-7-372193	0	27.05.2022	DB3A6DCB-7A8C-415C-AF35-AEA200D5FE7F	4A7020000001	NAT	STN	1	26.9500
15656858	193-R-7-373782	0	12.06.2022	D832836F-36BB-42ED-B64C-AEB200B6B19F	4A2022200001	BYD	L	1	299.9900
15656858	193-R-7-373782	0	12.06.2022	D832836F-36BB-42ED-B64C-AEB200B6B19F	4A2022200034	HAK	XL	1	299.9900
15656858	193-R-7-373742	0	11.06.2022	00CB6240-D073-4E06-8CA9-AEB10133DE90	4A1400000001	SYH	50	1	699.9900
15656858	193-R-7-373742	0	11.06.2022	00CB6240-D073-4E06-8CA9-AEB10133DE90	4A1400000001	ATR	52	1	699.9900
21069880	406-R-7-121023	0	4.05.2023	67732FA9-E3CA-4BB1-8C13-AFF800F89C60	4A0121100002	YSL	52	1	199.9900
21069880	406-R-7-120741	0	26.04.2023	284DE832-8CE7-41D1-9FCE-AE31011A4AA8	4A1400000001	SYH	56	1	379.9900
21069880	1-R-7-16894399	1	26.04.2023	284DE832-8CE7-41D1-9FCE-AE31011A4AA8	4A1400000001	SYH	56	-1	-379.9900
21069880	406-R-7-120740	0	26.04.2023	9E1674D1-8B70-4BCF-9A82-AFF00112E876	4A1400000001	SYH	52	1	879.9900
21069880	1-R-7-16853468	0	3.09.2022	F045ED37-E17C-4690-83D9-AF0500A17621	4A2000000013	amv	XL	3	1999.8600
21069880	1-R-7-16894396	1	7.09.2022	F045ED37-E17C-4690-83D9-AF0500A17621	4A2000000013	amv	XL	-1	-666.6200
21069880	1-R-7-16853468	0	3.09.2022	F045ED37-E17C-4690-83D9-AF0500A17621	4A3022100145	GSY	52	1	1587.2000
21069880	406-R-7-120740	0	4.05.2023	8C5D271E-30D2-497E-BB0C-AFF800F9EE0B	4A9000000001			0	0.0000