Solved

Help with nested case syntax issues

Posted on 2014-04-07
8
337 Views
Last Modified: 2014-04-08
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.
0
Comment
Question by:cjake2299
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39983582
<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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39983691
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
 

Author Comment

by:cjake2299
ID: 39983882
@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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:cjake2299
ID: 39983967
@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
 
LVL 41

Expert Comment

by:Sharath
ID: 39984677
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39986080
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
 

Author Comment

by:cjake2299
ID: 39987119
@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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39987197
Can you just remove the WHERE clause?  That would run it for every [SENSOR ID] in the [sensor] table.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question