CREATE TABLE TRANSACTIONS.ORDERDETAILS
(
	PurchaseID INT IDENTITY (2,2),
	OrderDate Datetime CONSTRAINT CHECK_ORDERDATE CHECK (ORDERDATE > DATETIME),

-- don't know what you mean by this
--third point,I dont know how to create it.

	QUANTITYORDERED INT CONSTRAINT CHECK_QUANTITYORDERED CHECK (QUANTITYORDERED > 0 ),
	QUANTITYRECEIVED INT CONSTRAINT CHECK_QUANTITYRECEIVED CHECK (QUANTITYRECEIVED > 0 ),
	UNITPRICE MONEY CONSTRAINT CHECK_UNITPRICE_MONEY CHECK (CHECK_UNITPRICE > 0 ),
	ReceivingDate Datetime  null ,
	OrderStatus Varchar(50)

-- don't know what you mean by this
--Eight point, I dont know how to create.
)

..
CHECK_ORDERDATE CHECK (ORDERDATE < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1))

quote:

---

Originally posted by Hinduson

please if you are asked a question like this how will you go about it.

Phone number must be in the following format : '[0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]'

I tried to build a rule, and sp_bindrule to bind the rule to the table. But when inserting values it give me errors. So please I will like you to help me out or anyone else around ..

Thanks alot brothers.

Best Regards.

Best Regards.

---

CHECK (phoneno LIKE  '[0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]')

quote:

---

Originally posted by Hinduson

This questions are killing me . And the solution VISAKH16 gave me are not working.

Please can someone help me out.

Create table Transaction.OrderDetails
*PurchaseOderID must be auto generated
*OrderDate should not be greater than the current date
*If the order date is not entered,the current date should be taken as the default date
*QuantityOrdered,QuantityReceived, and UnitPrice should be greater than 0
*QuantityReceived should allow NULL.
*Quantity received cannot be greater than QuantityOrdered.
*QuantityReceived should be added to QuantityInHand in the Items table
*When a record is inserted into the table,QuantityInHand in the Items table should be updated automatically
*OrderStatus must be any of the followinf values: `In Transit' , `Received' , or `Cancelled'.
*ReceivingDate should allow NULL and should be greater than OrderDate.

Best Regards.

---

CREATE TABLE #Test
(
ID int IDENTITY(1,1),
ORDERDATE datetime DEFAULT (GETDATE()),
Value varchar(100),
CONSTRAINT CHECK_ORDERDATE CHECK (ORDERDATE < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1))
)
--this will be success
INSERT #Test (ORDERDATE,Value)
VALUES (GETDATE()-5,'test 1')

--this will throw check constraint error
INSERT #Test (ORDERDATE,Value)
VALUES (GETDATE()+2,'test 2')

--this will put default value for date which will be current days date
INSERT #Test (Value)
VALUES ('test 3')


select * from #Test


output
------------------------------------------

Msg 547, Level 16, State 0, Line 13
The INSERT statement conflicted with the CHECK constraint "CHECK_ORDERDATE". The conflict occurred in database "tempdb", table "dbo.#Test_______________________________________________________________________________________________________________000000000005", column 'ORDERDATE'.
The statement has been terminated.


ID          ORDERDATE               Value
----------- ----------------------- ----------------------------------------------------------------------------------------------------
1           2013-09-29 11:19:48.270 test 1
3           2013-10-04 11:19:48.273 test 3

quote:

---

Originally posted by Hinduson

Please how do I write the queries for the questions Below?

*QuantityReceived should be added to QuantityInHand in the Items table.
*When a record is inserted into the table,QuantityInHand in the Items table should be updated automatically


Best Regards.

---