20181117, Sql Server

20181117

CREATE TABLE Offers (
    [OfferID] int,
    [description] nvarchar(255),
    PRIMARY KEY( [OfferID] )
);

CREATE TABLE OrderItems (
    [ordernum] int,
    [categoryid] int,
    [sku] nvarchar(255),
    [qty] int,
    [extprice] numeric(10,2),
    PRIMARY KEY( [OrderNum],[SKU] )
);

INSERT INTO Offers (OfferID, description) VALUES (1,'Offer1');
INSERT INTO Offers (OfferID, description) VALUES (2,'Offer2');
INSERT INTO Offers (OfferID, description) VALUES (3,'Offer3');

insert into OrderItems (ordernum, categoryid, sku, qty, extprice) values (11, 1,'SKU1', 2, 10.00); 
insert into OrderItems (ordernum, categoryid, sku, qty, extprice) values (11, 2,'SKU6', 2, 17.00); 
insert into OrderItems (ordernum, categoryid, sku, qty, extprice) values (11, 3,'SKU2', 2, 12.00); 
insert into OrderItems (ordernum, categoryid, sku, qty, extprice) values (11, 4,'SKU5', 2, 13.00); 
insert into OrderItems (ordernum, categoryid, sku, qty, extprice) values (11, 5,'SKU3', 2, 14.00); 
insert into OrderItems (ordernum, categoryid, sku, qty, extprice) values (11, 6,'SKU7', 2, 18.00);

select * from offers;
select * from orderitems order by categoryid;

select
i.orderNum
, sum(
    case when o.offerID is not null then i.extprice else 0 end
) promoSpend
, sum (
    case when o.offerID is null then i.extprice else 0 end
) nonPromoSpend
from orderItems i
left join Offers o on o.offerid = i.categoryID
group by
i.ordernum


run \| edit \| history \| help	0