January 20, 2009 at 11:11 am
Requirement:
There is a product table and prodcutpackage table. Each package id is also a productid. Now I want to calculate in a package how many products are there and what is the total cost of each package. And I want to add a column calculating what is the actual cost of each individual productid in a package versus how much it cost if we buy some product in in package.
Packageid productid unitprice totalprice
1 001 20.00
1 002 25.00 45.00
2 001 20.00
2 003 50.00 70.00
Table Structures:
Orderdetail Table
CREATE TABLE [dbo].[OrderDetail](
[OrderDetailID] [int] IDENTITY(1,1) NOT NULL,
[OrderID] [int] NULL,
[ProductID] [int] NULL,
[PackageProductID] [int] NULL,
[Quantity] [int] NULL,
[CustomerPrice] [money],
[TaxablePrice] [money] NULL,
[EstimatedValue] [money] NULL ,
[CustomsValue] [money] NULL,
[SubTotal] [money] NULL,
[FreightCharge] [money] NULL,
[TaxAmount] [money] NULL,
[TotalWithTax] [money] NULL,
[CreditAmount] [money] NULL,
[CreditDate] [datetime] NULL,
[ARDate] [datetime] NULL,
CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED
(
[OrderDetailID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
ProdcutTable:
CREATE TABLE [dbo].[Product](
[ProductID] [int] IDENTITY(1,1) NOT NULL,
[CourseID] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[ProductName] [varchar](200) COLLATE Latin1_General_CI_AI NULL,
[ProductCat] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[ProductGroup] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[ProductType] [varchar](50) COLLATE Latin1_General_CI_AI NULL ,
[ProductDesc] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[UnitPrice] [money] NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[ProductID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ProductPackage Table:
CREATE TABLE [dbo].[ProductPackage](
[PackageProductID] [int] IDENTITY(1,1) NOT NULL,
[PackageID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[Percentage] [float] NOT NULL,
[ProductOption] [int] NULL,
[AddonPrice] [money] NULL,
CONSTRAINT [PK_ProductPackage_1] PRIMARY KEY CLUSTERED
(
[PackageProductID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Sample data in three xls files.
Should have any quesstions please get back to me
Help appreciated
January 20, 2009 at 11:16 am
Before anyone starts doing your work for you, would you please show us what you have done so far to meet your requirements? What problems are you having with your code? Where do you need help figuring out what may be the problem with what you are trying to accomplish?
January 20, 2009 at 11:18 am
What you're asking for is a very, very simple Select statement. Is there something more to this, or do you not know how to write Select statements?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 20, 2009 at 11:23 am
i tried to get all the productid within a package (and apackage itself is also a product) with the following query.
WITH RecursiveTable_CTE(ProductID, PackageID)
AS
(
SELECT pp.ProductID, pp.PackageID FROM ProductPackage pp
UNION ALL
SELECT pp.productID, pp.PackageID
FROM ProductPackage pp
INNER JOIN RecursiveTable_CTE rt
ON pp.PackageID=rt.ProductID
)
SELECT * FROM RecursiveTable_CTE
option (maxrecursion 0);
January 20, 2009 at 11:24 am
WITH RecursiveTable_CTE(ProductID, PackageID)
AS
(
SELECT pp.ProductID, pp.PackageID FROM ProductPackage pp
UNION ALL
SELECT pp.productID, pp.PackageID
FROM ProductPackage pp
INNER JOIN RecursiveTable_CTE rt
ON pp.PackageID=rt.ProductID
)
SELECT * FROM RecursiveTable_CTE;
option (maxrecursion 0);
i tried to do this to get all product id within a package.
January 20, 2009 at 11:27 am
So, within packages, you have sub-packages as products? That's what the CTE seems to indicate. Is that correct? (Makes sense, just making sure I'm not missing something here.)
What does you CTE not do or do incorrectly?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 20, 2009 at 11:38 am
please see updated query:
WITH RecursiveTable_CTE(ProductID, PackageID,unitprice)
AS
(SELECT pp.ProductID, pp.PackageID,p.unitprice FROM ProductPackage pp
join product p on pp.productid =p.productid
UNION ALL
SELECT pp.productID, pp.PackageID,p.unitprice FROM ProductPackage pp
join product p on pp.productid =p.productid
INNER JOIN RecursiveTable_CTE rt
ON pp.PackageID=rt.ProductID
)
SELECT * FROM RecursiveTable_CTE
option (maxrecursion 0);
the desired output will something like this
packageid productid unitprice sum(unitprice)group by each packageid
January 20, 2009 at 11:39 am
Your CTE looks like it should work. Is there a problem with it?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 20, 2009 at 11:43 am
the desired output i am expecting will something like this
packageid productid unitprice sum(unitprice)group by each packageid
January 20, 2009 at 12:21 pm
Add a second CTE after the first one, have it sum up the prices from the first one, then in your final query, join the two together.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 20, 2009 at 12:24 pm
Query Please.
Thanks
January 20, 2009 at 12:54 pm
srathna77 (1/20/2009)
Query Please.Thanks
Pretentious aren't we. GSquared pointed you in the direction you need to go, try writing it yourself first. If you have problems, come back with what you tried and what the problem is you are encountering.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply