Query in SQL Server - Easy one? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

Transact-SQL (2000)

Query in SQL Server - Easy one?

Author

Topic

monchoman
Starting Member

6 Posts

Posted - 2007-10-06 : 21:11:17

Hi, I am having a problem to make a simple SQL Server 2000 query.

I have a INVOICES table with invoices, a n INVOICE_DETAIL table with details of the products of each invoice, and a PRICES table with the prices for each product. Each price has a date, which refers to the starting date of that price for that product (so, each product may have several prices, in different dates).

I just need to know the total amount of each Invoice, like this:

Invoice_id | Total_amount
-------------------------
1 | 150.34
2 | 22.52
5 | 5464.32

Please can you send me the SQL Server 2000 query I should use?

Here are the table structures:

Table INVOICES:
Keys: Invoice_id (bigint)
Fields: Date (datetime)
Customer_id (bigint)

Table INVOICE_DETAIL:
Keys: Invoice_id (bigint)
Product_id (bigint)
Fields: Quantity (int)

Table PRICES:
Keys: Product_id (bigint)
Date_from (datetime)
Fields: Price (money)

Thanks to all and best regards,
German

monchoman
Starting Member

6 Posts

Posted - 2007-10-07 : 00:13:48

That is fine, but where do you check the invoice date? Remember that the product price depends on the invoice date.

Regards,
German

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-10-07 : 00:16:46

I saw that after and retracted it as soon as I noticed the requirement.

Future guru in the making.

monchoman
Starting Member

6 Posts

Posted - 2007-10-07 : 00:19:27

Ok, no prob... I was starting to think I was crazy when I didn't see your post anymore.. hehe

Do you know how to do it?

German

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-10-07 : 00:39:51

interesting that CustomerID is a bigint. do you have so many customers that an int32 would overflow? same story with products and invoices.

wishful thinking perhaps.

I might give this a crack if you posted DDL for all tables, as well as sample data insert statements, and expected output given the sample data. I am too lazy to do that part myself...

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-10-07 : 00:43:55

It would have helped had the Invoice_detail table had a date field also. I am puzzled trying to figure out how to do it without that.

Future guru in the making.

monchoman
Starting Member

6 Posts

Posted - 2007-10-07 : 01:51:22

Hehehe, yes, the bigint types are just for prototyping purposes..

I need to make a correction to my request. It is not necessary to get the Total_billing result, it will be enough with getting the total price for each line detail of the avery invoice.

All I have is an access database where I was able to do it. I don't know how to attach a file to the post, so I can only transcript the SQL command for the query:

---
SELECT Billing_detail.BILLING_ID, Billing_detail.PRODUCT_ID, Last([quantity]*[price]) AS Detail_Price
FROM Prices INNER JOIN (Billing INNER JOIN Billing_detail ON Billing.BILLING_ID = Billing_detail.BILLING_ID) ON Prices.PRODUCT_ID = Billing_detail.PRODUCT_ID
WHERE (((Prices.DATE)<[billing].[date]))
GROUP BY Billing_detail.BILLING_ID, Billing_detail.PRODUCT_ID;
---

I have to do that exact SQL query in SQL Server 2000, but it doesn't work.

If you want, give me your emails and I will send you the Access database.

Regards,
German

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-10-07 : 10:36:28

This should do the same as your Access query:


SELECT  invoice_detail.invoice_ID,
        invoice_detail.PRODUCT_ID,
        max([quantity]*[price]) AS Detail_Price
FROM    Prices
INNER JOIN (invoices
INNER JOIN invoice_detail
ON      invoices.invoice_ID = invoice_detail.invoice_ID)
ON      Prices.PRODUCT_ID   = invoice_detail.PRODUCT_ID
WHERE (((Prices.DATE_from)<[invoices].[date]))
GROUP BY invoice_detail.invoice_ID,
        invoice_detail.PRODUCT_ID;

Future guru in the making.

monchoman
Starting Member

6 Posts

Posted - 2007-10-07 : 15:09:43

Zoroaster, thank you, I've tested it, but that only uses the maximum price for that product, instead of using the latest one.

Here is the table definition SQL commands:

CREATE TABLE [dbo].[Billing] (
[BILLING_ID] [int] NOT NULL ,
[DATE] [datetime] NULL ,
[CUSTOMER_ID] [smallint] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Billing_detail] (
[BILLING_ID] [int] NULL ,
[PRODUCT_ID] [smallint] NULL ,
[QUANTITY] [smallint] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Prices] (
[PRODUCT_ID] [int] NOT NULL ,
[DATE] [datetime] NOT NULL ,
[PRICE] [real] NULL
) ON [PRIMARY]
GO

And here is some data:

BILLING:
Billing_Id, Date, Customer_Id
385920,01/07/2004 00:03:48,2298

BILLING_DETAIL:
Billing_Id,Product_Id,Quantity
385920,1,1

PRICES:
Product_Id,Date,Price
1,01/01/2002 09:00:50,3
1,01/01/2003 10:00:50,2
1,01/01/2004 15:00:54,1

The required query should return:
Invoice_Id,Product_Id,Detail_Price
385920,1,1

But when using the proposed query, it returns:
Invoice_Id,Product_Id,Detail_Price
385920,1,3

Plesae let me know if you don't understand me...

Thanks and regards,
German

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-10-07 : 15:29:01

in the future, you should provide sample data in this form:

insert billing
select 385920,'2004-01-07 00:03:48',2298 union all
select -- add more here

that way we can just run the code to insert it. you, as the question asker ought to do this work so we don't have to!

anyway I guess this will do it:


select * from 
billing_detail bd
join billing b on bd.BILLING_ID=b.BILLING_ID
join prices p on p.DATE=(select max(DATE) from prices where DATE < b.DATE)
             and p.PRODUCT_ID=bd.PRODUCT_ID

monchoman
Starting Member

6 Posts

Posted - 2007-10-09 : 19:42:44

Jezemin, thanks for your answer. It does work, but only for one product. When there are more than one product in the billing_detail and price table, it doesn't do it properly.

Regardless that, your query gave me an idea on how to build a new query that can handle any situation. I post it here as the solution to the request, in case someone wants to test it:

SELECT TOP 100 PERCENT Billing_Detail.BILLING_ID, Billing_Detail.PRODUCT_ID, Billing_Detail.QUANTITY,
Billing_Detail.QUANTITY * Prices.PRICE AS Detail_Price
FROM Billing_Detail INNER JOIN
(SELECT Billing.BILLING_ID, Billing_detail.PRODUCT_ID, MAX(Prices.[DATE]) AS PRICE_DATE
FROM Billing_detail INNER JOIN
Prices ON Billing_detail.PRODUCT_ID = Prices.PRODUCT_ID INNER JOIN
Billing ON Billing_detail.BILLING_ID = Billing.BILLING_ID AND Prices.[DATE] <= Billing.[DATE]
GROUP BY Billing_detail.PRODUCT_ID, Billing.BILLING_ID) p ON Billing_Detail.BILLING_ID = p.BILLING_ID INNER JOIN
Prices ON Billing_Detail.PRODUCT_ID = Prices.PRODUCT_ID AND p.PRICE_DATE = Prices.[DATE] AND
p.PRODUCT_ID = Prices.PRODUCT_ID
ORDER BY Billing_Detail.BILLING_ID

Thanks again to all and regards,
German

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-10-09 : 20:04:54

quote:
Originally posted by monchoman

Jezemin, thanks for your answer. It does work, but only for one product. When there are more than one product in the billing_detail and price table, it doesn't do it properly.

I blame your paltry sample data!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources