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
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.