Link to home
Start Free TrialLog in
Avatar of Zolf
ZolfFlag for United Arab Emirates

asked on

MSSQL Query help

Hello there,

I have this query in mssql 2012 and I want to sum the products which has unitprice 0 to its product which has unitprice, but I am not able to do that becasue in GROUP BY the quantity of them are different. Can somebody help me with a solution for this issue.


User generated image
SELECT
    dbo.Branch.name                                   AS Branch,
    dbo.Visitor.firstName1 +' '+dbo.Visitor.lastName1 AS Visitor,
    CAST(dbo.Sale.created AS DATE)                    AS SalesDate,
    CAST(dbo.DeliveryOrder.paymentDate AS DATE) AS SalesPaymentDate,
    RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) +
    '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS ProductCode,
    dbo.Tafsil.description                               AS ProductName,
    SUM(dbo.SaleDetail.quantity)                         AS Quantity,
    ISNULL(dbo.Batch.unitPrice,0)                        AS UnitPrice
FROM
    dbo.DeliveryOrder
INNER JOIN
    dbo.Visitor
ON
    (
        dbo.DeliveryOrder.visitorId = dbo.Visitor.id)
INNER JOIN
    dbo.Sale
ON
    (
        dbo.DeliveryOrder.id = dbo.Sale.orderId)
INNER JOIN
    dbo.Customer
ON
    (
        dbo.DeliveryOrder.customerId = dbo.Customer.id)
INNER JOIN
    dbo.Receipt
ON
    (
        dbo.Sale.invoiceReceiptId = dbo.Receipt.id)
INNER JOIN
    dbo.SaleDetail
ON
    (
        dbo.Sale.id = dbo.SaleDetail.saleId)
INNER JOIN
    dbo.Batch
ON
    (
        dbo.SaleDetail.batchId = dbo.Batch.id)
INNER JOIN
    dbo.Tafsil
ON
    (
        dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Receipt.branchId = dbo.Branch.id)
INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
INNER JOIN
    dbo.ReceiveChequeSaleLink
ON
    (
        dbo.Sale.id = dbo.ReceiveChequeSaleLink.saleId)
INNER JOIN
    dbo.ReceiveChequeSale
ON
    (
        dbo.ReceiveChequeSaleLink.chequeId = dbo.ReceiveChequeSale.id)
INNER JOIN
    dbo.ReceiveCheque
ON
    (
        dbo.ReceiveChequeSale.id = dbo.ReceiveCheque.receiveChequeSaleId)
INNER JOIN
    dbo.Receipt Receipt_alias2
ON
    (
        dbo.ReceiveChequeSale.receiptId = Receipt_alias2.id)
WHERE
    dbo.Receipt.receiptDate >= '2015-03-21'
GROUP BY
    dbo.Visitor.firstName1,
    dbo.Visitor.lastName1,
    dbo.DeliveryOrder.paymentDate,
    dbo.Sale.created,
    dbo.Batch.unitPrice,
    dbo.Tafsil.code,
    dbo.Tafsil.description,
    dbo.Branch.name,
    dbo.Kol.code,
    dbo.Mohin.code

Open in new window

Avatar of Mlanda T
Mlanda T
Flag of South Africa image

SELECT
    dbo.Branch.name                                   AS Branch,
    dbo.Visitor.firstName1 +' '+dbo.Visitor.lastName1 AS Visitor,
    CAST(dbo.Sale.created AS DATE)                    AS SalesDate,
    CAST(dbo.DeliveryOrder.paymentDate AS DATE) AS SalesPaymentDate,
    RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) +
    '' + RIGHT('0000' + CAST(tafsil.code AS VARCHAR), 4) AS ProductCode,
    dbo.Tafsil.description                               AS ProductName,
    SUM(dbo.SaleDetail.quantity)                         AS Quantity,
    MAX(ISNULL(dbo.Batch.unitPrice,0))                       AS UnitPrice
FROM
    dbo.DeliveryOrder
INNER JOIN
    dbo.Visitor
ON
    (
        dbo.DeliveryOrder.visitorId = dbo.Visitor.id)
INNER JOIN
    dbo.Sale
ON
    (
        dbo.DeliveryOrder.id = dbo.Sale.orderId)
INNER JOIN
    dbo.Customer
ON
    (
        dbo.DeliveryOrder.customerId = dbo.Customer.id)
INNER JOIN
    dbo.Receipt
ON
    (
        dbo.Sale.invoiceReceiptId = dbo.Receipt.id)
INNER JOIN
    dbo.SaleDetail
ON
    (
        dbo.Sale.id = dbo.SaleDetail.saleId)
INNER JOIN
    dbo.Batch
ON
    (
        dbo.SaleDetail.batchId = dbo.Batch.id)
INNER JOIN
    dbo.Tafsil
ON
    (
        dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Receipt.branchId = dbo.Branch.id)
INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
INNER JOIN
    dbo.ReceiveChequeSaleLink
ON
    (
        dbo.Sale.id = dbo.ReceiveChequeSaleLink.saleId)
INNER JOIN
    dbo.ReceiveChequeSale
ON
    (
        dbo.ReceiveChequeSaleLink.chequeId = dbo.ReceiveChequeSale.id)
INNER JOIN
    dbo.ReceiveCheque
ON
    (
        dbo.ReceiveChequeSale.id = dbo.ReceiveCheque.receiveChequeSaleId)
INNER JOIN
    dbo.Receipt Receipt_alias2
ON
    (
        dbo.ReceiveChequeSale.receiptId = Receipt_alias2.id)
WHERE
    dbo.Receipt.receiptDate >= '2015-03-21'
GROUP BY
    dbo.Visitor.firstName1,
    dbo.Visitor.lastName1,
    dbo.DeliveryOrder.paymentDate,
    dbo.Sale.created,
    dbo.Batch.unitPrice,
    dbo.Tafsil.code,
    dbo.Tafsil.description,
    dbo.Branch.name,
    dbo.Kol.code,
    dbo.Mohin.code

Open in new window

That will work for what you want.

I seem to think I've seen this question before, and the UnitPrice was still an issue. In this case, we are using MAX on UnitPrice to resolve the issue (though I think we might be over-fitting the query to the current data sample - but it will work).
Avatar of Zolf

ASKER

Thanks for your comments.
How can I get the relevant products unit price based on the sales invoice date.
I have this table called SalesPricing where I store the new price and the date when it should be applied.
Now I have another table sales where I store the productid. this productid is also present in the SalesPricing table.Please see shot.User generated image
Avatar of Zolf

ASKER

and here is my SQL Query for that above result

SELECT
    dbo.Visitor.firstName1,
    dbo.Visitor.lastName1,
    dbo.DeliveryOrder.paymentDate,
    dbo.Sale.created,
    dbo.Sale.grandTotal,
    dbo.Receipt.transactionNumber,
    dbo.ReceiptDetail.credit,
    dbo.ReceiptDetail.quantity    AS Quantity,
    dbo.Branch.name               AS BranchName,
    dbo.Customer.organizationName AS CustomerName,
    Tafsil_alias2.description     AS ProductName,
    dbo.SalesPricing.date         AS SalesPriceDate,
    dbo.SalesPricing.price        AS SalesPrice,
    dbo.Receipt.receiptDate       AS SalesDate
FROM
    dbo.DeliveryOrder
INNER JOIN
    dbo.Visitor
ON
    (
        dbo.DeliveryOrder.visitorId = dbo.Visitor.id)
INNER JOIN
    dbo.Sale
ON
    (
        dbo.DeliveryOrder.id = dbo.Sale.orderId)
INNER JOIN
    dbo.Customer
ON
    (
        dbo.DeliveryOrder.customerId = dbo.Customer.id)
INNER JOIN
    dbo.Receipt
ON
    (
        dbo.Sale.invoiceReceiptId = dbo.Receipt.id)
INNER JOIN
    dbo.ReceiptDetail
ON
    (
        dbo.Receipt.id = dbo.ReceiptDetail.receiptId)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Receipt.branchId = dbo.Branch.id)
INNER JOIN
    dbo.Tafsil Tafsil_alias1
ON
    (
        dbo.ReceiptDetail.tafsilId = Tafsil_alias1.id)
INNER JOIN
    dbo.TafsilLink
ON
    (
        Tafsil_alias1.id = dbo.TafsilLink.targetId)
INNER JOIN
    dbo.Tafsil Tafsil_alias2
ON
    (
        dbo.TafsilLink.sourceId = Tafsil_alias2.id)
INNER JOIN
    dbo.FinishProduct
ON
    (
        Tafsil_alias1.id = dbo.FinishProduct.tafsilId)
INNER JOIN
    dbo.SalesPricing
ON
    (
        dbo.FinishProduct.id = dbo.SalesPricing.finishProductId)
WHERE
    dbo.Receipt.receiptDate >= '2015-03-21'
AND dbo.ReceiptDetail.credit IS NOT NULL
AND dbo.ReceiptDetail.descriptionType = 1
AND dbo.Receipt.transactionNumber = 1099400001 ;

Open in new window

Original (in question) GROUP BY clause is:
GROUP BY
    dbo.Visitor.firstName1,
    dbo.Visitor.lastName1,
    dbo.DeliveryOrder.paymentDate,
    dbo.Sale.created,
   dbo.Batch.unitPrice,
    dbo.Tafsil.code,
    dbo.Tafsil.description,
    dbo.Branch.name,
    dbo.Kol.code,
    dbo.Mohin.code

You will get a NEW ROW if ANY of those columns change in value. The value of UNIT PRICE changes, so you get a new row when it does that. i.e. There is a direct the relationship between the contents of the GROUP BY clause and the number of rows the query produces.

Remove that column from the GROUP BY clause and make it MAX(...) in the select clause will "fix it" but you also ask for more help:

>>"How can I get the relevant products unit price based on the sales invoice date."
From that image I do not know how to do that.
But I could help if you provide data we can use in tables.

Could you please supply a small sample (you can omit/alter any private data)
of the 3 tables involved:
>>product
>>SalesPricing
>>invoice

it is important that these records are accurate samples (particularly date/time information)  but a sample does NOT need to be big. Just a few rows can be sufficient, although each table must have information that relates to the others.
Avatar of Zolf

ASKER

Thanks Paul,please hang on while I prepare those tables
Avatar of Zolf

ASKER

here is the tables exported into excel as rar and also I have attached screenshot of the relationship

User generated imageSales.xls
Receipt.xls
ReceiptDetail.xls
Tafsil.xls
FinishProduct.xls
SalesPricing.xls
Avatar of Zolf

ASKER

AND this is my QUERY

SELECT
    dbo.Visitor.firstName1,
    dbo.Visitor.lastName1,
    dbo.DeliveryOrder.paymentDate,
    dbo.Sale.created,
    dbo.Sale.grandTotal,
    dbo.Receipt.transactionNumber,
    dbo.ReceiptDetail.credit,
    dbo.ReceiptDetail.quantity    AS Quantity,
    dbo.Branch.name               AS BranchName,
    dbo.Customer.organizationName AS CustomerName,
    Tafsil_alias2.description     AS ProductName,
    dbo.SalesPricing.date         AS SalesPriceDate,
    MAX(dbo.SalesPricing.price)        AS SalesPrice,
    dbo.Receipt.receiptDate       AS SalesDate
FROM
    dbo.DeliveryOrder
INNER JOIN
    dbo.Visitor
ON
    (
        dbo.DeliveryOrder.visitorId = dbo.Visitor.id)
INNER JOIN
    dbo.Sale
ON
    (
        dbo.DeliveryOrder.id = dbo.Sale.orderId)
INNER JOIN
    dbo.Customer
ON
    (
        dbo.DeliveryOrder.customerId = dbo.Customer.id)
INNER JOIN
    dbo.Receipt
ON
    (
        dbo.Sale.invoiceReceiptId = dbo.Receipt.id)
INNER JOIN
    dbo.ReceiptDetail
ON
    (
        dbo.Receipt.id = dbo.ReceiptDetail.receiptId)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Receipt.branchId = dbo.Branch.id)
INNER JOIN
    dbo.Tafsil Tafsil_alias1
ON
    (
        dbo.ReceiptDetail.tafsilId = Tafsil_alias1.id)
INNER JOIN
    dbo.TafsilLink
ON
    (
        Tafsil_alias1.id = dbo.TafsilLink.targetId)
INNER JOIN
    dbo.Tafsil Tafsil_alias2
ON
    (
        dbo.TafsilLink.sourceId = Tafsil_alias2.id)
INNER JOIN
    dbo.FinishProduct
ON
    (
        Tafsil_alias1.id = dbo.FinishProduct.tafsilId)
INNER JOIN
    dbo.SalesPricing
ON
    (
        dbo.FinishProduct.id = dbo.SalesPricing.finishProductId)
WHERE
    dbo.Receipt.receiptDate >= '2014-03-21'
AND dbo.ReceiptDetail.credit IS NOT NULL
AND dbo.ReceiptDetail.descriptionType = 1
AND SalesPricing.date < dbo.Receipt.receiptDate 
GROUP BY
dbo.Visitor.firstName1,
    dbo.Visitor.lastName1,
    dbo.DeliveryOrder.paymentDate,
    dbo.Sale.created,
    dbo.Sale.grandTotal,
    dbo.Receipt.transactionNumber,
    dbo.ReceiptDetail.credit,
    dbo.ReceiptDetail.quantity,
    dbo.Branch.name ,
    dbo.Customer.organizationName,
    Tafsil_alias2.description ,
    dbo.SalesPricing.date,
    dbo.Receipt.receiptDate

Open in new window

>>"How can I get the relevant products unit price based on the sales invoice date."

According to the relationship diagram there is no need for dates to be considered.
Why did you ask for dates to be considered?

If they do need to be considered - WHICH date columns are you referring to?
please be specific

e.g. there is a column SalesPricing.Date : I cannot tell from that name what the relevance of that column is
Avatar of Zolf

ASKER

