I'm attempting to write a function that inserts a date ("End") based on a serial number recorded in the table. The function looks for a previous instance of SERIAL_NUMBER_RECEIVED and if none is found, populates the "End" field with Today's date. If a previous instance of SERIAL_NUMBER_RECEIVED is found, then "End" is populated with a date that is one day prior to the previous instance's Receive Date.
In my dataset there is a single instance of a particular SERIAL_NUMBER_RECEIVED value, so I would expect to see the "End" field populated with today's date. However, when I look at the data in a straight table after the load, I see two values for "End". One populated with today's date and one populated with the date prior to the Recieve date. Can anyone explain what I'm doing wrong?
[Returns]: LOAD ITEM_NUMBER, CUSTOMER_NUMBER, CUSTOMER_NAME, ORDER_TYPE, ORDER_NUMBER, ORDERED_DATE, DESCRIPTION, LINE_TYPE, LINE_STATUS, RETURN_REASON_CODE, SERIAL_NUMBER_RECEIVED as Returned_SN, ORDER_PACK_NOTES, ORDER_SHIP_NOTES, LINE_PACK_NOTES, LINE_SHIP_NOTES, LINE_NUMBER, LINE_CATEGORY_CODE, DATE_RECEIVED as [Receive Date], Date(If(SERIAL_NUMBER_RECEIVED = Previous(SERIAL_NUMBER_RECEIVED), RangeSum(Previous(DATE_RECEIVED), -1), Today())) as End //CR Service Completion Date should be between start and end date Resident ReturnsTemp Order By SERIAL_NUMBER_RECEIVED, DATE_RECEIVED desc;
If I only load the first table, ReturnsTemp, there is only one value in the table for SN 15H005709, which is expected. when I load the second table, Returns, and drop ReturnsTemp, I end up with a duplicate.