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

    Question Unanswered: Finding the lowest value in a table greater than a certain value

    I need help finding the first value to cross a specific threshold in SQL Server

    LoadTemp1 LoadTemp2 LoadTemp3 TimeStamp
    300 350 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 design query to find the first LoadTemp* that cross a threshold of 340 degrees per TimeStamp.

    Please help. Thanks.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Unfortunately your data doesn't seem to be normalised so this won't be as easy to achieve.

    Give this a whirl and see how you get on.
    Code:
    DECLARE @threshold int = 340;
    
    ; WITH load_temperatures AS (
      SELECT LoadTemp1 As load_temperature
           , TimeStamp As time_stamp
      FROM   your_table
      UNION ALL
      SELECT LoadTemp2 As load_temperature
           , TimeStamp As time_stamp
      FROM   your_table
      UNION ALL
      SELECT LoadTemp3 As load_temperature
           , TimeStamp As time_stamp
      FROM   your_table
    )
    SELECT Min(time_stamp) As first_to_cross_threshold
    FROM   load_temperatures
    WHERE  load_temperature >= @threshold
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2012
    Posts
    213
    jazlady, you can also try something like this:

    Code:
    with CTE_UnPivot as
    (
        select [TimeStamp], LoadTemp
        from MyTable
        unpivot
            (LoadTemp for LoadTempCol in (LoadTemp1, LoadTemp2, LoadTemp3)
            ) as u 
    )
    
    select top 1 * 
    from CTE_UnPivot
    where 
        LoadTemp >= 340
    order by 
        LoadTemp
    Hope this helps.

  4. #4
    great idea sharing thanks...

  5. #5
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Is there any reason to denormalize the data with a repeated group? Why did you fail to post DDL? Not even a table name? Here is my guess.

    CREATE TABLE Loads
    (load_temp_1 INTEGER NOT NULL,
    load_temp_2 INTEGER NOT NULL,
    load_temp_3 INTEGER NOT NULL,
    load_timestamp DATETIME2(0) DEFAULT CURRENT_TIMESTAMP
    NOT NULL PRIMARY KEY);

    INSERT INTO Loads
    VALUES
    (300, 350, 200, '12-10-2013 13:30:00'),
    (177, 238, 333, '12-10-2013 13:05:00'),
    (240, 190, 170, '12-10-2013 12:45:00');

    SELECT MIN (load_timestamp)
    FROM Loads
    WHERE 340 <= load_temp_1
    OR 340 <= load_temp_2
    OR 340 <= load_temp_3;

  6. #6
    Join Date
    Sep 2005
    Posts
    19
    Provided Answers: 1

    Need help showing those values that don't cross 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 01:00:13.000	2014-02-05 01: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)
























    Quote Originally Posted by gvee View Post
    Unfortunately your data doesn't seem to be normalised so this won't be as easy to achieve.

    Give this a whirl and see how you get on.
    Code:
    DECLARE @threshold int = 340;
    
    ; WITH load_temperatures AS (
      SELECT LoadTemp1 As load_temperature
           , TimeStamp As time_stamp
      FROM   your_table
      UNION ALL
      SELECT LoadTemp2 As load_temperature
           , TimeStamp As time_stamp
      FROM   your_table
      UNION ALL
      SELECT LoadTemp3 As load_temperature
           , TimeStamp As time_stamp
      FROM   your_table
    )
    SELECT Min(time_stamp) As first_to_cross_threshold
    FROM   load_temperatures
    WHERE  load_temperature >= @threshold

  7. #7
    Join Date
    Sep 2005
    Posts
    19
    Provided Answers: 1

    Question How do I Find values that are Near the threshold value

    Gvee....

    If I use the With statement in the above previous post I am not getting any data for some of my license numbers for example:

    No rows are returned when I pass license number 10009 and Null for Lot. How do I handle this? There is no data to provide the SSRS parameter to plot a vertical benchmark line. I have 8 temperatures and I don't have any data for all 8 temperature reading.


    How do I handle this situation with no data returned? The License Number is selected by the enduser and it display a chart on the SSRS report based on data available. I don't have any data to plot the points so how do I keep the user from selecting a licenseNumber that doesn't have any data without have to make a big query to exclude them?

    Also, for those temperatures that are UNDER 340 (threshold), how do get the code to give me the hour so I can plot the vertical benchmark, when 280 was close to 340 but not at the end of 13 hours. Can you help?


    Sample data:

    Code:
    ageLoadTemp3	ageLoadTemp4	ageLoadTemp5	ageLoadTemp6	ageLoadTemp7	ageLoadTemp8	ageSampleTsDate		The Closest to 340 Degrees (Threshold)
    2192	334	338.3	2192	2192	2192	2/2/2014 14:34		338.3
    2194	2194	0	0	0	0	2/2/2014 14:34		No data
    2192	338.1	287.6	2192	2192	269.6	2/2/2014 14:35		338.1
    2192	334.5	338.7	2192	2192	2192	2/2/2014 14:35		338.7
    2194	2194	0	0	0	0	2/2/2014 14:35		No data
    2192	337.2	287	2192	2192	270.3	2/2/2014 14:36		337.2
    2192	333.8	338.1	2192	2192	2192	2/2/2014 14:36		338.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
  •