Help with nested case syntax issues

Greeting EE!

I am trying to write a T-SQL Script that will auto-populate a table.  It looks at another table for the list of current sensors, then for each sensor write the possible range of values for temperature, day and hour.  I thought that a nested case would work, but with the errors I'm currently experiencing I may be going about this wrong.  This is the latest rendition of the code I have so far (I don't even remember the other versions at this point):

DECLARE @SENSORName as NVARCHAR;
DECLARE @SENSORNUm as INT;
DECLARE @TEMP as SMALLINT;
DECLARE @Day as TINYINT;
DECLARE @Hour as TINYINT;
DECLARE @MinTemp as SMALLINT;
DECLARE @MaxTemp as SMALLINT;
SET @SENSORNum = 0;
SET @Day = 1;
SET @Hour = 1;
SET @MinTemp = -80;
SET @MaxTemp = 135;
SET @TEMP = @MinTemp;
SET @SENSORName = (SELECT MIN([SENSOR_ID]) from SENSOR where @SENSORNum < ID)
WHILE @SENSORName is not null
BEGIN
	INSERT INTO [Hourly]([SENSOR],[TEMP],[Day],[Hour])
	VALUES(@SENSORName, @TEMP, @Day, @Hour)
	SET @Hour =	CASE
		WHEN @Hour = 24
			THEN @Hour = 1 
			SET @Day = CASE(
					WHEN @Day = 7
						THEN SET @Day = 1
					ELSE SET @Day = @day + 1
		ELSE SET @Hour = @Hour + 1
END;

Open in new window


Yes, I don't have the TEMP incrementing yet, because if I can't get the hour and day to increment correctly the TEMP will never increment either.  I had created something similar in a VBScript that worked well, but I was hoping that a T-SQL Statement might yield better performance.

All ideas are appreciated.
cjake2299Asked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
<knee-jerk reaction>

Give the below a whirl..
BEGIN
   INSERT INTO [Hourly]([SENSOR],[TEMP],[Day],[Hour])
   VALUES(@SENSORName, @TEMP, @Day, @Hour)

   SELECT @Hour = CASE WHEN 24 THEN 1 ELSE @Hour + 1 END
 
   SELECT @Day = CASE WHEN 7 THEN 1 ELSE @day + 1 END
END

Open in new window

In case it helps, I have an article on  SQL Server CASE Solutions with a wompload of examples.
0
 
Scott PletcherSenior DBACommented:
INNER/CROSS JOIN is by far the most effective and efficient way to generate rows, much preferable to looping.  So something along the lines below -- not sure of the specific ranges you need.

;WITH
cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT -[100s].digit * 100 + -[10s].digit * 10 + -[1s].digit AS tally
    FROM cteDigits [1s]
    CROSS JOIN cteDigits [10s]
    CROSS JOIN cteDigits [100s]
    UNION ALL
    SELECT [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS tally
    FROM cteDigits [1s]
    CROSS JOIN cteDigits [10s]
    CROSS JOIN cteDigits [100s]
)
INSERT INTO [Hourly]([SENSOR],[TEMP],[Day],[Hour])
SELECT [SENSOR_ID], temp.tally, day.tally, hour.tally
FROM SENSOR
INNER JOIN cteTally temp ON
    temp.tally BETWEEN -80 AND 135
INNER JOIN cteTally hour ON
    hour.tally BETWEEN 0 AND 23
INNER JOIN cteTally day ON
    day.tally BETWEEN 1 AND 31
WHERE
    [SENSOR_ID] < '0'
0
 
cjake2299Author Commented:
@Jim,  I'll take a look at your article.  I might be over thinking things and trying to do T-SQL as C# again...hard to shift between the two sometimes.  I'll pose this though, the @Day shouldn't increment unless the @Hour is 1, AND, if the @Day is 7, it should go back to 1 (representing Saturday - Sunday).

@Scott,  that's an interesting approach.  I need to read up on that a little more and see how it might work for what I have.

I'll let both of you know what results I get from my efforts shortly.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
cjake2299Author Commented:
@Jim, I read through your article and made some changes to my script and it is running now.  Might take just a little-bit of time on my test system (like I said, 54,000 possible rows for each sensor).  So far it looks like it might work, but won't know for sure until it is complete and I can evaluate the data that was generated.  This is my new modified code:

DECLARE @SENSORName as NVARCHAR;
DECLARE @SENSORNUm as INT;
DECLARE @TEMP as SMALLINT;
DECLARE @Day as TINYINT;
DECLARE @Hour as TINYINT;
DECLARE @MinTemp as SMALLINT;
DECLARE @MaxTemp as SMALLINT;
SET @SENSORNum = 0;
SET @Day = 1;
SET @Hour = 1;
SET @MinTemp = -80;
SET @MaxTemp = 135;
SET @TEMP = @MinTemp;
SET @SENSORName = (SELECT MIN([SENSOR_ID]) from SENSOR where @SENSORNum < ID)
WHILE @SENSORName is not null
BEGIN
	INSERT INTO [Hourly]([SENSOR],[TEMP],[Day],[Hour])
	VALUES(@SENSORName, @TEMP, @Day, @Hour)
SET @Hour = CASE WHEN @Hour = 24 THEN 1	Else @Hour + 1 END
SET @Day = CASE 
	WHEN @Hour = 1 THEN
		CASE WHEN @Day = 7 THEN 1 ELSE @Day + 1 End
	ELSE @Day END
SET @TEMP = CASE
	WHEN @Day = 1 THEN
		CASE WHEN @Temp = 135 then -80 else @TEMP + 1 END
	ELSE @Day END
END

Open in new window


@Scott,  I ran yours first and it executed successfully with no errors, but it did not enter any data into the table.  I'm going to read up on the method you proposed to see what I might need to change.  Hopefully it'll work and perform better than the loop.  Again, I'll let you both know the results when I have some.
0
 
SharathData EngineerCommented:
give a try
INSERT INTO [Hourly]([SENSOR],[TEMP],[Day],[Hour])
SELECT [SENSOR],Temp_Val,Day_Val,Hour_Val
  FROM SENSOR S
  CROSS JOIN (SELECT number Hour_Val  FROM master..spt_values WHERE type = 'P' AND number BETWEEN 1 AND 24) Hours_Table
  CROSS JOIN (SELECT number Day_Val FROM master..spt_values WHERE type = 'P' AND number BETWEEN 1 AND 7) Days_Table
  CROSS JOIN (SELECT number-80 Temp_Val  FROM master..spt_values WHERE type = 'P' AND number <= 135+80) temp_Table

Open in new window

0
 
Scott PletcherSenior DBACommented:
Make sure the condition I put on there is correct and matches what you want:

WHERE
    [SENSOR_ID] < '0'

The WHERE might be excluding all rows now; I don't know what your specific data values are, of course :-) .
0
 
cjake2299Author Commented:
@Scott,  I see now.  Unfortunately there HAS to be a loop, because the number of sensors in each database is different, and it has to create the rows for each sensor.  Unfortunately you're method of removing the LOOP will not work unless I place it into a loop as well, or manually run it for each sensor in the DB.  That won't work, because when they add sensors I've modified the creation script to drop this table completely and rebuild it.  I see not other way without the use of a loop.
0
 
Scott PletcherSenior DBACommented:
Can you just remove the WHERE clause?  That would run it for every [SENSOR ID] in the [sensor] table.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.