A multiple inner join query question

[samuel_1991]

Hi, I need to use query to check if a person has made a purchase during the month of his / her birthday. If yes, I have to give the person 10% discount.

So, I have my database as such (Some of the tables are omitted due to its relavancy):

Shopper Table (Details of each registered shopper):

/* Table: dbo.Shopper 	*/
CREATE TABLE dbo.Shopper 
(
  ShopperID int IDENTITY (1,1),
  Name varchar (50) NOT NULL,
  BirthDate datetime NULL,
  Address varchar (150) NULL,
  Country varchar (50) NULL,
  Phone varchar (20) NULL,
  Email varchar (50) NOT NULL,
  Password varchar (20) NOT NULL,
  PwdQuestion varchar (100) NULL,
  PwdAnswer varchar (50) NULL,
  ActiveStatus int NOT NULL DEFAULT (1),
  DateEntered datetime NULL DEFAULT (getdate()),
  CONSTRAINT PK_Shopper PRIMARY KEY NONCLUSTERED (ShopperID)
)
GO

Shopping Cart (To store shopping cart.)

CREATE TABLE dbo.ShopCart
(
  ShopCartID int IDENTITY (1,1),
  ShopperID int NOT NULL,
  OrderPlaced int NOT NULL DEFAULT (0),
  Quantity int NULL,  
  SubTotal money NULL,
  Tax money NULL,
  ShipCharge money NULL,
  Discount money NULL DEFAULT (0),
  Total money NULL,
  DateCreated datetime NULL DEFAULT (getdate()),
  CONSTRAINT PK_ShopCart PRIMARY KEY NONCLUSTERED (ShopCartID),
  CONSTRAINT FK_ShopCart_Shopper FOREIGN KEY (ShopperID) REFERENCES dbo.Shopper(ShopperID)
)
GO

Notes:

Firstly, I need to update Discount Money as in how much have given as discount, if $100 has spent to purchase, then should be $10 should be updated in this column.

Lastly, The OrderPlaced column is meant to detect if an order has really confirmed and made.

And this column OrderData Table (To store all data regarding to order details such as shipping address, delivery mode, date when order was placed etc)

CREATE TABLE dbo.OrderData
(
  OrderID int IDENTITY (1,1),
  ShopCartID int NOT NULL,
  ShipName varchar (50) NOT NULL,
  ShipAddress varchar (150) NOT NULL,
  ShipCountry varchar (50) NOT NULL,
  ShipPhone varchar (20) NULL,
  ShipEmail varchar (50) NULL,
  BillName varchar (50) NOT NULL,
  BillAddress varchar (150) NOT NULL,
  BillCountry varchar (50) NOT NULL,
  BillPhone varchar (20) NULL,
  BillEmail varchar (50) NULL,
  DeliveryDate datetime NULL,
  DeliveryTime varchar(50) NULL,
  DeliveryMode varchar(50) NULL,
  OrderStatus int NOT NULL DEFAULT (1),
  DateOrdered datetime NULL DEFAULT (getdate()),
  CONSTRAINT PK_OrderData PRIMARY KEY NONCLUSTERED (OrderID),
  CONSTRAINT FK_OrderData_ShopCart FOREIGN KEY (ShopCartID)
  REFERENCES dbo.ShopCart (ShopCartId)
)

The question is in "step 1":

select Birthdate from Shopper
Inner Join ShopCart
On ShopCart.ShopperID = Shopper.ShopperID
Where OrderPlaced = 1

I tried to join 2 tables and it works. Now, how to join OrderData in order to get the date where order is placed and to update the Discount Money column in ShopCart table.

[noamwegner]

-- creating a temp table to hold the lucky ones
CREATE TABLE #whoGotDisountShopCartId(id BIGINT)
INSERT INTO #whoGotDisountShopCartId
SELECT DISTINT ShopCart.ShopCartID
FROM Shopper
INNER JOIN ShopCart
ON ShopCart.ShopperID = Shopper.ShopperID
INNER JOIN orderData ON orderData.ShopCartId = ShopCart.id
WHERE OrderPlaced = 1 AND MONTH(Shopper.Birthdate) = MONTH(orderData.DateOrdered )
AND YEAR(Shopper.Birthdate) = YEAR(orderData.DateOrdered )

-- here you can use cursor but i am showing you the easy but a bit less efficient way
DECLARE @count INT = COUNT(*) FROM #whoGotDisountShopCartId,@id BIGINT
WHILE (@count != 0)
BEGIN
-- getting the shop cart id to give the discount
SET @id = SELECT TOP 1 id FROM #whoGotDisountShopCartId

-- so that the next time we wont get the same value
DELETE id FROM #whoGotDisountShopCartId WHERE id = @id
--giving the discount
UPDATE ShopCart SET discount = total / 10 WHERE ShopCart.id = @id
-- well you know
@count = @count - 1
END
DROP TABLE #whoGotDisountShopCartId
hope it helped
i enjoyed your riddle

[adam_k]

Why use temp table and especially why use trigger?

update shopcart set Discount = (SubTotal + Tax  + ShipCharge) * 0.1 , Total = (SubTotal + Tax  + ShipCharge) - ((SubTotal + Tax  + ShipCharge) * 0.1)
from shopcart a inner join shopper b on a.shopperid = b.shopperid 
inner join orderdata c on a.shopcartid = c.shopcartid 
where month(b.BirthDate) = month(c.DateOrdered)

But be carefull as this will update all orders placed the same month as customer's birthday. You might want to filter out the ones executed or something.

[noamwegner]

Why use temp table and especially why use trigger?

update shopcart set Discount = (SubTotal + Tax  + ShipCharge) * 0.1 , Total = (SubTotal + Tax  + ShipCharge) - ((SubTotal + Tax  + ShipCharge) * 0.1)
from shopcart a inner join shopper b on a.shopperid = b.shopperid 
inner join orderdata c on a.shopcartid = c.shopcartid 
where month(b.BirthDate) = month(c.DateOrdered)

But be carefull as this will update all orders placed the same month as customer's birthday. You might want to filter out the ones executed or something.

i went to sleep and then i thought thats so stupid why did i do that
and didnt have the minute to edit that
thank you