# Thread: Need help show values for temperatures that are not equal to or above the threshold

1. Registered User
Join Date
Sep 2005
Posts
20

## Unanswered: Need help show values for temperatures that are not equal to or above the threshold

The below stored procedure is used to create a vertical benchmark line on the X-Axis which has a hour scale. I use the stored procedure to find out which temperature crosses or equals the threshold temperature (340), then plot the vertical benchmark line at the hour the first temperature is equal to or greater than 340 degrees and less than 1000 degrees. The logic below works if the temperature is equal to or greater than 340 degrees and less than 1000 degrees. THE ISSUE is I have 8 temperatures if they don't cross the threshold of 340 degrees I need to set a default value for my vertical line. In other words if the temperature is 180 and my threshold is 340 then set my vertical line on the highest temperature close to 340.

I tried removing my Where clause (but then it breaks the logic for those temperatures that are equal to or greater than 340). I tried using Case When but this didn't give me what I want either. I tried UNION as well. All giving me results I don't want.

Here is what I am looking for:

This first example is one where there was a temperature that was equal to or greater than the threshold of 340 degrees. This is CORRECT

Code:
```first_to_cross_threshold	AgeSampleDateDiff	AgeovenStartTimestamp	AgeovenCompleteTimestamp
2014-02-04 19:24:00	3.350000	2014-02-04 16:02:13.000	2014-02-05 05:02:13.000```

Here is what I am looking for - I need help with:

If 8 temperatures did not equal or cross the threshold then give me the hour of the highest temperature close to the threshold but do not return 0.

For Example:

temp1 92
temp2 108
temp3 0
temp4 284 <<< this is the closest to the threshold so give me the hour when this occurred.
temp5 2192 *Remember I can only count temperatures less than 1000 degrees. Anything above 1000 degrees mean there is nothing in the oven. So it is false/positive.
temp6 102
temp7 0
temp8 12

Code:
```first_to_cross_threshold	AgeSampleDateDiff	AgeovenStartTimestamp	AgeovenCompleteTimestamp
2014-02-05 00:30:00	1.300000	2014-02-05 02:00:13.000	2014-02-05 02:00:13.000```

Code:
```CREATE PROCEDURE [dbo].[AgeScoreCardThreshold_JJ_12232013]
-- Add the parameters for the stored procedure here
@Lot varchar(50) = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

, ageSampleTsDate As time_stamp
, AgeovenStartTimestamp
, AgeovenCompleteTimestamp
,ISNULL(DATEDIFF(MI,
(SELECT        MIN(ageSampleTsDate) AS Expr1
ageSampleTsDate) / 60.0, 0) AS AgeSampleDateDiff
INNER JOIN dbo.ageOven ao
ON fat.ageOvenKey = ao.ageOvenKey
UNION ALL
, ageSampleTsDate As time_stamp
, AgeovenStartTimestamp
, AgeovenCompleteTimestamp
,ISNULL(DATEDIFF(MI,
(SELECT        MIN(ageSampleTsDate) AS Expr1
ageSampleTsDate) / 60.0, 0) AS AgeSampleDateDiff
INNER JOIN dbo.ageOven ao
ON fat.ageOvenKey = ao.ageOvenKey
UNION ALL
, ageSampleTsDate As time_stamp
, AgeovenStartTimestamp
, AgeovenCompleteTimestamp
,ISNULL(DATEDIFF(MI,
(SELECT        MIN(ageSampleTsDate) AS Expr1
ageSampleTsDate) / 60.0, 0) AS AgeSampleDateDiff
INNER JOIN dbo.ageOven ao
ON fat.ageOvenKey = ao.ageOvenKey
UNION ALL
, ageSampleTsDate As time_stamp
, AgeovenStartTimestamp
, AgeovenCompleteTimestamp
,ISNULL(DATEDIFF(MI,
(SELECT        MIN(ageSampleTsDate) AS Expr1
ageSampleTsDate) / 60.0, 0) AS AgeSampleDateDiff
INNER JOIN dbo.ageOven ao
ON fat.ageOvenKey = ao.ageOvenKey
UNION ALL
, ageSampleTsDate As time_stamp
, AgeovenStartTimestamp
, AgeovenCompleteTimestamp
,ISNULL(DATEDIFF(MI,
(SELECT        MIN(ageSampleTsDate) AS Expr1
ageSampleTsDate) / 60.0, 0) AS AgeSampleDateDiff
INNER JOIN dbo.ageOven ao
ON fat.ageOvenKey = ao.ageOvenKey
UNION ALL
, ageSampleTsDate As time_stamp
, AgeovenStartTimestamp
, AgeovenCompleteTimestamp
,ISNULL(DATEDIFF(MI,
(SELECT        MIN(ageSampleTsDate) AS Expr1
ageSampleTsDate) / 60.0, 0) AS AgeSampleDateDiff
INNER JOIN dbo.ageOven ao
ON fat.ageOvenKey = ao.ageOvenKey
UNION ALL
, ageSampleTsDate As time_stamp
, AgeovenStartTimestamp
, AgeovenCompleteTimestamp
,ISNULL(DATEDIFF(MI,
(SELECT        MIN(ageSampleTsDate) AS Expr1
ageSampleTsDate) / 60.0, 0) AS AgeSampleDateDiff
INNER JOIN dbo.ageOven ao
ON fat.ageOvenKey = ao.ageOvenKey
UNION ALL
, ageSampleTsDate As time_stamp
, AgeovenStartTimestamp
, AgeovenCompleteTimestamp
,ISNULL(DATEDIFF(MI,
(SELECT        MIN(ageSampleTsDate) AS Expr1
ageSampleTsDate) / 60.0, 0) AS AgeSampleDateDiff
INNER JOIN dbo.ageOven ao
ON fat.ageOvenKey = ao.ageOvenKey

)
SELECT TOP 1 Min(time_stamp) As first_to_cross_threshold, AgeSampleDateDiff, AgeovenStartTimestamp
, AgeovenCompleteTimestamp
AND AgeSampleDateDiff IS NOT NULL
group by  AgeSampleDateDiff, AgeovenStartTimestamp
, AgeovenCompleteTimestamp
order by  Min(time_stamp)```
Last edited by jazlady; 02-10-14 at 13:27.

2. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Have a play with this:
Code:
```DECLARE @x table (
note char(5)
, temp int
);

INSERT INTO @x (note, temp)
VALUES ('temp1',   92)
, ('temp2',  108)
, ('temp3',    0)
, ('temp4',  284)
, ('temp5', 2192)
, ('temp6',  102)
, ('temp7',    0)
, ('temp8',   12)
;

DECLARE @threshold int = 340;

; WITH temps AS (
SELECT note
, temp
, Row_Number() OVER (ORDER BY temp DESC) As seq
FROM   @x
WHERE  temp < 1000
)
SELECT note
, temp
FROM   temps
WHERE  temp >= @threshold
OR seq = 1```

3. Registered User
Join Date
Jan 2013
Posts
358
Use a table of time slots for each hour in a day. I am assuming you want the data per day. Since SQL is a data base language, we prefer to use tables and not do procedural math.

CREATE TABLE Timeslots
(timeslot_name CHAR(14) NOT NULL PRIMARY KEY,
slot_start_time DATETIME2(0) NOT NULL PRIMARY KEY,
slot_end_time DATETIME2(0) NOT NULL,
CHECK (slot_start_time < slot_end_time));

INSERT INTO Timeslots -- one hour slots
VALUES ('2014-02-15 S00', '2014-02-15 00:00:00', '2014-02-15 00:59:59'),
('2014-02-15 S01','2014-02-15 S1','2014-02-15 01:00:00', '2014-02-15 01:59:59'),
('2014-02-15 S02','2014-02-15 02:00:00', '2014-02-15 02:59:59'),
('2014-02-15 S03','2014-02-15 03:00:00', '2014-02-15 03:59:59'),
..
('2014-02-15 S23','2014-02-15 23:00:00', '2014-02-15 23:59:59');

(timeslot_name CHAR(14) NOT NULL PRIMARY KEY
REFERENCES Timeslots,
oven_temperature INTEGER NOT NULL
CHECK (oven_temperature BETWEEN 0 AND 9999
);

VALUES
('2014-02-15 S01', 92),
('2014-02-15 S02', 108),
('2014-02-15 S03', 0),
('2014-02-15 S04', 284),
('2014-02-15 S05', 2192), --- set to NULL
('2014-02-15 S06', 102),
('2014-02-15 S07', 0),
('2014-02-15 S08', 12);

We can trim off the false readings and get the daily max reading(s).

WITH X1(timeslot_name, oven_temperature)
AS
(SELECT timeslot_name,
CASE WHEN oven_temperature >= 340
THEN CAST (NULL AS INTEGER)
ELSE oven_temperature END

X2 (timeslot_name, oven_temperature, max*_oven_temperature)
AS
(SELECT timeslot_name, oven_temperature,
MAX(oven_temperature)
OVER (PARTITION BY SUBSTRING (timeslot_name, 1, 10)
FROM X1)

SELECT timeslot_name, oven_temperature
FROM X2
WHERE oven_temperature = max*_oven_temperature;

4. Registered User
Join Date
Sep 2005
Posts
20

## Still need help with Theshold

Thank you for you feedback, but my data is like this not as the example I sent in the initial post:

300 330 200 12-10-2013 13:30
177 238 333 12-10-2013 13:05
240 190 170 12-10-2013 12:45

How do I find the hour for the temperature closest to 340 degrees from this list above?

I tried your query it didn't work quit right on the above data set.

Originally Posted by gvee
Have a play with this:
Code:
```DECLARE @x table (
note char(5)
, temp int
);

INSERT INTO @x (note, temp)
VALUES ('temp1',   92)
, ('temp2',  108)
, ('temp3',    0)
, ('temp4',  284)
, ('temp5', 2192)
, ('temp6',  102)
, ('temp7',    0)
, ('temp8',   12)
;

DECLARE @threshold int = 340;

; WITH temps AS (
SELECT note
, temp
, Row_Number() OVER (ORDER BY temp DESC) As seq
FROM   @x
WHERE  temp < 1000
)
SELECT note
, temp
FROM   temps
WHERE  temp >= @threshold
OR seq = 1```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•