Display invoice total - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Display invoice total

Author

Topic

Arend
Starting Member

17 Posts

Posted - 2007-02-06 : 08:36:38

Hi,

I need assistance with the following please.

I have to display the total for an invoice. The price for the products change over time.

I want the invoice date to correspond with the current period's price.

Thus if invoice date is y, it should calculate using the price between x and z.

Thanks,

Arend

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-06 : 08:40:12

Please post some tangible sample data, expected output and table structures to get accurate replies.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Arend
Starting Member

17 Posts

Posted - 2007-02-07 : 03:35:50

Hi,

I have to display the details and the total cost of a specific invoice. This is the code i have sofar.

CREATE PROCEDURE sp_DisplayInvoice
@invoice_id VARCHAR(10)
AS
Select * FROM INVOICE
WHERE invoice_id = @invoice_id

SELECT product_code, materialdetail_quantity
FROM MATERIALDETAIL
WHERE invoice_id = @invoice_id

SELECT SUM(materialdetail_quantity * price_price) AS TotalCost
FROM MATERIALDETAIL, PRICE
WHERE invoice_id = @invoice_id
GO
*************
I am having trouble with my SUM code. the problem i have is that i have a price table with dates for different prices for a specific product. i have to calculate the cost for the products where the issue date of the invoice falls between the start and end date of the prices for the specific products.

for instance, product A101 has the price 3 for the period 24/07 to 24/08 and then the price change to 5 for the period 24/9 to 24/10. my invoice is issued on 1/08 so that mean i have to calculate the product with the price of 3.

how do i do it? also if i can change my code to work better please assist me.

Thanx a million.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-07 : 03:41:37

Post some DDL for the PRICE table and MaterialDetail table, please. Without this information, it is impossible for us to know your business logic.
As of now, you are producing a Cartesian Product (CROSS JOIN) for all invoice's quantities with all prices...

For the second query, change to this just in case you have several lines for same product code

SELECT product_code, sum(materialdetail_quantity) as materialdetail_quantity
FROM MATERIALDETAIL
WHERE invoice_id = @invoice_id
group by product_code

Peter Larsson
Helsingborg, Sweden

Arend
Starting Member

17 Posts

Posted - 2007-02-07 : 03:55:03

Price Table with columns: productCode, startDate, endDate, price

MaterialDetail Table with columns: invoiceId, productCode, quantity

Invoice Table with columns: id, date, clientId

Hope this helps...

Thanks for the assisatance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-07 : 04:02:04

Something like this

SELECT	    SUM(md.Quantity * p.Price) AS TotalCost
FROM	    Invoice AS i
INNER JOIN  MaterialDetail AS md ON md.InvoiceID = i.ID
INNER JOIN  Price AS p ON p.ProductCode = md.ProductCode AND p.StartDate <= i.Date AND p.EndDate >= i.Date
WHERE	    i.ID = @Invoice_ID

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-07 : 04:03:48

Something like this?

Select p.ProductCode, sum(md.Quantity * p.price)
from MaterialDetail as MD JOIN Price p on md.productcode  = p.productcode
JOIN Invoice i on md.invoiceid = i.md.invoiceid
where md.invoiceid = @invoice_id
Group by p.ProductCode
Having i.[date] between p.StartDate and p.EndDate

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Arend
Starting Member

17 Posts

Posted - 2007-02-07 : 04:12:20

Thank you guys. I am going to try it right away.

God bless.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources