Function Return value from other table

Question

Post reply

Function Return value from other table

Sarfaraj Ahmed

SSC-Addicted

Points: 447
More actions
June 6, 2008 at 8:50 pm

#187725

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ramkumar.K Default port Points: 1405 More actions · Answer 1

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

Sarfaraj Ahmed SSC-Addicted Points: 447 More actions · Answer 2

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

Ramkumar.K Default port Points: 1405 More actions · Answer 3

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

Ramkumar.K Default port Points: 1405 More actions · Answer 4

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

Sarfaraj Ahmed SSC-Addicted Points: 447 More actions · Answer 5

ramkumar (6/7/2008)
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

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