We help IT Professionals succeed at work.

SQL Query help

Medium Priority
561 Views
Last Modified: 2012-05-12
Hello there,

I have got this productid 6618 which has price change on different dates(col date). now when i do a query to return me the qty between specific dates it returns all the 3 prices,but i want to know the price with which it was sold on that date.how do i get that.please help

cheers
Zolf
11-5-2011-12-36-50-PM.png
Comment
Watch Question

sarabhaiDeveloper
CERTIFIED EXPERT

Commented:
I want to see your SalePricing joins please.

Commented:
Can you send the schema of all the three tables and explain in brief how you input data in these tables.
This is happening because of joining. The join column needs some other condition that prevents it from showing everything.
Armand GSenior Developer
CERTIFIED EXPERT

Commented:
You need to restructure your SQL statement as such it would return the latest pricing and date at the time of receipt which means that the pricing date should be before the receipt date was issued. See code below (there's an inner query for the pricing and date field) which gives your answer:

SELECT dbo.DeliveryOrderDetail.productId, dbo.Receipt.receiptDate, dbo.SaleDetail.quantity,

(SELECT TOP 1 dbo.SalesPricing.price FROM dbo.SalesPricing WHERE dbo.SalesPricing.productId = dbo.DeliveryOrderDetail.productId AND dbo.SalesPricing.[date] <= dbo.Receipt.receiptDate ORDER BY dbo.SalesPricing.[date] DESC) price,
(SELECT TOP 1 dbo.SalesPricing.[date] FROM dbo.SalesPricing WHERE dbo.SalesPricing.productId = dbo.DeliveryOrderDetail.productId AND dbo.SalesPricing.[date] <= dbo.Receipt.receiptDate ORDER BY dbo.SalesPricing.[date] DESC) [date],

FROM dbo.SaleDetail INNER JOIN dbo.DeliveryOrderDetail ON (dbo.SaleDetail.orderDetailId = dbo.DeliveryOrderDetail.id)
INNER JOIN dbo.Sale ON (dbo.Sale.id = dbo.SaleDetail.saleId)
INNER JOIN dbo.Receipt ON (dbo.Sale.invoiceReceiptId = dbo.Receipt.id)

Author

Commented:

i have attached the schema diagram to help you'll


armchang:

when i run your query i get error.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'FROM'.
11-6-2011-9-03-15-AM.png
Armand GSenior Developer
CERTIFIED EXPERT

Commented:
I've removed a comma which isn't supposed to be there and changed Salespricing.ProductId to finishProductId:


SELECT dbo.DeliveryOrderDetail.productId, dbo.Receipt.receiptDate, dbo.SaleDetail.quantity,

(SELECT TOP 1 dbo.SalesPricing.price FROM dbo.SalesPricing WHERE dbo.SalesPricing.finishProductId = dbo.DeliveryOrderDetail.productId AND dbo.SalesPricing.[date] <= dbo.Receipt.receiptDate ORDER BY dbo.SalesPricing.[date] DESC) price,
(SELECT TOP 1 dbo.SalesPricing.[date] FROM dbo.SalesPricing WHERE dbo.SalesPricing.finishProductId = dbo.DeliveryOrderDetail.productId AND dbo.SalesPricing.[date] <= dbo.Receipt.receiptDate ORDER BY dbo.SalesPricing.[date] DESC) [date]

FROM dbo.SaleDetail INNER JOIN dbo.DeliveryOrderDetail ON (dbo.SaleDetail.orderDetailId = dbo.DeliveryOrderDetail.id)
INNER JOIN dbo.Sale ON (dbo.Sale.id = dbo.SaleDetail.saleId)
INNER JOIN dbo.Receipt ON (dbo.Sale.invoiceReceiptId = dbo.Receipt.id)

Author

Commented:

thanks,it worked,but i need to filter the query to these conditions,can you please help me to add them

WHERE
    dbo.Receipt.receiptDate >= '2011-09-23'
AND dbo.Receipt.receiptDate <= '2011-10-23'
AND dbo.Tafsil.id = 6618

Author

Commented:

my original query is like this

SELECT
    dbo.DeliveryOrderDetail.productId,
    dbo.Tafsil.description,
    dbo.Receipt.receiptDate,
    dbo.SaleDetail.quantity,
    dbo.SalesPricing.price,
    dbo.SalesPricing.date
FROM
    dbo.SaleDetail
INNER JOIN
    dbo.DeliveryOrderDetail
ON
    (
        dbo.SaleDetail.orderDetailId = dbo.DeliveryOrderDetail.id)
INNER JOIN
    dbo.Sale
ON
    (
        dbo.SaleDetail.saleId = dbo.Sale.id)
INNER JOIN
    dbo.Receipt
ON
    (
        dbo.Sale.invoiceReceiptId = dbo.Receipt.id)
INNER JOIN
    dbo.Tafsil
ON
    (
        dbo.DeliveryOrderDetail.productId = dbo.Tafsil.id)
INNER JOIN
    dbo.FinishProduct
ON
    (
        dbo.Tafsil.id = dbo.FinishProduct.tafsilId)
INNER JOIN
    dbo.SalesPricing
ON
    (
        dbo.FinishProduct.id = dbo.SalesPricing.finishProductId)
WHERE
    dbo.Receipt.receiptDate >= '2011-09-23'
AND dbo.Receipt.receiptDate <= '2011-10-23'
AND dbo.Tafsil.id = 6618

ORDER BY
    dbo.Receipt.receiptDate ASC ;
Armand GSenior Developer
CERTIFIED EXPERT

Commented:
So how about this query:

SELECT dbo.DeliveryOrderDetail.productId, dbo.Receipt.receiptDate, dbo.SaleDetail.quantity,

(SELECT TOP 1 dbo.SalesPricing.price FROM dbo.SalesPricing WHERE dbo.SalesPricing.finishProductId = dbo.DeliveryOrderDetail.productId AND dbo.SalesPricing.[date] <= dbo.Receipt.receiptDate ORDER BY dbo.SalesPricing.[date] DESC) price,
(SELECT TOP 1 dbo.SalesPricing.[date] FROM dbo.SalesPricing WHERE dbo.SalesPricing.finishProductId = dbo.DeliveryOrderDetail.productId AND dbo.SalesPricing.[date] <= dbo.Receipt.receiptDate ORDER BY dbo.SalesPricing.[date] DESC) [date]

FROM dbo.SaleDetail INNER JOIN dbo.DeliveryOrderDetail ON (dbo.SaleDetail.orderDetailId = dbo.DeliveryOrderDetail.id)
INNER JOIN dbo.Sale ON (dbo.Sale.id = dbo.SaleDetail.saleId)
INNER JOIN dbo.Receipt ON (dbo.Sale.invoiceReceiptId = dbo.Receipt.id)
INNER JOIN dbo.Tafsil ON (dbo.DeliveryOrderDetail.productId = dbo.Tafsil.id)
INNER JOIN dbo.FinishProduct ON (dbo.Tafsil.id = dbo.FinishProduct.tafsilId)
WHERE
dbo.Receipt.receiptDate >= '2011-09-23'
AND dbo.Receipt.receiptDate <= '2011-10-23'
AND dbo.Tafsil.id = 6618

ORDER BY dbo.Receipt.receiptDate ASC ;

Author

Commented:

now it returns all null for those 2 col.please see the shot
11-6-2011-10-08-27-AM.gif
Armand GSenior Developer
CERTIFIED EXPERT

Commented:
How about this one:

SELECT dbo.DeliveryOrderDetail.productId, dbo.Receipt.receiptDate, dbo.SaleDetail.quantity,

(SELECT TOP 1 dbo.SalesPricing.price FROM dbo.SalesPricing WHERE dbo.SalesPricing.finishProductId = dbo.DeliveryOrderDetail.productId AND dbo.SalesPricing.[date] <= dbo.Receipt.receiptDate ORDER BY dbo.SalesPricing.[date] DESC) price,
(SELECT TOP 1 dbo.SalesPricing.[date] FROM dbo.SalesPricing WHERE dbo.SalesPricing.finishProductId = dbo.DeliveryOrderDetail.productId AND dbo.SalesPricing.[date] <= dbo.Receipt.receiptDate ORDER BY dbo.SalesPricing.[date] DESC) [date]

FROM dbo.SaleDetail INNER JOIN dbo.DeliveryOrderDetail ON (dbo.SaleDetail.orderDetailId = dbo.DeliveryOrderDetail.id)
INNER JOIN dbo.Sale ON (dbo.Sale.id = dbo.SaleDetail.saleId)
INNER JOIN dbo.Receipt ON (dbo.Sale.invoiceReceiptId = dbo.Receipt.id)
INNER JOIN dbo.Tafsil ON (dbo.DeliveryOrderDetail.productId = dbo.Tafsil.id)

WHERE
dbo.Receipt.receiptDate >= '2011-09-23'
AND dbo.Receipt.receiptDate <= '2011-10-23'
AND dbo.Tafsil.id = 6618

ORDER BY dbo.Receipt.receiptDate ASC ;

Author

Commented:

no luck,same result

Author

Commented:

you see for e.g.

the Product price was 100 from 1/1/2009 till 1/6/2009 then from 2/6/2009 the price changed to 120 and then again on 5/11/2009 the price was changed to 150.
now i want to get the total qty and the price of the products sold between say 1/11/2009 till 20/12/2009. between these dates some of the priduct was sold with price 120 and some with 150.i want to get these records.please help
Armand GSenior Developer
CERTIFIED EXPERT

Commented:
Can you post complete fields used in Salespricing, Tafsil and FinishProduct table?

Author

Commented:
Senior Developer
CERTIFIED EXPERT
Commented:
Remodified the query as to the link of the SalesPricing should be at the FinishProduct table not at the DeliveryOrderDetail:

SELECT dbo.DeliveryOrderDetail.productId, dbo.Receipt.receiptDate, dbo.SaleDetail.quantity,

(SELECT TOP 1 dbo.SalesPricing.price FROM dbo.SalesPricing WHERE dbo.SalesPricing.finishProductId = dbo.FinishProduct.id AND dbo.SalesPricing.[date] <= dbo.Receipt.receiptDate ORDER BY dbo.SalesPricing.[date] DESC) price,
(SELECT TOP 1 dbo.SalesPricing.[date] FROM dbo.SalesPricing WHERE dbo.SalesPricing.finishProductId = dbo.FinishProduct.id AND dbo.SalesPricing.[date] <= dbo.Receipt.receiptDate ORDER BY dbo.SalesPricing.[date] DESC) [date]

FROM dbo.SaleDetail INNER JOIN dbo.DeliveryOrderDetail ON (dbo.SaleDetail.orderDetailId = dbo.DeliveryOrderDetail.id)
INNER JOIN dbo.Sale ON (dbo.Sale.id = dbo.SaleDetail.saleId)
INNER JOIN dbo.Receipt ON (dbo.Sale.invoiceReceiptId = dbo.Receipt.id)
INNER JOIN dbo.Tafsil ON (dbo.DeliveryOrderDetail.productId = dbo.Tafsil.id)
INNER JOIN dbo.FinishProduct ON (dbo.Tafsil.id = dbo.FinishProduct.tafsilId)
WHERE
dbo.Receipt.receiptDate >= '2011-09-23'
AND dbo.Receipt.receiptDate <= '2011-10-23'
AND dbo.Tafsil.id = 6618

ORDER BY dbo.Receipt.receiptDate ASC ;

Author

Commented:

thanks a lot for your help. it seems to be working. can you please tell expalin what you did in order to return the price of the product for that date.

Author

Commented:

specially this
(SELECT TOP 1 dbo.SalesPricing.[date] FROM dbo.SalesPricing WHERE dbo.SalesPricing.finishProductId = dbo.FinishProduct.id AND dbo.SalesPricing.[date] <= dbo.Receipt.receiptDate ORDER BY dbo.SalesPricing.[date] DESC) [date]

when we say dbo.SalesPricing.[date] <= dbo.Receipt.receiptDate

i was in the impression that it will return all of the old dates before,but it returned just one date,how come

Author

Commented:
cheers
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>i was in the impression that it will return all of the old dates before,but it returned just one date,how come <<
That would be because of the TOP 1 clause.

Author

Commented:

acperkins:

thanks for your feedback.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.