SELECT [Order].Id, [Order].Email, [Order].PromotionCode, [Order].ShippingCost, OrderItem.ItemNumber, Size.Name, OrderItem.Price, Size.Id AS Expr1
FROM [Order] INNER JOIN OrderItem ON [Order].Id = OrderItem.OrderId
INNER JOIN Size ON OrderItem.SizeId = Size.Id
WHERE ([Order].PromotionCode = 'M123') OR ([Order].PromotionCode = 'T4444') OR ([Order].PromotionCode = 'WABD') OR ([Order].PromotionCode = 'Q695') OR ([Order].PromotionCode = 'P675')
DECLARE @Order TABLE (
Id INT,
Email VARCHAR(100),
PromotionCode VARCHAR(100),
ShippingCost MONEY
)
INSERT INTO @Order VALUES (1001, 'test1@mail.com', 'WABD', 4.95)
INSERT INTO @Order VALUES (1002, 'test2@mail.com', 'WABD', 4.95)
INSERT INTO @Order VALUES (1003, 'test3@mail.com', 'WABD', 14.95)
INSERT INTO @Order VALUES (1004, 'test1@mail.com', 'T4444', 4.95)
INSERT INTO @Order VALUES (1005, 'test2@mail.com', 'WABD', 14.95)
INSERT INTO @Order VALUES (1006, 'test3@mail.com', 'WABD', 4.95)
INSERT INTO @Order VALUES (1007, 'test1@mail.com', 'M123', 14.95)
INSERT INTO @Order VALUES (1008, 'test2@mail.com', 'W123', 4.95)
INSERT INTO @Order VALUES (1009, 'test3@mail.com', 'M435', 4.95)
INSERT INTO @Order VALUES (1010, 'test1@mail.com', 'WABD', 4.95)
DECLARE @OrderItem TABLE (
ItemNumber VARCHAR(100),
OrderId INT,
SizeId INT,
Price MONEY
)
INSERT INTO @OrderItem VALUES ('Y0804', 1001, 401, 198)
INSERT INTO @OrderItem VALUES ('Y0804', 1002, 401, 198)
INSERT INTO @OrderItem VALUES ('Y0803', 1002, 310, 189)
INSERT INTO @OrderItem VALUES ('Y3305', 1002, 401, 52)
INSERT INTO @OrderItem VALUES ('Y0804', 1003, 401, 198)
INSERT INTO @OrderItem VALUES ('Y3305', 1003, 401, 189)
INSERT INTO @OrderItem VALUES ('Y2302', 1003, 207, 112)
INSERT INTO @OrderItem VALUES ('Y2302', 1004, 207, 112)
INSERT INTO @OrderItem VALUES ('Y3305', 1004, 401, 52)
INSERT INTO @OrderItem VALUES ('Y3207', 1005, 312, 38)
INSERT INTO @OrderItem VALUES ('X1304', 1006, 512, 58)
INSERT INTO @OrderItem VALUES ('X1005', 1007, 512, 56)
INSERT INTO @OrderItem VALUES ('X1304', 1008, 512, 58)
INSERT INTO @OrderItem VALUES ('X1005', 1008, 512, 56)
INSERT INTO @OrderItem VALUES ('Y0804', 1009, 401, 198)
INSERT INTO @OrderItem VALUES ('Y0804', 1010, 401, 198)
INSERT INTO @OrderItem VALUES ('Y0803', 1010, 310, 189)
DECLARE @Size TABLE (
Id INT,
Name VARCHAR(100)
)
INSERT INTO @Size VALUES (401, '7')
INSERT INTO @Size VALUES (310, '4')
INSERT INTO @Size VALUES (207, '2T')
INSERT INTO @Size VALUES (312, '5')
INSERT INTO @Size VALUES (512, '')
DECLARE @Result TABLE (
OrderId VARCHAR(100),
Email VARCHAR(100),
PromotionCode VARCHAR(100),
ShippingCost VARCHAR(100),
TotalPrice VARCHAR(100),
TotalPricePlusShipping VARCHAR(100),
RowNo INT,
ItemNo VARCHAR(100),
ItemPrice VARCHAR(100),
Size VARCHAR(100),
SizeID VARCHAR(100)
)
DECLARE @x INT
DECLARE @y INT
DECLARE @OrderId INT
------------------------------------------------------------------
SELECT
o.Id AS OrderId,
o.Email,
o.PromotionCode,
o.ShippingCost,
SUM(i.Price) AS TotalPrice,
SUM(i.Price) + o.ShippingCost AS TotalPricePlusShipping,
ROW_NUMBER() OVER(ORDER BY Id) RowNo
INTO
#SelectedOrder
FROM
@Order o
INNER JOIN @OrderItem i ON o.Id = i.OrderId
WHERE
PromotionCode IN ('M123', 'T4444', 'WABD', 'Q695', 'P675')
GROUP BY
o.Id,
o.Email,
o.PromotionCode,
o.ShippingCost
SET @x = 1
SELECT
@y = COUNT(*)
FROM
#SelectedOrder
------------------------------------------------------------------
WHILE @x <= @y
BEGIN
SELECT
@OrderId = OrderId
FROM
#SelectedOrder
WHERE
RowNo = @x
INSERT INTO
@Result
SELECT
*,
'',
'',
'',
''
FROM
#SelectedOrder
WHERE
OrderId = @OrderId
INSERT INTO
@Result
SELECT
'',
'',
'',
'',
'',
'',
'',
i.ItemNumber,
i.Price,
s.Name,
i.SizeId
FROM
@OrderItem i
LEFT JOIN @Size s ON i.SizeId = s.Id
WHERE
i.OrderId = @OrderId
SET @x = @x + 1
END
------------------------------------------------------------------
SELECT
OrderId,
Email,
PromotionCode,
ShippingCost,
TotalPrice,
TotalPricePlusShipping,
ItemNo,
ItemPrice,
Size,
SizeID
FROM
@Result
------------------------------------------------------------------
DROP TABLE #SelectedOrder