cancel
Showing results for 
Search instead for 
Did you mean: 

Date Difference Query Syntax Formatted Search

Former Member
0 Kudos

Hi Experts,

My client renders a service(warehousing of Cargo) on time and material basis,therefore the Quantity column of the AR Invoice represents the number of days the cargo was warehoused.

I want to create a formatted search with reference to a query on this field to help me compute the number of days automatically. The number of days is normally calculated by Subtracting the the Admission date(Serial Number Details Table "OSRI", field "InDate" from the document date of the invoice) from the AR invoice document date.

Considering that the Item is serial number managed, the item serial number will connect the Serial Number details table to the AR Invoice table.

Can anybody help me with the syntax of the query that will achieve the above for me.

Waiting to hear from you .

Thanks

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member583013
Active Contributor
0 Kudos

If you have many serial numbers for an item, there will be no way to identify which serial number was actually selected in the invoice till the time the Invoice is added.

So knowing the Serial Number that was selected on that Invoice is important and what you select in the Serial number selection window on the Invoice is stored in temporary location and cannot be referencing by looking into OSRI table.

If there are multiple serial numbers with different InDate's, how could you calculate?

Is there a Delivery Step? Delivery > Invoice

OR

Do you copy SO > AR Invoice directly

Suda

Former Member
0 Kudos

Thanks for the prompt response.We copy the Sales Order to the Invoice, the Delivery happensafter invoicing so we cannot use it.What if I creat a custom Serial number field on the SO, can it establish the required connection?

former_member583013
Active Contributor
0 Kudos

YES, then it can be done

Sample Query

SELECT T1.[ItemCode] AS 'Item No.', T1.[IntrSerial] AS 'Serial Number', 
T1.[InDate] AS 'Admission Date', DATEDIFF(DAY, T1.InDate, GetDAte()) AS 'Qty Days'
FROM  [dbo].[SRI1] T0  INNER  JOIN [dbo].[OSRI] T1  ON  T1.[SysSerial] = T0.SysSerial 
AND T0.ItemCode = T1.ItemCode
WHERE T0.ItemCode = $[$38.1.0] AND T1.[SysSerial] = $[$38.U_SerialNo.0]

U_SerialNo is just an example, change this with your actual UDF name

Former Member
0 Kudos

Sorry for my late response,I have been ill, the sugested syntax delivered the required result.Many thanks.I'll be designing a lot of queries in the near future,I was wondering if there is any documentation I can get to improve my knowledge of Query syntax.I will immensely appreciate if you can point me to any document.

Regards