T-sql While Loop

Question

I would like to take a query i've written and make it more efficient with less code duplication. See query below. I feel that a WHILE Loop would give me a similar result but i'm not clear on the syntax to get the same outcome. 

SQL Query;

DECLARE @JEOPARDY TABLE(
	[GUID] VARCHAR(255),
	[JDATECOUNT] INT,
	[JEOPARDYDATE] DATETIME
)

INSERT @JEOPARDY([GUID], [JDATECOUNT]) 
SELECT * FROM @calldateDD C
WHERE C.DAYCOUNT <= 21

UPDATE @JEOPARDY 
SET JEOPARDYDATE =
	(
		CASE
			WHEN ((JDATECOUNT + 1) > 21) THEN DATEADD(dd,1,getdate())
			WHEN ((JDATECOUNT + 2) > 21) THEN DATEADD(dd,2,getdate())
			WHEN ((JDATECOUNT + 3) > 21) THEN DATEADD(dd,3,getdate())
			WHEN ((JDATECOUNT + 4) > 21) THEN DATEADD(dd,4,getdate())
			WHEN ((JDATECOUNT + 5) > 21) THEN DATEADD(dd,5,getdate())
			WHEN ((JDATECOUNT + 6) > 21) THEN DATEADD(dd,6,getdate())
			WHEN ((JDATECOUNT + 7) > 21) THEN DATEADD(dd,7,getdate())
			WHEN ((JDATECOUNT + 8) > 21) THEN DATEADD(dd,8,getdate())
			WHEN ((JDATECOUNT + 9) > 21) THEN DATEADD(dd,9,getdate())
			WHEN ((JDATECOUNT + 10) > 21) THEN DATEADD(dd,10,getdate())
			WHEN ((JDATECOUNT + 11) > 21) THEN DATEADD(dd,11,getdate())
			WHEN ((JDATECOUNT + 12) > 21) THEN DATEADD(dd,12,getdate())
			WHEN ((JDATECOUNT + 13) > 21) THEN DATEADD(dd,13,getdate())
			WHEN ((JDATECOUNT + 14) > 21) THEN DATEADD(dd,14,getdate())
			WHEN ((JDATECOUNT + 15) > 21) THEN DATEADD(dd,15,getdate())
			WHEN ((JDATECOUNT + 16) > 21) THEN DATEADD(dd,16,getdate())
			WHEN ((JDATECOUNT + 17) > 21) THEN DATEADD(dd,17,getdate())
			WHEN ((JDATECOUNT + 18) > 21) THEN DATEADD(dd,18,getdate())
			WHEN ((JDATECOUNT + 19) > 21) THEN DATEADD(dd,19,getdate())
			WHEN ((JDATECOUNT + 20) > 21) THEN DATEADD(dd,20,getdate())
			ELSE DATEADD(dd,22,getdate())
		END
	)

Output:

GUID  JDATECOUNT JEOPARDYDATE
CA968A7F-D96B-4C71-8238-B89B0BE4D755 0 2012-05-10 13:44:20.047
01626F89-0E36-4BB2-9B68-125D480364A0 2 2012-05-08 13:44:20.047
C3EAD131-19CE-4924-ABB3-916B767AD9DA 1 2012-05-10 13:44:20.047
ba23908b-791d-b93d-c476-4ca6247a5b46 8 2012-05-02 13:44:20.047
43bb41eb-57e6-d528-52a9-4cbc66fceb3f 1 2012-05-10 13:44:20.047
D7C2EAEA-4E6E-41C0-8D0C-7D9AF57CF8F3 20 2012-04-20 13:44:20.047
88051CF0-3BD0-42F4-8CEB-085A407C7E0D 1 2012-05-10 13:44:20.047
A5C217F6-FE0F-4B75-9F21-B21AE72FBEE5 6 2012-05-04 13:44:20.047
883F5330-4A54-4175-9A67-8BD0F7432447 1 2012-05-10 13:44:20.047
92170E3C-8B56-470A-8747-97FBD99CD36E 2 2012-05-08 13:44:20.047
F99166DE-4548-4419-B071-EB02A9ACAC0D 6 2012-05-04 13:44:20.047

Answer 1

Regarding the code duplication, it looks like you could go with a single case statement if I understand the issue correctly (see SQL 2008 R2 example below). You could also create another table that stores the value of days to be added given a JDATECOUNT value (e.g. store 19 as the days to add for JDATECOUNT = 3).

with nums(num) as (
	select 0
	union all
	select num + 1
	from nums
	where num < 30
)


select 
 n.num,
 
 case
  when n.num between 2 and 21 then 22 - n.num
  else 22
 end as value_added,
 
 case
  when n.num between 2 and 21 then dateadd(dd, 22 - n.num, getdate())
  else dateadd(dd, 22, getdate())
 end as date_dts
 
from nums n

---

website: jontav.com | Blog

Answer 2

You can approach this in an algebraic way:

i.e., If 22 - (JDATECOUNT + X) <= 0 then you need to add X days to the current date else add 22 days to the current date.

So X will be 22 - JDATECOUNT. Then you can find out number of days to be added to the date using the below query:

-- Create a sample table
DECLARE @MyTable TABLE
(
DateCount INT
)
DECLARE @intLimit AS INT = 50
DECLARE @intCounter AS INT = 1
-- Populate table
WHILE @intCounter <= @intLimit
	BEGIN
		INSERT INTO @MyTable VALUES (@intCounter)
		SET @intCounter = @intCounter + 1
	END
	
SELECT * FROM @MyTable
SELECT
	DateCount,
	22 - DateCount,
	CASE 
		WHEN (22 - DateCount) BETWEEN 0 AND 20 THEN DATEADD(dd, 22 - DateCount, GETDATE()) -- Count Upto 22
		WHEN (22 - DateCount) < 0 THEN DATEADD(dd, 1, GETDATE()) -- For count above 23
		ELSE DATEADD(dd, 22, GETDATE())
	END AS NewDate
FROM
	@MyTable

This is just a thought for the startup.

- Krishnakumar S

Answer 3

Although it's always fun and educational to challenge yourself to learn new and better ways to do things, keep in mind that your code is currently very self explanatory and easy to understand.  And since you have an editor with syntax highlighting, copy and paste, detailed error messages, syntax checking,  and other fancy tools (a.k.a. this isn't being done in punchcarsd), it's also quite easy to maintain.

In this particular case, it's hard, almost impossible to imagine that another approach will run any faster.  Same speed, probably, possibly with more style points, but sometimes, steady and simple wins races too.

So am I discouraging you from proceeding?  Not exactly.  Just pointing out that more complex isn't necessarily better.  Keep us posted when you get an answer, and run a few performance tests too?  :-)