Choose Language Hide Translation Bar
Highlighted
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"));
Highlighted
guy_yosef
Level II

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


Highlighted
bernie426
Level II

Re: Create a date time panel to extract SQL data

Thanks for sharing....guy.yosef

Article Labels

    There are no labels assigned to this post.