on 03-18-2009 4:29 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
User | Count |
---|---|
89 | |
7 | |
6 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.