asked on

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.

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

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'

cjake2299

ASKER

@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.

cjake2299

ASKER

@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.

Sharath S

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

Scott Pletcher

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 :-) .

cjake2299

ASKER

@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.

Scott Pletcher

Can you just remove the WHERE clause? That would run it for every [SENSOR ID] in the [sensor] table.