Solved

Help with nested case syntax issues

Posted on 2014-04-07
8
330 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
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:ScottPletcher
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
 

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 40

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:ScottPletcher
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:ScottPletcher
ID: 39987197
Can you just remove the WHERE clause?  That would run it for every [SENSOR ID] in the [sensor] table.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now