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

• liana.melissa12

  SSC Rookie

  Points: 34

  More actions

  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.

• Erland Sommarskog

  SSC-Insane

  Points: 24038

  More actions

  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]