Create a date time panel to extract SQL data

[#1]

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||"')");

[#2]

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. ) 
22. ), 
23. << Set Format( Format( "m/d/y h:m:s", 23, 0 ) ); // Makes datetime format for the box (gives calendar) 
24. ) 
25. ), 
26. Expr( DUMMY ), timething 
27. ) 
28. ); 
29. newbox; 
30. ); 
31.  
32. nw = 
33. New Window( "SQL Data Table Extraction", 
34. LineUpBox(NCol(2), 
35. TextBox("Start DateTime"), start = dateBox(Today() - InDays(12)), 
36. TextBox("End DateTime"), end = dateBox(), 
37. ButtonBox("Cancel", nw << close window), ButtonBox("Get Data", RunScript) 
38. )); 
39.    
40. RunScript= Expr(nw<<Close Window; 
41.   sql_statement=evalinsert("SELECT * FROM dbo.TESTCOMPLETE WHERE dCreated >= '^startDT^' AND dCreated <= '^endDT^' ORDER BY dCreated");
    
42.   startDT=MDYHMS(start << get); 
43.   endDT=MDYHMS(end<<get); 
44.   Open Database( 
45.   "DSN=;Description=;UID=;PWD=;APP=;WSID=", 
46.   sql_statement,"Test Result"));

[#3]

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

[#4]

Thanks for sharing....guy.yosef​