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.


1
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

zolfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MlandaTCommented:
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).
0
zolfAuthor Commented:
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.1
0
zolfAuthor Commented:
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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PortletPaulfreelancerCommented:
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.
1
zolfAuthor Commented:
Thanks Paul,please hang on while I prepare those tables
0
zolfAuthor Commented:
here is the tables exported into excel as rar and also I have attached screenshot of the relationship

1Sales.xls
Receipt.xls
ReceiptDetail.xls
Tafsil.xls
FinishProduct.xls
SalesPricing.xls
0
zolfAuthor Commented:
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

0
PortletPaulfreelancerCommented:
>>"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
0
zolfAuthor Commented:
Paul, you see I need to get the unit price of that product for that date when the sales was done.
0
zolfAuthor Commented:
SalePricing.Date is the date which implies that from this date the new product price has to be used
0
PortletPaulfreelancerCommented:
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.
2
zolfAuthor Commented:
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

0
zolfAuthor Commented:
1
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

0
zolfAuthor Commented:
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

0
zolfAuthor Commented:
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

0
PortletPaulfreelancerCommented:
An APPLY operator is used within the FROM clause. You cannot just add it at the end of a much bigger query.

In your query you would also use it INSTEAD of joining to the SalesPricing table, so please don't continue joining to SalesPricing AND use CROSS APPLY on that table
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
      CROSS APPLY (
            SELECT TOP (1)
                  sp.id
                , sp.price
                , sp.[Date]
            FROM SalesPricing sp
            WHERE dbo.FinishProduct.id = sp.finishProductId
                  AND r.receiptDate >= sp.[Date]
            ORDER BY sp.[Date] DESC
          ) ca
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
;

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zolfAuthor Commented:
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.
0
PortletPaulfreelancerCommented:
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
0
zolfAuthor Commented:
Thanks very much for your feedbacks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.