Zolf
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.
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.
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
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.
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.
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 ;
Original (in question) GROUP BY clause is:
GROUP BY
dbo.Visitor.firstName1,
dbo.Visitor.lastName1,
dbo.DeliveryOrder.paymentD ate,
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.
GROUP BY
dbo.Visitor.firstName1,
dbo.Visitor.lastName1,
dbo.DeliveryOrder.paymentD
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.
ASKER
Thanks Paul,please hang on while I prepare those tables
ASKER
here is the tables exported into excel as rar and also I have attached screenshot of the relationship
Sales.xls
Receipt.xls
ReceiptDetail.xls
Tafsil.xls
FinishProduct.xls
SalesPricing.xls
Sales.xls
Receipt.xls
ReceiptDetail.xls
Tafsil.xls
FinishProduct.xls
SalesPricing.xls
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
>>"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
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
ASKER
Paul, you see I need to get the unit price of that product for that date when the sales was done.
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:
Another approach to this, which will resolves the problem of too many prices is to use CROSS APPLY. Like this
Query 2
see: http://sqlfiddle.com/#!6/7308a/9
Details:
{+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.
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 |
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 |
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)
;
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.
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
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
ASKER
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
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)))
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))
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
Thanks very much for your feedbacks
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).