March 31, 2015 at 9:26 am
Hello, I have the below query which pulls invoice data based on posting date and GL parameters. I am having the issue where some invoices have multiple posting dates so they are being duplicated. How do I get my query to only pull the oldest posting date so some lines are not duplicated?
SELECT
uvDocumentTypeAPInvoices.DocumentId
,uvPropertyValueInvoiceDate.PropertyValue AS [uvPropertyValueInvoiceDate PropertyValue]
,uvPropertyValueVendorID.PropertyValue AS [uvPropertyValueVendorID PropertyValue]
,uvPropertyValueVendorName.PropertyValue AS [uvPropertyValueVendorName PropertyValue]
,uvPropertyValueGLAccount.PropertyValue AS [uvPropertyValueGLAccount PropertyValue]
,uvPropertyValueGLAmount.PropertyValue AS [uvPropertyValueGLAmount PropertyValue]
,uvPropertyValueGLName.PropertyValue AS [uvPropertyValueGLName PropertyValue]
,uvPropertyValueInvoiceNumber.PropertyValue AS [uvPropertyValueInvoiceNumber PropertyValue]
,uvPropertyValueDivision.PropertyValue AS [uvPropertyValueDivision PropertyValue]
,uvPropertyValuePostingDate.PropertyValue AS [uvPropertyValuePostingDate PropertyValue]
FROM
uvDocumentTypeAPInvoices
INNER JOIN uvPropertyValueInvoiceNumber
ON uvDocumentTypeAPInvoices.DocumentId = uvPropertyValueInvoiceNumber.DocumentId
INNER JOIN uvPropertyValueInvoiceDate
ON uvDocumentTypeAPInvoices.DocumentId = uvPropertyValueInvoiceDate.DocumentId
INNER JOIN uvPropertyValueVendorID
ON uvDocumentTypeAPInvoices.DocumentId = uvPropertyValueVendorID.DocumentId
INNER JOIN uvPropertyValueVendorName
ON uvDocumentTypeAPInvoices.DocumentId = uvPropertyValueVendorName.DocumentId
INNER JOIN uvPropertyValueGLAccount
ON uvDocumentTypeAPInvoices.DocumentId = uvPropertyValueGLAccount.DocumentId
INNER JOIN uvPropertyValueGLAmount
ON uvPropertyValueGLAccount.DocumentId = uvPropertyValueGLAmount.DocumentId AND uvPropertyValueGLAccount.GroupValue = uvPropertyValueGLAmount.GroupValue
INNER JOIN uvPropertyValueGLName
ON uvPropertyValueGLAccount.DocumentId = uvPropertyValueGLName.DocumentId AND uvPropertyValueGLAccount.GroupValue = uvPropertyValueGLName.GroupValue
LEFT OUTER JOIN uvPropertyValueDivision
ON uvDocumentTypeAPInvoices.DocumentId = uvPropertyValueDivision.DocumentId
LEFT OUTER JOIN uvPropertyValuePostingDate
ON uvDocumentTypeAPInvoices.DocumentId = uvPropertyValuePostingDate.DocumentId
WHERE
uvPropertyValuePostingDate.PropertyValue >= @PropertyValue
AND uvPropertyValuePostingDate.PropertyValue <= @PropertyValue2
AND uvPropertyValueGLAccount.PropertyValue LIKE @PropertyValue3
March 31, 2015 at 10:57 am
Short answer - you need to turn this into a totals query and group by everything but the date. Get MAX(Date). Or use a subquery to return only the latest posting.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply