June 6, 2008 at 8:50 pm
Dear All
I am doing a restaurant order project
order table design as follows
OrderID(int PK), Menu1_Price(money, allow null), Menu2_Price(money, allow null), Menu3_Price(money allow null), Discount(int allow null), TotalPrice(money)
Where customer can choose more than one menu at the same time like if customer choose menu1 and menu2 then i want to put total items cost on menu1_Price and menu2_Price depend what they order. And TotalPrice column is a computed column value combination of menu1, menu2, menu3 and discount
orderDetails table as follows
OrderID (int, FK), OrderType( it can be menu1, menu2 or menu3), ItemID, Price
Value as follows
100 Menu1 ITEM1 3.50
100 Menu1 ITEM2 4.50
100 Menu2 ITEM3 5.00
100 Menu2 ITEM4 2.50
100 Menu3 ITEM4 3.00
I have an OrderID 100 into Order Table
What I want to do
Auto check orderID from OrderDetails table and put the price summation into Order table of these column Menu1_Price, Menu2_Price, Menu3_Price Order table
Any instruction will be really helpful
June 6, 2008 at 11:59 pm
Hi Sarfaraj..
What u want as output from the order table???
If you want to get the Bill information as per menu wise means, then you can use group by to compute the price details in menu wise..
RamKumar
Ramkumar . K
Senior Developer
######################
No Surrender... No Give Up....
######################
June 7, 2008 at 12:13 am
Hi ramkumar
Thanks for your reply, is it possible to get the menu total price automatically?
If yes then how?
Just think Menu1_Price is compute column and would like to get summation from orderDetails table
Thanks
June 7, 2008 at 12:19 am
hi..
You can use PIVOT for this need..
Just try that.. if not i ll post an example for you..
Ramkumar . K
Senior Developer
######################
No Surrender... No Give Up....
######################
June 7, 2008 at 12:23 am
Hi..
Just try like below example..
This is working fine..
-- Suppress data loading messages
SET NOCOUNT ON
-- Create Sample Data using a Table Varable
DECLARE @OrderDetail TABLE
(orderid int,
productname varchar(30),
productqty int)
-- Load Sample Data
INSERT INTO @OrderDetail VALUES (1, 'Gift Card', 2)
INSERT INTO @OrderDetail VALUES (1, 'Shipping', 1)
INSERT INTO @OrderDetail VALUES (2, 'Gift Card', 2)
INSERT INTO @OrderDetail VALUES (2, 'T-Shirt', 2)
INSERT INTO @OrderDetail VALUES (2, 'Shipping', 2)
--Query to Retrieve Desired Data
--SELECT orderid, [Gift Card], [T-Shirt], [Shipping]
SELECT *
FROM
(
SELECT orderid, productname, productqty
FROM @OrderDetail) AS source
PIVOT
(
SUM(ProductQty)
FOR ProductName IN ([Gift Card], [T-Shirt], [Shipping])
) as pvt
SELECT * FROM @OrderDetail
--select * from tbl_dept
--The Below statements are working fine
--select * from (
--select code,[order],reg_year
--from tbl_dept )as Source
--Pivot
--(
-- Max([Order])
-- For Reg_year in( [2004])
--) as Pvt
Ramkumar . K
Senior Developer
######################
No Surrender... No Give Up....
######################
June 7, 2008 at 12:30 am
ramkumar (6/7/2008)
Hi..Just try like below example..
This is working fine..
-- Suppress data loading messagesSET NOCOUNT ON
-- Create Sample Data using a Table Varable
DECLARE @OrderDetail TABLE
(orderid int,
productname varchar(30),
productqty int)
-- Load Sample Data
INSERT INTO @OrderDetail VALUES (1, 'Gift Card', 2)
INSERT INTO @OrderDetail VALUES (1, 'Shipping', 1)
INSERT INTO @OrderDetail VALUES (2, 'Gift Card', 2)
INSERT INTO @OrderDetail VALUES (2, 'T-Shirt', 2)
INSERT INTO @OrderDetail VALUES (2, 'Shipping', 2)
--Query to Retrieve Desired Data
--SELECT orderid, [Gift Card], [T-Shirt], [Shipping]
SELECT *
FROM
(
SELECT orderid, productname, productqty
FROM @OrderDetail) AS source
PIVOT
(
SUM(ProductQty)
FOR ProductName IN ([Gift Card], [T-Shirt], [Shipping])
) as pvt
SELECT * FROM @OrderDetail
--select * from tbl_dept
--The Below statements are working fine
--select * from (
--select code,[order],reg_year
--from tbl_dept )as Source
--Pivot
--(
-- Max([Order])
-- For Reg_year in( [2004])
--) as Pvt
Dear Rum
Thnaks for your quick reply
I am going to sleep now, I will have a look in afternoon if I get any problem then I will let you know, please left your email address I will mail you regarding my problem
Thanks Again
Have a nice weekend
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply