cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
bernie426
Level II

Create a date time panel to extract SQL data

Hello,

I used to use a jmp script to extract sql data table. This day I tried to built a window panel box to execute the same sql data table function.

The simple idea is to extract sql data table within a period of time, which would be two input variables in the panel box. The "End_Time" can easily use Today() syntax. However, for "Start_Time", I tried to write a script that allow user to input a certain "hour "before End_time that he want to pull the data table.

Unfortunately, this script is not working as expected.

Can any jmp script guru try to correct my script below? I also attached the jmp script I wrote for modification.

Many Thanks,

nw = new window("SQL Data Table Extraction",

      Vlistbox(

            tb1=textbox("Start_Time: "),

            dteb1 = numbereditbox(1),

  hlistbox(

  text box("Enter the number of hours: "),

  hours_teb = text edit box()),

            tb2=textbox("End_Time: "),

            dteb2 = numbereditbox(1),

                  ),

      ok_button = button box("Get Data", << Open Database("DSN=;Description=;UID=;PWD=;APP=;WSID=", sql_statement, Test Data)),

dteb1 << set format(format("m/d/y h:m:s", 23, 0)),

dteb1 << set width(23),

dteb1 << set(today()-EvalExpr((hours_tab)*60*60)),

dteb2 << set format(format("m/d/y h:m:s", 23, 0)),

dteb2 << set(Today()));

  sql_statement="SELECT * FROM dbo.TESTCOMPLETE WHERE (dCreated >= '"||Start Date/Time||"') AND (dCreated <='"||End_Date/Time||"')");

12 REPLIES 12
bernie426
Level II

Re: Create a date time panel to extract SQL data

Hi Vince,

Thanks for the guidance and patience.  Finally I got it work out.

Underneath one is the correct and working version. 

  1. NamesDefaultToHere(1); 
  2.  
  3. // Written by Vince Faller 
  4. // Makes a calendar come up on a numeditbox (defaults to now) 
  5. // Note, this only works on JMP 11 or above 
  6. dateBox = Function
  7. {timething = Today()}, 
  8. {newbox}, 
  9. newbox = Eval( 
  10. Substitute( 
  11. Name Expr( 
  12. Number Edit Box( 
  13. timething,  // Eval(Sub()) because of JMP quirk 
  14. 10,  // Just need this because JMP doesn't default a width 
  15. <<Set function
  16. Function( {self}, 
  17. {}, 
  18. If( Is Missing( self << get ), 
  19. self << Set( Eval( DUMMY ) ) // Sets the date to the timething argument 
  20. ), 
  21. << Set Format( Format( "m/d/y h:m:s", 23, 0 ) ); // Makes datetime format for the box (gives calendar) 
  22. ), 
  23. Expr( DUMMY ), timething 
  24. ); 
  25. newbox; 
  26. ); 
  27.  
  28. nw = 
  29. New Window( "SQL Data Table Extraction"
  30. LineUpBox(NCol(2), 
  31. TextBox("Start DateTime"), start = dateBox(Today() - InDays(12)), 
  32. TextBox("End DateTime"), end = dateBox(), 
  33. ButtonBox("Cancel", nw << close window), ButtonBox("Get Data", RunScript) 
  34. )); 
  35.    
  36. RunScript= Expr(nw<<Close Window; 
  37.   sql_statement=evalinsert("SELECT * FROM dbo.TESTCOMPLETE WHERE dCreated >= '^startDT^' AND dCreated <= '^endDT^' ORDER BY dCreated");
  38.   startDT=MDYHMS(start << get); 
  39.   endDT=MDYHMS(end<<get); 
  40.   Open Database
  41.   "DSN=;Description=;UID=;PWD=;APP=;WSID="
  42.   sql_statement,"Test Result"));
guy_yosef
Level III

Re: Create a date time panel to extract SQL data

i am using the folwing script to exract SQL data table and limit the time in modal

::dbc  = my name and login that i insert in different add in, you can use open data base instead

i added botton for 2 diffrent tables (defect and yield)

another filter for paramter (defect type, yield paramter etc.)

my SQL time column is 'dayxxx'

 

win = New Window("select data"

<< Modal,

Panel Box("Select",

rbox = Radio Box( {"DEFECT", "YIELD"}

)

,Text Box("PARAMETER"),variablebox1 = Text Edit Box()

,Text Box("Insert days"),variablebox2 = Text Edit Box()

);

area1 = rbox <<get;

PARAM = variablebox1 <<get text ;

Days = variablebox2 <<get text ;

table1 = if(

area1 == 1, "defect tablexxx",

area1 == 2, "yield tablexxx" 

)

;

/// SQL data selsction ///

dt = Execute SQL(::dbc,

"SELECT * FROM \!"pathxxx\!".\!""||table1||"\!"

WHERE \!"dayxxx\!" > (CURRENT_DATE - "||Days||" DAYS)

AND \!"parameterxxx\!" = '"||PARAM||"'

"

);

 

dt << Set Name(""||PARAM||" "||days||"");

*i did alot of modifcation to hide my company data base name so i might made some script erros


bernie426
Level II

Re: Create a date time panel to extract SQL data

Thanks for sharing....guy.yosef