Post reply

NetSuite - System_Notes SQL to obtain STATUS as of a past date using SYSTEM_NOTE

  • October 4, 2019 at 9:29 am

    #3685948

    Hi all, Everything I've seen on the subject of NetSuite Course SYSTEM_NOTES discusses mostly saved searches or PHP. I've been trying to write a SQL-based query that will join in SYSTEM_NOTES so that I can see what status was on a particular past date for a particular table.

    So for example, here is what I have so far. I think I must go through one or more levels to get down to SYSTEM_NOTES but I cannot seem to find the link. The schema does not show any info for SYSTEM_NOTES.

    Any ideas would be greatly appreciated.

    --get a count of all JOBS that were backlogged as of 01/01/2015

    SELECT A.CUSTOMER_ID, A.STATUS

    FROM CUSTOMERS A

    JOIN SYSTEM_NOTES C ON ????

    WHERE C.DATE = (SELECT MAX(C1.DATE) FROM SYSTEM_NOTES C1

    WHERE C1.? = C.?

    AND C1.DATE <= '2015-01-01')

    where A.CATEGORY_0 = 'JOB'

    AND C.STANDARD_FIELD = 'STATUS'

    and C.VALUE_NEW = 'Not Started'

    and A.DUE_DATE < '2015-01-01'

    Thanks in advance.

  • October 4, 2019 at 7:49 pm

    #3686142

    If you are assuming that we know the tables you are working with, I will have to disappoint you. We don't.

    The normal recommendation when people ask with help to form a query is that I suggest that they should post

    1. CREATE TABLE statements for the tables involved.
    2. INSERT statements with sample data given the sample.
    3. The desired result given the sample.
    4. A short description of the business rules that explains why you want that particular result.

    This is not unreasonable to ask for when people have a real-world problem, because presumably they have some understanding of their tables, and even more so of the business requirements.

    I reckon that this can be a lot more difficult when you are taking a class, because the problem may be more or less artificial, and the business rules are not fleshed out well. (So that is often the case is real-world problems, but in that case you can require people to clarify.)

    Still, without knowledge about the tables involved, we know even less than you do.

     

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply