Emerson Exchange 365

SQL Query to generate list of all Batches with Formula that were run during last 2 year on a particular UNIT.

Dear All,

I need to do data mining for all Batches with Formula that were run during last 2 year on a particular UNIT. I have never tried generating the Queries before, so I am not aware of that. Recently read online about how SQL can really improve and fasten the data mining job using Queries so want to give it a try. 

Any help regarding writing the query would be greatly appreciated. 

Thank you.

-Ashish P

• SQL Query

Ashish P

• 14 Oct 2014 5:46 AM
• Cancel

7 Replies

• Niklas Flykt

  

  • 14 Oct 2014 12:02 PM

  Hi,
  It´s not that simple if you haven´t ever done it before especially in SQL server, you could gain some experience playing with an Access database (it´s grapichal)..
  it could look something like "Select column 1, column 2, column3, coulmn 4 from batches ; where formula = xxx and time start =0 and stoptime =2 ....
  http://www.w3schools.com/sql/ here you can learn something

  Niklas Flykt 

  Klinkmann Oy
  

  Key Account Manager safety products

  nikfly@gmail.com

  • Cancel
• Robert Rijnders

  

  • 14 Oct 2014 2:43 PM

  In reply to Niklas Flykt:

  I assume you have a batch historian server to run the query on. Generated batch auto-archives are automatically closed so be sure to connect the old archives from last 2 years before querying.

  • Cancel
• Ashish P

  

  • 15 Oct 2014 9:23 AM

  In reply to Robert Rijnders:

  Hello Niklas and Robert,
  Thank you very much for your replies. I will go through the tutorials to get accustomed to SQL.
  I will be working on batch historian. Unfortunately we don't have database for previous 2 yrs. I will need to run the query generated multiple archived databases.
  
  Any other suggestion or things I should look before starting the data gathering.

  • Cancel
• Youssef.El-Bahtimy

  

  • 16 Oct 2014 6:40 PM

  In reply to Ashish P:

  Try this.  change use DVHisDB to each archive that may contains batches of interest (i.e. use archive2013, use archive2012)  The trick here is formula is not associated with the batch directly, only through batch recipe parameters.  Also, unit is not associated with the batch directly, only with the unit procedures or lower level items within the batch.  The following query joins batch formula view and the batch recipe views on uniqueID provided the criteria in each view is met.

   

  use DVHisDB

  SELECT distinct batchformulaview.batchID    

       ,batchformulaview.formula

       ,batchrecipeview.unit

   FROM batchformulaview

   inner join batchrecipeview

   on batchformulaview.uniqueid = batchrecipeview.uniqueid

   and batchrecipeview.starttime > DATEADD(year,-2,GETDATE())

   where (formula = 'theFormulaname') and (unit = 'theUnitName')

  • Cancel
• Ashish P

  

  • 27 Oct 2014 2:46 AM

  In reply to Youssef.El-Bahtimy:

  Hello Youssef,
  Thank you very much for your reply. :) Your provided script really helped.
  
  On the other hand, I was able to study and worked out a small script just to populate the data (I am just a beginner with SQL) :
  DECLARE @Starttime datetime
  DECLARE @Endtime datetime
  SET @Starttime = '7/1/2014 12:00:00 AM'(Can set as per your requirement)
  SET @Endtime = '10/1/2014 12:00:00 AM'(Can set as per your requirement)
  
  SELECT [batchid]
  ,[occurtime]
  ,[area]
  ,[processcell]
  ,[unit]
  ,[phasemodule]
  ,[action]
  ,[uniqueid]
  FROM [dbo].[breportview]
  WHERE occurtime >= @Starttime AND occurtime <= @Endtime
  and unit like '%T-20000%'
  order by occurtime asc
  ________________________________________________________________
  
  Just use this code to query the database from which you need to pull data and it will give you everything. Later copy data in excel and apply formulas as needed.
  
  Thanks everyone for all your help !!

  • Cancel
• Youssef.El-Bahtimy

  

  • 27 Oct 2014 3:34 AM

  In reply to Ashish P:

  Glad to see you have what you need. Much simpler if you don't have to cross reference formula.

  • Cancel
• Rachelle McWright

  

  • 27 Oct 2014 2:29 PM

  In reply to Youssef.El-Bahtimy:

  Youssef.El-Bahtimy to the rescue, again! :)

  Best Regards,

  Rachelle McWright: Business Development Manager, Dynamic Simulation: U.S. Gulf Coast

  • Cancel