Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2005
    Posts
    19
    Provided Answers: 1

    Question 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
    	@LicenseNumber int = NULL, 
    	@Lot varchar(50) = NULL
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    	DECLARE @threshold int = (SELECT MAX(loadTempLL) FROM [AgedCartForZLotDWP])  --Max(LloadTempLL)  --340
    
    
    	; WITH load_temperatures AS (
    	SELECT AgeLoadTemp1 As load_temperature
    		, ageSampleTsDate As time_stamp
    		, AgeovenStartTimestamp
    		, AgeovenCompleteTimestamp
    		,ISNULL(DATEDIFF(MI,
    								(SELECT        MIN(ageSampleTsDate) AS Expr1
    								FROM            dbo.funGetAgeOvenTrendByCartWipLicense_JJ_11052013(@LicenseNumber,@Lot)  AS funGetAgeOvenTrendByCartWipLicense_2), 
    							ageSampleTsDate) / 60.0, 0) AS AgeSampleDateDiff
    	FROM dbo.funGetAgeOvenTrendByCartWipLicense_JJ_11052013(@LicenseNumber,@Lot)   fat
    		INNER JOIN dbo.ageOven ao 
    				ON fat.ageOvenKey = ao.ageOvenKey
    		INNER JOIN  [AgedCartForZLotDWP] ad
    				ON ad.agecartID = ao.agecartID or ad.agecartID = ao.ageCArtID2
    	UNION ALL
    	SELECT AgeLoadTemp2 As load_temperature
    		, ageSampleTsDate As time_stamp
    		, AgeovenStartTimestamp
    		, AgeovenCompleteTimestamp
    		,ISNULL(DATEDIFF(MI,
    								(SELECT        MIN(ageSampleTsDate) AS Expr1
    								FROM            dbo.funGetAgeOvenTrendByCartWipLicense_JJ_11052013(@LicenseNumber,@Lot)  AS funGetAgeOvenTrendByCartWipLicense_2), 
    							ageSampleTsDate) / 60.0, 0) AS AgeSampleDateDiff
    	FROM dbo.funGetAgeOvenTrendByCartWipLicense_JJ_11052013(@LicenseNumber,@Lot)   fat
    		INNER JOIN dbo.ageOven ao 
    				ON fat.ageOvenKey = ao.ageOvenKey
    		INNER JOIN  [AgedCartForZLotDWP] ad
    				ON ad.agecartID = ao.agecartID or ad.agecartID = ao.ageCArtID2
    	UNION ALL
    	SELECT AgeLoadTemp3 As load_temperature
    		, ageSampleTsDate As time_stamp
    		, AgeovenStartTimestamp
    		, AgeovenCompleteTimestamp
    		,ISNULL(DATEDIFF(MI,
    								(SELECT        MIN(ageSampleTsDate) AS Expr1
    								FROM            dbo.funGetAgeOvenTrendByCartWipLicense_JJ_11052013(@LicenseNumber,@Lot)  AS funGetAgeOvenTrendByCartWipLicense_2), 
    							ageSampleTsDate) / 60.0, 0) AS AgeSampleDateDiff
    	FROM dbo.funGetAgeOvenTrendByCartWipLicense_JJ_11052013(@LicenseNumber,@Lot)   fat
    		INNER JOIN dbo.ageOven ao 
    				ON fat.ageOvenKey = ao.ageOvenKey
    		INNER JOIN  [AgedCartForZLotDWP] ad
    				ON ad.agecartID = ao.agecartID or ad.agecartID = ao.ageCArtID2 
    	UNION ALL
    	SELECT AgeLoadTemp4 As load_temperature
    		, ageSampleTsDate As time_stamp
    		, AgeovenStartTimestamp
    		, AgeovenCompleteTimestamp
    		,ISNULL(DATEDIFF(MI,
    								(SELECT        MIN(ageSampleTsDate) AS Expr1
    								FROM            dbo.funGetAgeOvenTrendByCartWipLicense_JJ_11052013(@LicenseNumber,@Lot) AS funGetAgeOvenTrendByCartWipLicense_2), 
    							ageSampleTsDate) / 60.0, 0) AS AgeSampleDateDiff
    	FROM dbo.funGetAgeOvenTrendByCartWipLicense_JJ_11052013(@LicenseNumber,@Lot)   fat
    		INNER JOIN dbo.ageOven ao 
    				ON fat.ageOvenKey = ao.ageOvenKey
    		INNER JOIN  [AgedCartForZLotDWP] ad
    				ON ad.agecartID = ao.agecartID or ad.agecartID = ao.ageCArtID2 
    	UNION ALL
    	SELECT AgeLoadTemp5 As load_temperature
    		, ageSampleTsDate As time_stamp
    		, AgeovenStartTimestamp
    		, AgeovenCompleteTimestamp
    		,ISNULL(DATEDIFF(MI,
    								(SELECT        MIN(ageSampleTsDate) AS Expr1
    								FROM            dbo.funGetAgeOvenTrendByCartWipLicense_JJ_11052013(@LicenseNumber,@Lot)  AS funGetAgeOvenTrendByCartWipLicense_2), 
    							ageSampleTsDate) / 60.0, 0) AS AgeSampleDateDiff
    	FROM dbo.funGetAgeOvenTrendByCartWipLicense_JJ_11052013(@LicenseNumber,@Lot)   fat
    		INNER JOIN dbo.ageOven ao 
    				ON fat.ageOvenKey = ao.ageOvenKey
    		INNER JOIN  [AgedCartForZLotDWP] ad
    				ON ad.agecartID = ao.agecartID or ad.agecartID = ao.ageCArtID2 
    	UNION ALL
    	SELECT AgeLoadTemp6 As load_temperature
    		, ageSampleTsDate As time_stamp
    		, AgeovenStartTimestamp
    		, AgeovenCompleteTimestamp
    		,ISNULL(DATEDIFF(MI,
    								(SELECT        MIN(ageSampleTsDate) AS Expr1
    								FROM            dbo.funGetAgeOvenTrendByCartWipLicense_JJ_11052013(@LicenseNumber,@Lot)  AS funGetAgeOvenTrendByCartWipLicense_2), 
    							ageSampleTsDate) / 60.0, 0) AS AgeSampleDateDiff
    	FROM dbo.funGetAgeOvenTrendByCartWipLicense_JJ_11052013(@LicenseNumber,@Lot)   fat
    		INNER JOIN dbo.ageOven ao 
    				ON fat.ageOvenKey = ao.ageOvenKey
    		INNER JOIN  [AgedCartForZLotDWP] ad
    				ON ad.agecartID = ao.agecartID or ad.agecartID = ao.ageCArtID2 
    	UNION ALL
    	SELECT AgeLoadTemp7 As load_temperature
    		, ageSampleTsDate As time_stamp
    		, AgeovenStartTimestamp
    		, AgeovenCompleteTimestamp
    		,ISNULL(DATEDIFF(MI,
    								(SELECT        MIN(ageSampleTsDate) AS Expr1
    								FROM            dbo.funGetAgeOvenTrendByCartWipLicense_JJ_11052013(@LicenseNumber,@Lot) AS funGetAgeOvenTrendByCartWipLicense_2), 
    							ageSampleTsDate) / 60.0, 0) AS AgeSampleDateDiff
    	FROM dbo.funGetAgeOvenTrendByCartWipLicense_JJ_11052013(@LicenseNumber,@Lot)  fat
    		INNER JOIN dbo.ageOven ao 
    				ON fat.ageOvenKey = ao.ageOvenKey
    		INNER JOIN  [AgedCartForZLotDWP] ad
    				ON ad.agecartID = ao.agecartID or ad.agecartID = ao.ageCArtID2 
    	UNION ALL
    	SELECT AgeLoadTemp8 As load_temperature
    		, ageSampleTsDate As time_stamp
    		, AgeovenStartTimestamp
    		, AgeovenCompleteTimestamp
    		,ISNULL(DATEDIFF(MI,
    								(SELECT        MIN(ageSampleTsDate) AS Expr1
    								FROM            dbo.funGetAgeOvenTrendByCartWipLicense_JJ_11052013(@LicenseNumber,@Lot) AS funGetAgeOvenTrendByCartWipLicense_2), 
    							ageSampleTsDate) / 60.0, 0) AS AgeSampleDateDiff
    	FROM dbo.funGetAgeOvenTrendByCartWipLicense_JJ_11052013(@LicenseNumber,@Lot)   fat
    		INNER JOIN dbo.ageOven ao 
    				ON fat.ageOvenKey = ao.ageOvenKey
    		INNER JOIN  [AgedCartForZLotDWP] ad
    				ON ad.agecartID = ao.agecartID or ad.agecartID = ao.ageCArtID2 
    
    	)
    	SELECT TOP 1 Min(time_stamp) As first_to_cross_threshold, AgeSampleDateDiff, AgeovenStartTimestamp
    		, AgeovenCompleteTimestamp
    	FROM   load_temperatures
    	WHERE  load_temperature >= @threshold and load_temperature < 1000
    	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. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    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');

    Now let's do your table:

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

    INSERT INTO Oven_Readings
    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
    FROM Oven_Readings),

    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. #4
    Join Date
    Sep 2005
    Posts
    19
    Provided Answers: 1

    Question 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:

    LoadTemp1 LoadTemp2 LoadTemp3 TimeStamp
    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.


    Quote Originally Posted by gvee View Post
    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
  •