Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
In the following code I have a one to many relationship. One [Sales Order] for multiple [Bill To] dates.
I need to find the lowest Bill to date for each sales order.
Sales Order Bill To
4000003 11/01/2008
4000005 11/01/2009
4000025 11/01/2010
4000085 11/01/2011
4000078 11/01/2012
4000458 11/01/2013
I'm using the following code, but im still receiving multiple Bill To dates for 1 Sales Order.
LEFT KEEP (Main)
LOAD
DOC_NUMBER,
min(date(invoice_date)) as inv_date_sd
FROM source
GROUP BY DOC_NUMBER, invoice_date;
Does anyone have any suggestions?
Thanks
Is invoice_date the same as "Bill To" date? If so, remove it from the Group By clause.
Hope this helps,
Jason
Is invoice_date the same as "Bill To" date? If so, remove it from the Group By clause.
Hope this helps,
Jason
LEFT KEEP (Main)
LOAD
DOC_NUMBER,
min(date(invoice_date)) as inv_date_sd
FROM source
GROUP BY DOC_NUMBER;
That worked, I removed the invoice_date from the Group By clause and it worked correctly.
Why did this resolve the issue?
Thank you both for your help.
Grouping by all fields is the same as not grouping. You want to group the rows by DOC_NUMBER, and from within each group get the minimum invoice date.