Paul, you see I need to get the unit price of that product for that date when the sales was done.
Avatar of Zolf

ASKER

SalePricing.Date is the date which implies that from this date the new product price has to be used
You can add a date comparison condition to the join where you call in table SalesPricing. I used the [Date] field of that table as you indicated above but had to guess which other date it is compared to. I chose  Receipt.receiptDate but if that is incorrect just change that reference in the join to the table.column or alias.column that is correct.

Query 1:
select
    sp.*, r.receiptDate
from sale s
    inner join Receipt r on s.ReceiptID = r.id
    inner join ReceiptDetail rd on r.id = rd.receiptId
    inner join Tafsil t on rd.tafsilId = t.id
    inner join FinishProduct fp on t.id = fp.tafsilId
    inner join SalesPricing sp on fp.id = sp.finishProductId
                               AND r.receiptDate >= sp.[Date]
;

|   id |                    created |            lastUpdated | createdBy | updatedBy | price |                       date | finishProductId |             receiptDate |
|------|----------------------------|------------------------|-----------|-----------|-------|----------------------------|-----------------|-------------------------|
| 4373 | November, 25 2013 09:01:05 |  May, 06 2014 04:17:31 |        73 |        73 | 11500 | November, 25 2013 00:00:00 |            1388 | March, 28 2015 07:27:50 |
| 4677 |     June, 03 2014 11:22:07 | June, 30 2015 09:28:24 |        73 |        73 | 14754 |     June, 22 2014 00:00:00 |            1388 | March, 28 2015 07:27:50 |

Open in new window


Another approach to this, which will resolves the problem of too many prices is to use CROSS APPLY. Like this

Query 2
select
    ca.*, r.receiptDate
from sale s
    inner join Receipt r on s.ReceiptID = r.id
    inner join ReceiptDetail rd on r.id = rd.receiptId
    inner join Tafsil t on rd.tafsilId = t.id
    inner join FinishProduct fp on t.id = fp.tafsilId
    CROSS APPLY (
       select top (1) sp.id, sp.price, sp.[Date]
       from SalesPricing sp
       where fp.id = sp.finishProductId AND r.receiptDate >= sp.[Date]
       ORDER BY sp.[Date] DESC
       ) ca

|   id | price |                   Date |             receiptDate |
|------|-------|------------------------|-------------------------|
| 4677 | 14754 | June, 22 2014 00:00:00 | March, 28 2015 07:27:50 |
        

Open in new window

The CROSS APPLY approach ensures you get ONLY ONE PRICE which will be the price that is the closest to the receiptdate (or whichever date is the correct one to use).

see: http://sqlfiddle.com/#!6/7308a/9

Details:
CREATE TABLE SalesPricing
    ([id] int, [created] datetime, [lastUpdated] datetime, [createdBy] int
     , [updatedBy] int, [price] int, [date] datetime, [finishProductId] int)
;
    
INSERT INTO SalesPricing
    ([id], [created], [lastUpdated], [createdBy], [updatedBy], [price], [date], [finishProductId])
VALUES
    (4373, '2013-11-25 09:01:05', '2014-05-06 04:17:31', 73, 73, 11500, '2013-11-25 00:00:00', 1388),
    (4677, '2014-06-03 11:22:07', '2015-06-30 09:28:24', 73, 73, 14754, '2014-06-22 00:00:00', 1388),
    (5550, '2015-06-30 06:09:31', '2015-07-21 09:42:10', 73, 73, 17360, '2015-07-01 00:00:00', 1388)
;

CREATE TABLE FinishProduct
    ([id] int, [created] datetime, [lastUpdated] datetime, [createdBy] int, [updatedBy] int
     , [genericName] varchar(4), [ean] varchar(4), [ircNumber] varchar(4), [universalCode] int
     , [localExportType] int, [tafsilId] int, [dosageFormId] int, [pharmaceuticalCategoryId] int
     , [therapeuticalCategoryId] int, [packId] int, [cartonId] int, [storageConditionId] int
     , [specialOfferId] varchar(4), [agreementId] int, [bonusId] varchar(4), [supplierId] int
     , [manufacturerId] int, [shelfLifeId] varchar(4), [brandName] varchar(4), [concentration] int
     , [concentrationUnitId] varchar(4), [valueAddId] int, [seasonalProduct] varchar(4))
;
    
INSERT INTO FinishProduct
    ([id], [created], [lastUpdated], [createdBy], [updatedBy], [genericName], [ean], [ircNumber], [universalCode], [localExportType], [tafsilId], [dosageFormId], [pharmaceuticalCategoryId], [therapeuticalCategoryId], [packId], [cartonId], [storageConditionId], [specialOfferId], [agreementId], [bonusId], [supplierId], [manufacturerId], [shelfLifeId], [brandName], [concentration], [concentrationUnitId], [valueAddId], [seasonalProduct])
VALUES
    (1388, '2013-11-25 09:01:05', '2015-07-21 09:42:10', 73, 73, 'ferr', NULL, NULL, 5763, 1, 18265, 22, 2, 117, 1387, 1387, 1387, NULL, 1387, NULL, 18137, 129, NULL, 'Irof', 0, NULL, 1369, NULL)
;

CREATE TABLE Tafsil
    ([id] int, [created] datetime, [lastUpdated] datetime, [createdBy] int
     , [updatedBy] int, [code] int, [description] varchar(16), [mohinId] int
     , [branchId] varchar(4), [disabled] varchar(5))
;
    
INSERT INTO Tafsil
    ([id], [created], [lastUpdated], [createdBy], [updatedBy], [code], [description], [mohinId], [branchId], [disabled])
VALUES
    (18265, '2013-11-25 09:01:05', '2015-07-21 09:42:10', 73, 73, 3012, 'Oral Drops-Sales', 735, NULL, 'false')
;


CREATE TABLE ReceiptDetail
    ([id] int, [created] datetime, [lastUpdated] datetime, [createdBy] int, [updatedBy] int
     , [comment] varchar(4), [debit] varchar(8), [credit] varchar(7), [toCheck] varchar(5)
     , [quantity] varchar(4), [tafsilId] int, [receiptId] int, [descriptionType] int)
;
    
INSERT INTO ReceiptDetail
    ([id], [created], [lastUpdated], [createdBy], [updatedBy], [comment], [debit], [credit], [toCheck], [quantity], [tafsilId], [receiptId], [descriptionType])
VALUES
    (26993582, '2015-03-28 07:30:12', '2015-03-28 07:30:12', 176, 176, NULL, NULL, '7967', 'false', NULL, 20587, 2635111, 19),
    (26993581, '2015-03-28 07:30:12', '2015-03-28 07:30:12', 176, 176, NULL, NULL, '15639', 'false', NULL, 20586, 2635111, 18),
    (26993580, '2015-03-28 07:30:12', '2015-03-28 07:30:12', 176, 176, NULL, '23606', '(null)', 'false', NULL, 1045, 2635111, 20),
    (26993579, '2015-03-28 07:30:12', '2015-03-28 07:30:12', 176, 176, NULL, NULL, '295080', 'false', '20', 18265, 2635111, 1),
    (26993578, '2015-03-28 07:30:12', '2015-03-28 07:30:12', 176, 176, NULL, NULL, '409825', 'false', '25', 3575, 2635111, 1),
    (26993577, '2015-03-28 07:30:12', '2015-03-28 07:30:12', 176, 176, NULL, NULL, '172140', 'false', '20', 6621, 2635111, 1),
    (26993576, '2015-03-28 07:30:12', '2015-03-28 07:30:12', 176, 176, NULL, NULL, '418080', 'false', '6', 19346, 2635111, 1),
    (26993575, '2015-03-28 07:30:12', '2015-03-28 07:30:12', 176, 176, NULL, NULL, '1672320', 'false', '24', 19346, 2635111, 1),
    (26993574, '2015-03-28 07:30:12', '2015-03-28 07:30:12', 176, 176, NULL, NULL, '713110', 'false', '29', 19357, 2635111, 1),
    (26993573, '2015-03-28 07:30:12', '2015-03-28 07:30:12', 176, 176, NULL, NULL, '944280', 'false', '72', 18371, 2635111, 1),
    (26993572, '2015-03-28 07:30:12', '2015-03-28 07:30:12', 176, 176, NULL, NULL, '708192', 'false', '32', 3578, 2635111, 1),
    (26993571, '2015-03-28 07:30:12', '2015-03-28 07:30:12', 176, 176, NULL, NULL, '516384', 'false', '36', 129, 2635111, 1),
    (26993570, '2015-03-28 07:30:12', '2015-03-28 07:30:12', 176, 176, NULL, NULL, '590148', 'false', '36', 288, 2635111, 1),
    (26993569, '2015-03-28 07:30:12', '2015-03-28 07:30:12', 176, 176, NULL, NULL, '1270500', 'false', '20', 6811, 2635111, 1),
    (26993568, '2015-03-28 07:30:12', '2015-03-28 07:30:12', 176, 176, NULL, NULL, '224000', 'false', '20', 6582, 2635111, 1),
    (26993567, '2015-03-28 07:30:12', '2015-03-28 07:30:12', 176, 176, NULL, NULL, '1278000', 'false', '30', 5520, 2635111, 1),
    (26993566, '2015-03-28 07:30:12', '2015-03-28 07:30:12', 176, 176, NULL, NULL, '1049200', 'false', '80', 150, 2635111, 1),
    (26993565, '2015-03-28 07:30:12', '2015-03-28 07:30:12', 176, 176, NULL, NULL, '531144', 'false', '24', 17082, 2635111, 1),
    (26993564, '2015-03-28 07:30:12', '2015-03-28 07:30:12', 176, 176, NULL, NULL, '1062288', 'false', '48', 17082, 2635111, 1),
    (26993563, '2015-03-28 07:30:12', '2015-03-28 07:30:12', 176, 176, NULL, NULL, '229500', 'false', '20', 18981, 2635111, 1),
    (26993562, '2015-03-28 07:30:12', '2015-03-28 07:30:12', 176, 176, NULL, NULL, '344250', 'false', '30', 1314, 2635111, 1),
    (26993561, '2015-03-28 07:30:12', '2015-03-28 07:30:12', 176, 176, NULL, NULL, '1536885', 'false', '15', 6720, 2635111, 1),
    (26993560, '2015-03-28 07:30:12', '2015-03-28 07:30:12', 176, 176, NULL, NULL, '1721310', 'false', '15', 6711, 2635111, 1),
    (26993559, '2015-03-28 07:30:12', '2015-03-28 07:30:12', 176, 176, NULL, '15686636', NULL, 'false', NULL, 1045, 2635111, 1)
;


CREATE TABLE Receipt
    ([id] int, [created] datetime, [lastUpdated] datetime, [createdBy] int, [updatedBy] int
     , [transactionNumber] int, [receiptType] int, [receiptDate] datetime, [receiptId] int
     , [branchId] int, [sectionId] int)
;
    
INSERT INTO Receipt
    ([id], [created], [lastUpdated], [createdBy], [updatedBy], [transactionNumber], [receiptType], [receiptDate], [receiptId], [branchId], [sectionId])
VALUES
    (2635111, '2015-03-28 07:27:50', '2015-03-28 07:30:17', 176, 176, 1099400001, 3, '2015-03-28 07:27:50', 411, 23, 1)
;


CREATE TABLE Sale
    ([id] int, [created] datetime, [lastUpdated] datetime, [createdBy] int, [updatedBy] int
     , [orderId] int, [ReceiptId] int, [grandTotal] int)
;
    
INSERT INTO Sale
    ([id], [created], [lastUpdated], [createdBy], [updatedBy], [orderId], [ReceiptId], [grandTotal])
VALUES
    (309137, '2015-03-28 07:27:50', '2015-03-28 07:30:12', 176, 176, 307108, 2635111, 15710242)
;

Open in new window

see: http://sqlfiddle.com/#!6/7308a/6

{+edit}
by the way, "date" is a reserved word and it isn't a good idea to use it as a column name [PriceFromDate] or something similar would be preferred.
Avatar of Zolf

ASKER

Paul,

Thanks a lot for oyur feedbacks. I am trying to use your second method i.e. using CROSS APPLY but for some reason I get this error

[SELECT - 0 row(s), 0.003 secs]  1) [Error Code: 156, SQL State: S1000]  Incorrect syntax near the keyword 'CROSS'. 2) [Error Code: 156, SQL State: S1000]  Incorrect syntax near the keyword 'ORDER'.

and below is the query I am trying to run with CROSS APPLY, I am using this for the first time and am not sure if it is correct

 
SELECT
     ca.price,
    dbo.Visitor.firstName1,
    dbo.Visitor.lastName1,
    dbo.DeliveryOrder.paymentDate,
    dbo.Sale.created,
    dbo.Sale.grandTotal,
    dbo.Receipt.transactionNumber,
    dbo.ReceiptDetail.credit,
    dbo.ReceiptDetail.quantity    AS Quantity,
    dbo.Branch.name               AS BranchName,
    dbo.Customer.organizationName AS CustomerName,
    Tafsil_alias2.description     AS ProductName,
    dbo.Receipt.receiptDate       AS SalesDate
FROM
    dbo.DeliveryOrder
INNER JOIN
    dbo.Visitor
ON
    (
        dbo.DeliveryOrder.visitorId = dbo.Visitor.id)
INNER JOIN
    dbo.Sale
ON
    (
        dbo.DeliveryOrder.id = dbo.Sale.orderId)
INNER JOIN
    dbo.Customer
ON
    (
        dbo.DeliveryOrder.customerId = dbo.Customer.id)
INNER JOIN
    dbo.Receipt
ON
    (
        dbo.Sale.invoiceReceiptId = dbo.Receipt.id)
INNER JOIN
    dbo.ReceiptDetail
ON
    (
        dbo.Receipt.id = dbo.ReceiptDetail.receiptId)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Receipt.branchId = dbo.Branch.id)
INNER JOIN
    dbo.Tafsil Tafsil_alias1
ON
    (
        dbo.ReceiptDetail.tafsilId = Tafsil_alias1.id)
INNER JOIN
    dbo.TafsilLink
ON
    (
        Tafsil_alias1.id = dbo.TafsilLink.targetId)
INNER JOIN
    dbo.Tafsil Tafsil_alias2
ON
    (
        dbo.TafsilLink.sourceId = Tafsil_alias2.id)
INNER JOIN
    dbo.FinishProduct
ON
    (
        Tafsil_alias1.id = dbo.FinishProduct.tafsilId)
INNER JOIN
    dbo.SalesPricing
ON
    (
        dbo.FinishProduct.id = dbo.SalesPricing.finishProductId)
WHERE
    dbo.Receipt.receiptDate >= '2014-03-21'
AND dbo.ReceiptDetail.credit IS NOT NULL
AND dbo.ReceiptDetail.descriptionType = 1
AND dbo.Receipt.transactionNumber = 1099400001 ;
GROUP BY
dbo.Visitor.firstName1,
    dbo.Visitor.lastName1,
    dbo.DeliveryOrder.paymentDate,
    dbo.Sale.created,
    dbo.Sale.grandTotal,
    dbo.Receipt.transactionNumber,
    dbo.ReceiptDetail.credit,
    dbo.ReceiptDetail.quantity,
    dbo.Branch.name ,
    dbo.Customer.organizationName,
    Tafsil_alias2.description ,
    dbo.Receipt.receiptDate
    CROSS APPLY (
       select top (1) sp.id, sp.price, sp.[Date]
       from SalesPricing sp
       where fp.id = sp.finishProductId AND r.receiptDate >= sp.[Date]
       ORDER BY sp.[Date] DESC

Open in new window

Avatar of Zolf

ASKER

User generated image
Looking at this screenshot which returns all the details of a customer with the products it has bought for a long period and below is the query. How can I have a contraint to compare the Receipt.receiptDate  AS SalesDate with the .SalesPricing.date AS SalesPriceDate,,so that I get the SalesPricing.price) AS SalesPrice which is between the  SalesPricing.date.
For e.g. if you see the screenshot I have 3 products which is getting repeated n number of times depending how many time the price changed over a period. Now I need to filter and keep only those records where i marked green in the shot.

SELECT
    dbo.Visitor.firstName1,
    dbo.Visitor.lastName1,
    dbo.DeliveryOrder.paymentDate,
    dbo.Sale.created,
    dbo.Sale.grandTotal,
    dbo.Receipt.transactionNumber,
    dbo.ReceiptDetail.credit,
    dbo.ReceiptDetail.quantity    AS Quantity,
    dbo.Branch.name               AS BranchName,
    dbo.Customer.organizationName AS CustomerName,
    Tafsil_alias2.description     AS ProductName,
    dbo.SalesPricing.date         AS SalesPriceDate,
    MAX(dbo.SalesPricing.price)        AS SalesPrice,
    dbo.Receipt.receiptDate       AS SalesDate
FROM
    dbo.DeliveryOrder
INNER JOIN
    dbo.Visitor
ON
    (
        dbo.DeliveryOrder.visitorId = dbo.Visitor.id)
INNER JOIN
    dbo.Sale
ON
    (
        dbo.DeliveryOrder.id = dbo.Sale.orderId)
INNER JOIN
    dbo.Customer
ON
    (
        dbo.DeliveryOrder.customerId = dbo.Customer.id)
INNER JOIN
    dbo.Receipt
ON
    (
        dbo.Sale.invoiceReceiptId = dbo.Receipt.id)
INNER JOIN
    dbo.ReceiptDetail
ON
    (
        dbo.Receipt.id = dbo.ReceiptDetail.receiptId)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Receipt.branchId = dbo.Branch.id)
INNER JOIN
    dbo.Tafsil Tafsil_alias1
ON
    (
        dbo.ReceiptDetail.tafsilId = Tafsil_alias1.id)
INNER JOIN
    dbo.TafsilLink
ON
    (
        Tafsil_alias1.id = dbo.TafsilLink.targetId)
INNER JOIN
    dbo.Tafsil Tafsil_alias2
ON
    (
        dbo.TafsilLink.sourceId = Tafsil_alias2.id)
INNER JOIN
    dbo.FinishProduct
ON
    (
        Tafsil_alias1.id = dbo.FinishProduct.tafsilId)
INNER JOIN
    dbo.SalesPricing
ON
    (
        dbo.FinishProduct.id = dbo.SalesPricing.finishProductId)
WHERE
    dbo.Receipt.receiptDate >= '2014-03-21'
AND dbo.ReceiptDetail.credit IS NOT NULL
AND dbo.ReceiptDetail.descriptionType = 1
AND dbo.Receipt.transactionNumber = 1099400001 ;
AND SalesPricing.date < dbo.Receipt.receiptDate 
GROUP BY
dbo.Visitor.firstName1,
    dbo.Visitor.lastName1,
    dbo.DeliveryOrder.paymentDate,
    dbo.Sale.created,
    dbo.Sale.grandTotal,
    dbo.Receipt.transactionNumber,
    dbo.ReceiptDetail.credit,
    dbo.ReceiptDetail.quantity,
    dbo.Branch.name ,
    dbo.Customer.organizationName,
    Tafsil_alias2.description ,
    dbo.SalesPricing.date,
    dbo.Receipt.receiptDate

Open in new window

Avatar of Zolf

ASKER

IN this query I tried to use MAX in the WHERE clause for the SalesRpicing.price...this is giving single records for each product but the unitprice is wrong,it is getting the latest unit price even for the old sales

SELECT
    dbo.Visitor.firstName1,
    dbo.Visitor.lastName1,
    dbo.DeliveryOrder.paymentDate,
    dbo.Sale.created,
    dbo.Sale.grandTotal,
    dbo.Receipt.transactionNumber,
    dbo.ReceiptDetail.credit,
    dbo.ReceiptDetail.quantity    AS Quantity,
    dbo.Branch.name               AS BranchName,
    dbo.Customer.organizationName AS CustomerName,
    Tafsil_alias2.description     AS ProductName,
    dbo.SalesPricing.date         AS SalesPriceDate,
    dbo.SalesPricing.price        AS SalesPrice,
    dbo.Receipt.receiptDate       AS SalesDate
FROM
    dbo.DeliveryOrder
INNER JOIN
    dbo.Visitor
ON
    (
        dbo.DeliveryOrder.visitorId = dbo.Visitor.id)
INNER JOIN
    dbo.Sale
ON
    (
        dbo.DeliveryOrder.id = dbo.Sale.orderId)
INNER JOIN
    dbo.Customer
ON
    (
        dbo.DeliveryOrder.customerId = dbo.Customer.id)
INNER JOIN
    dbo.Receipt
ON
    (
        dbo.Sale.invoiceReceiptId = dbo.Receipt.id)
INNER JOIN
    dbo.ReceiptDetail
ON
    (
        dbo.Receipt.id = dbo.ReceiptDetail.receiptId)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Receipt.branchId = dbo.Branch.id)
INNER JOIN
    dbo.Tafsil Tafsil_alias1
ON
    (
        dbo.ReceiptDetail.tafsilId = Tafsil_alias1.id)
INNER JOIN
    dbo.TafsilLink
ON
    (
        Tafsil_alias1.id = dbo.TafsilLink.targetId)
INNER JOIN
    dbo.Tafsil Tafsil_alias2
ON
    (
        dbo.TafsilLink.sourceId = Tafsil_alias2.id)
INNER JOIN
    dbo.FinishProduct
ON
    (
        Tafsil_alias1.id = dbo.FinishProduct.tafsilId)
INNER JOIN
    dbo.SalesPricing
ON
    (
        dbo.FinishProduct.id = dbo.SalesPricing.finishProductId)
WHERE
    dbo.Receipt.receiptDate >= '2012-03-21'
AND dbo.ReceiptDetail.credit IS NOT NULL
AND dbo.ReceiptDetail.descriptionType = 1
AND dbo.Receipt.transactionNumber = 1099400001 ,
AND (SalesPricing.date =
                          (SELECT     MAX(date) AS Expr1
                             FROM         SalesPricing AS B
                             WHERE     (finishProductId = SalesPricing.finishProductId)))

Open in new window

Avatar of Zolf

ASKER

OK, refining my query in the MAX and adding an AND, when I add this additional clause it is getting the correct unit price for the corresponding product and its sales date,BUT then it is filtering some Sales transactions

(SELECT     MAX(date) AS Expr1
                             FROM         SalesPricing AS B
                             WHERE     (finishProductId = SalesPricing.finishProductId) AND SalesPricing.date < dbo.Receipt.receiptDate))

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Zolf

ASKER

Thanks for your query, but now I get another error
[Error Code: 4104, SQL State: S1000]  The multi-part identifier "r.receiptDate" could not be bound.
Please attempt to resolve simple issues yourself - it will be faster for you

That message is very simple to interpret, somewhere in the new piece of code I introduced, I have used the alias "r"

but in your query you have not defined the alias "r"

so the error message: The multi-part identifier "r.receiptDate"  

is referring to the "r" and the "recepitDate" as a multi-part identifier, and it cannot figure out what to do with it be cause the alias "r" does not exist

so, to avoid this, instead of r.receiptDate use the full table name
Avatar of Zolf

ASKER

Thanks very much for your feedbacks