SQL-Query

Diijay

Member
Join Date
Jun 2007
Location
Kotka
Posts
15
Hi !

I know this is not straightly a PLC-Problem, but im trying to fetch data from WinCC-Database using SQL-statements and what i basicly need to do is :

I need to find out when the temperature of Burner has reached the optimal temperature to start the production during Monday mornings with a timescale of the whole last year, up to this point..

Im generating the week scale and extracting Mondays from it with a variable of 1 (The week scale is 1 to 7), 1 meaning as Monday, 7 as sunday, with a following query :

select created_date as DATE_AND_TIME, burner_temp as BURNER_TEMPERATURE, thermo_oil_inlet_temp as THERMON_OIL_TEMPERATURE,

extract(year from created_date) as YEAR
,extract(weekday from created_date) as DAY_OF_THE_WEEK
from selma_measure

where extract(weekday from created_date)= 1 and created_date>'01.01.2007' and burner_temp>738 order by created_date

The problem is that the result shows me every single result from every monday when temperature is 738 or higher. I need to get only the first time the temperature has reached 738 or higher & after that from the next Monday.

Does any of you guys have any idea how to do this ?
 
I tried this in MS access so the syntax is a bit different but you should be able to use it to get your SQL query working.

SELECT test4.created_date AS DATE_AND_TIME, min(test4.burner_temp) AS BURNER_TEMPERATURE, test4.other_temp AS THERMON_OIL_TEMPERATURE, Year(created_date) AS [YEAR], Weekday(created_date) AS DAY_OF_THE_WEEK
FROM test4
WHERE (((test4.created_date)>#1/1/2007#) AND ((test4.burner_temp)>738) AND ((Weekday([created_date]))=1))
GROUP BY test4.created_date,test4.other_temp

it basically groups the records by date and picks out the lowest burner value that meets your criteria.
 
Hi

I think you should check out the UNIQUE statment, which should return the only the first item that matches your query

SELECT UNIQUE ............. rest of query
 
"Limit 1" will only give you 1 line, not exactly sure that's what you're lookning for though.
 

Similar Topics

Good Evening, I'm loading a recipe into a Rockwell ControlLogix or CompactLogix CPU via Excel, this is all working fine. Is there anyway I can...
Replies
7
Views
1,994
Hi, I have a question about SQL. This question is a little to much on the IT side I suppose, but I will give it a try anyway as I do not have an...
Replies
6
Views
2,474
Hi there, I use Indusoft IWS8.0 & I have created a sample Production data Date&time, MachineNo,Production Data with SQL Server 2008 DB. Need to...
Replies
1
Views
1,864
Hello, For my general information and knowledge, how is querying and storing data from a AB PLC (Compact or Control Logix) typically...
Replies
15
Views
7,285
Goodevening all... I have a Redlion PTV and Graphite with built in webserver... I can create the simple default.htm page... drag in tags to...
Replies
1
Views
1,893
Back
Top Bottom