SQL GROUP BY query dilemma/question

Toggle sidebar Toggle sidebar
Homerboy

Homerboy

Lifer
Mar 1, 2000
30,890
5,001
126
I have a query:

SELECT
CODE,

MIN(DATE) AS 'Oldest',
COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) < -5) THEN FILENO END) AS '> 5 Days Behind',
COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = -5) THEN FILENO END) AS '-5',
COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = -4) THEN FILENO END) AS '-4',
COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = -3) THEN FILENO END) AS '-3',
COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = -2) THEN FILENO END) AS '-2',
COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = -1) THEN FILENO END) AS '-1',
COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = 0) THEN FILENO END) AS 'TODAY',
COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = 1) THEN FILENO END) AS '1',
COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = 2) THEN FILENO END) AS '2',
COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = 3) THEN FILENO END) AS '3',
COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = 4) THEN FILENO END) AS '4',
COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = 5) THEN FILENO END) AS '5',
COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = 6) THEN FILENO END) AS '6',
COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = 7) THEN FILENO END) AS '7',
COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = 8) THEN FILENO END) AS '8',
COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = 9) THEN FILENO END) AS '9',
COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = 10) THEN FILENO END) AS '10',
COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = 11) THEN FILENO END) AS '11',
COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = 12) THEN FILENO END) AS '12',
COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = 13) THEN FILENO END) AS '13',
COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = 14) THEN FILENO END) AS '14',
COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) > 14) THEN FILENO END) AS '>=14 Days Ahead',
COUNT(*) AS "TOTAL"

FROM DIARYINT
WHERE CODE IN (45, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 310, 320,
321, 327, 328, 329, 330, 331, 332, 333, 334, 335, 336, 337, 344, 347, 364, 379, 381, 382, 383, 384, 385, 386, 387, 390, 391, 392, 393, 398, 399) OR
CODE IN (400, 401, 403, 404, 405, 407, 409, 410, 411, 412, 413, 414, 415, 416, 417, 419, 420, 421, 422, 423, 424, 425, 426, 427, 428, 429,
440, 441, 442, 443, 451, 460, 462, 463, 464, 465, 466, 467, 468, 470, 490, 491, 492, 493, 494, 495, 496, 499, 530, 570, 571, 340, 341)
GROUP BY CODE
Click to expand...


But sometimes one (or more) of those CODES have no hits in the database. So it will not return a row for those &#8220;empty&#8221; counts/rows.
I have 108 CODES listed there in the WHERE clause.
No matter what, I want 108 rows to be returned.
is that possible? If so, how?
Somehow to replace any "NULL" GROUP with "0" or something.
 
Evadman

Evadman

Administrator Emeritus<br>Elite Member
Feb 18, 2001
30,990
5
81
What is the DBMS?

<edit>
I'm feeling really lazy, so I just did a crap version in MS SQL that should work on almost any version. If you are using 2005 or newer, use pivot. There are about a billion ways to do this, and this is not performance tested. But it should have the least amount of disk hits, as the table variable should stay in memory instead of tempdb.

Code: 
DECLARE @CodeTable TABLE (CODE INT)
INSERT INTO @CodeTable (CODE) VALUES(45)
INSERT INTO @CodeTable (CODE) VALUES(134)
INSERT INTO @CodeTable (CODE) VALUES(135)
INSERT INTO @CodeTable (CODE) VALUES(136)
INSERT INTO @CodeTable (CODE) VALUES(137)
INSERT INTO @CodeTable (CODE) VALUES(138)
INSERT INTO @CodeTable (CODE) VALUES(139)
INSERT INTO @CodeTable (CODE) VALUES(140)
INSERT INTO @CodeTable (CODE) VALUES(141)
INSERT INTO @CodeTable (CODE) VALUES(142)
INSERT INTO @CodeTable (CODE) VALUES(143)
INSERT INTO @CodeTable (CODE) VALUES(144)
INSERT INTO @CodeTable (CODE) VALUES(145)
INSERT INTO @CodeTable (CODE) VALUES(146)
INSERT INTO @CodeTable (CODE) VALUES(147)
INSERT INTO @CodeTable (CODE) VALUES(148)
INSERT INTO @CodeTable (CODE) VALUES(149)
INSERT INTO @CodeTable (CODE) VALUES(150)
INSERT INTO @CodeTable (CODE) VALUES(151)
INSERT INTO @CodeTable (CODE) VALUES(152)
INSERT INTO @CodeTable (CODE) VALUES(153)
INSERT INTO @CodeTable (CODE) VALUES(154)
INSERT INTO @CodeTable (CODE) VALUES(155)
INSERT INTO @CodeTable (CODE) VALUES(156)
INSERT INTO @CodeTable (CODE) VALUES(310)
INSERT INTO @CodeTable (CODE) VALUES(320)
INSERT INTO @CodeTable (CODE) VALUES(321)
INSERT INTO @CodeTable (CODE) VALUES(327)
INSERT INTO @CodeTable (CODE) VALUES(328)
INSERT INTO @CodeTable (CODE) VALUES(329)
INSERT INTO @CodeTable (CODE) VALUES(330)
INSERT INTO @CodeTable (CODE) VALUES(331)
INSERT INTO @CodeTable (CODE) VALUES(332)
INSERT INTO @CodeTable (CODE) VALUES(333)
INSERT INTO @CodeTable (CODE) VALUES(334)
INSERT INTO @CodeTable (CODE) VALUES(335)
INSERT INTO @CodeTable (CODE) VALUES(336)
INSERT INTO @CodeTable (CODE) VALUES(337)
INSERT INTO @CodeTable (CODE) VALUES(344)
INSERT INTO @CodeTable (CODE) VALUES(347)
INSERT INTO @CodeTable (CODE) VALUES(364)
INSERT INTO @CodeTable (CODE) VALUES(379)
INSERT INTO @CodeTable (CODE) VALUES(381)
INSERT INTO @CodeTable (CODE) VALUES(382)
INSERT INTO @CodeTable (CODE) VALUES(383)
INSERT INTO @CodeTable (CODE) VALUES(384)
INSERT INTO @CodeTable (CODE) VALUES(385)
INSERT INTO @CodeTable (CODE) VALUES(386)
INSERT INTO @CodeTable (CODE) VALUES(387)
INSERT INTO @CodeTable (CODE) VALUES(390)
INSERT INTO @CodeTable (CODE) VALUES(391)
INSERT INTO @CodeTable (CODE) VALUES(392)
INSERT INTO @CodeTable (CODE) VALUES(393)
INSERT INTO @CodeTable (CODE) VALUES(398)
INSERT INTO @CodeTable (CODE) VALUES(399)
INSERT INTO @CodeTable (CODE) VALUES(400)
INSERT INTO @CodeTable (CODE) VALUES(401)
INSERT INTO @CodeTable (CODE) VALUES(403)
INSERT INTO @CodeTable (CODE) VALUES(404)
INSERT INTO @CodeTable (CODE) VALUES(405)
INSERT INTO @CodeTable (CODE) VALUES(407)
INSERT INTO @CodeTable (CODE) VALUES(409)
INSERT INTO @CodeTable (CODE) VALUES(410)
INSERT INTO @CodeTable (CODE) VALUES(411)
INSERT INTO @CodeTable (CODE) VALUES(412)
INSERT INTO @CodeTable (CODE) VALUES(413)
INSERT INTO @CodeTable (CODE) VALUES(414)
INSERT INTO @CodeTable (CODE) VALUES(415)
INSERT INTO @CodeTable (CODE) VALUES(416)
INSERT INTO @CodeTable (CODE) VALUES(417)
INSERT INTO @CodeTable (CODE) VALUES(419)
INSERT INTO @CodeTable (CODE) VALUES(420)
INSERT INTO @CodeTable (CODE) VALUES(421)
INSERT INTO @CodeTable (CODE) VALUES(422)
INSERT INTO @CodeTable (CODE) VALUES(423)
INSERT INTO @CodeTable (CODE) VALUES(424)
INSERT INTO @CodeTable (CODE) VALUES(425)
INSERT INTO @CodeTable (CODE) VALUES(426)
INSERT INTO @CodeTable (CODE) VALUES(427)
INSERT INTO @CodeTable (CODE) VALUES(428)
INSERT INTO @CodeTable (CODE) VALUES(429)
INSERT INTO @CodeTable (CODE) VALUES(440)
INSERT INTO @CodeTable (CODE) VALUES(441)
INSERT INTO @CodeTable (CODE) VALUES(442)
INSERT INTO @CodeTable (CODE) VALUES(443)
INSERT INTO @CodeTable (CODE) VALUES(451)
INSERT INTO @CodeTable (CODE) VALUES(460)
INSERT INTO @CodeTable (CODE) VALUES(462)
INSERT INTO @CodeTable (CODE) VALUES(463)
INSERT INTO @CodeTable (CODE) VALUES(464)
INSERT INTO @CodeTable (CODE) VALUES(465)
INSERT INTO @CodeTable (CODE) VALUES(466)
INSERT INTO @CodeTable (CODE) VALUES(467)
INSERT INTO @CodeTable (CODE) VALUES(468)
INSERT INTO @CodeTable (CODE) VALUES(470)
INSERT INTO @CodeTable (CODE) VALUES(490)
INSERT INTO @CodeTable (CODE) VALUES(491)
INSERT INTO @CodeTable (CODE) VALUES(492)
INSERT INTO @CodeTable (CODE) VALUES(493)
INSERT INTO @CodeTable (CODE) VALUES(494)
INSERT INTO @CodeTable (CODE) VALUES(495)
INSERT INTO @CodeTable (CODE) VALUES(496)
INSERT INTO @CodeTable (CODE) VALUES(499)
INSERT INTO @CodeTable (CODE) VALUES(530)
INSERT INTO @CodeTable (CODE) VALUES(570)
INSERT INTO @CodeTable (CODE) VALUES(571)
INSERT INTO @CodeTable (CODE) VALUES(340)
INSERT INTO @CodeTable (CODE) VALUES(341)

SELECT
    TTB.CODE,
    MIN(DATE) AS 'Oldest',
    COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) < -5) THEN FILENO END) AS '> 5 Days Behind',
    COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = -5) THEN FILENO END) AS '-5',
    COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = -4) THEN FILENO END) AS '-4',
    COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = -3) THEN FILENO END) AS '-3',
    COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = -2) THEN FILENO END) AS '-2',
    COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = -1) THEN FILENO END) AS '-1',
    COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = 0) THEN FILENO END) AS 'TODAY',
    COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = 1) THEN FILENO END) AS '1',
    COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = 2) THEN FILENO END) AS '2',
    COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = 3) THEN FILENO END) AS '3',
    COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = 4) THEN FILENO END) AS '4',
    COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = 5) THEN FILENO END) AS '5',
    COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = 6) THEN FILENO END) AS '6',
    COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = 7) THEN FILENO END) AS '7',
    COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = 8) THEN FILENO END) AS '8',
    COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = 9) THEN FILENO END) AS '9',
    COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = 10) THEN FILENO END) AS '10',
    COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = 11) THEN FILENO END) AS '11',
    COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = 12) THEN FILENO END) AS '12',
    COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = 13) THEN FILENO END) AS '13',
    COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) = 14) THEN FILENO END) AS '14',
    COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) > 14) THEN FILENO END) AS '>=14 Days Ahead',
    COUNT(*) AS "TOTAL"
FROM 
    @CodeTable as TTB LEFT OUTER JOIN DIARYINT ON TTB.CODE = DIARYINT.CODE
GROUP BY 
    TTB.CODE
 
Last edited:
KentState

KentState

Diamond Member
Oct 19, 2001
8,397
393
126
I would create either a lookup table or at least a temp table with all the of code values and right join to it.
 
Homerboy

Homerboy

Lifer
Mar 1, 2000
30,890
5,001
126
Thanks guys.

I figured there was a way to "predefine" a fixed list of values to return, and it appears this is exactly what Evadman has outlined.

However, when using his code, I'm getting error(s) thrown:

Msg 102, Level 15, State 1, Line 109
Incorrect syntax near 'INT'.

1 error for each use of INT.
 
Homerboy

Homerboy

Lifer
Mar 1, 2000
30,890
5,001
126
Oh wait, I'm guessing the error is due to the CODE column datatype not being an integer.... maybe I should change that. I guess it always would be a 1-3 digit integer.
 
Homerboy

Homerboy

Lifer
Mar 1, 2000
30,890
5,001
126
Hmmmmm. Guess it wasn't and integer problem.
Switched to DT_I4 and still throwing that error.
 
KB

KB

Diamond Member
Nov 8, 1999
5,406
389
126
The PIVOT would be the fastest and cleanest.

Heres an example.

Code: 
SELECT CODE, [-6] AS '>5 Days behind', [-5] AS [-5], [-4] AS [-4], [-3] AS [-3], [-2] As [-2], [-1] As [-1], [0] As [0], [1] As [1], [2] As [2], [3] As [3], [4] As [4], [5] As [5], [6] AS [6], [7] AS [7], [8] AS [8], [9] AS [9], [10] AS [10], [11] AS [11], [12] AS [12], [13] AS [13], [14] AS [14], [15] as '> 14 Days'
FROM (
	SELECT CODE, (CASE WHEN DATEDIFF(DAY, GETDATE(), DATE) < -5 THEN -6
							WHEN DATEDIFF(DAY, GETDATE(), DATE) > 14 THEN 15
							ELSE DATEDIFF(DAY, GETDATE(), DATE) END) As DayInt, 1 As QTY
	FROM DIARYINT) p
	PIVOT (COUNT(QTY) FOR CODE IN ([-6], [-5], [-4], [-3], [-2], [-1], [0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15])) AS pvt
ORDER BY CODE
GO
 
Homerboy

Homerboy

Lifer
Mar 1, 2000
30,890
5,001
126
KB said:
The PIVOT would be the fastest and cleanest.

Heres an example.

Code: 
SELECT CODE, [-6] AS '>5 Days behind', [-5] AS [-5], [-4] AS [-4], [-3] AS [-3], [-2] As [-2], [-1] As [-1], [0] As [0], [1] As [1], [2] As [2], [3] As [3], [4] As [4], [5] As [5], [6] AS [6], [7] AS [7], [8] AS [8], [9] AS [9], [10] AS [10], [11] AS [11], [12] AS [12], [13] AS [13], [14] AS [14], [15] as '> 14 Days'
FROM (
	SELECT CODE, (CASE WHEN DATEDIFF(DAY, GETDATE(), DATE) < -5 THEN -6
							WHEN DATEDIFF(DAY, GETDATE(), DATE) > 14 THEN 15
							ELSE DATEDIFF(DAY, GETDATE(), DATE) END) As DayInt, 1 As QTY
	FROM DIARYINT) p
	PIVOT (COUNT(QTY) FOR CODE IN ([-6], [-5], [-4], [-3], [-2], [-1], [0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15])) AS pvt
ORDER BY CODE
GO
Click to expand...

This solution throws errors too:


Msg 207, Level 16, State 1, Line 1
Invalid column name 'CODE'.


'CODE' is most definitely a column in the DIARYINT table... so not sure why that's causing an issue.
 
Evadman

Evadman

Administrator Emeritus<br>Elite Member
Feb 18, 2001
30,990
5
81
BoberFett said:
Yep, an outer join is the easiet way, and that's what Evadman did albeit with a variable table.
Click to expand...

Correct. I assumed that Homerboy couldn't change the database architecture. If he can, then having a table of possible values would be another good option.

Homerboy said:
I'm sorry I should have pointed out too, the DBMS is MS SQL R2.
Click to expand...

The code I posted works fine in MS SQL 2000, MS SQL 2005, MS SQL 2008 and MS SQL 2008R2. It is based upon the table names and such that you posted in the OP.

KB said:
The PIVOT would be the fastest and cleanest.
Click to expand...

Pivot will not return values of CODE without data.
 
Homerboy

Homerboy

Lifer
Mar 1, 2000
30,890
5,001
126
Evadman said:
Correct. I assumed that Homerboy couldn't change the database architecture. If he can, then having a table of possible values would be another good option.
Click to expand...

I can change the architecture /design if need be.

Evadman said:
The code I posted works fine in MS SQL 2000, MS SQL 2005, MS SQL 2008 and MS SQL 2008R2. It is based upon the table names and such that you posted in the OP.
Click to expand...

I copied and pasted directly into a query window within SSMS and it throws the error(s):

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'INT'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'INT'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'INT'.
...
Msg 102, Level 15, State 1, Line 109
Incorrect syntax near 'INT'.
Click to expand...

If you're familiar with SSMS, in the query window all the "INT" references are underlined in red too, indicating an issue with the code/syntax.
 
Homerboy

Homerboy

Lifer
Mar 1, 2000
30,890
5,001
126
LOL i see your ninja edit on your post now... and, coincidentally, I had worked on the solution on my own and was coming back here to post it.
 
Homerboy

Homerboy

Lifer
Mar 1, 2000
30,890
5,001
126
Can I add another log on this fire?

is it possible to say add more rows to the @CodeTable.... but not just by CODE (DIARYINT.CODE)? I would still want all 100+ existing rows JUST defined by DIARYINT.CODE as we have established here. These additional rows would be subsets of those rows. Does that make sense?

What I would also like are rows() that are defined as WHERE (DIARYINT.CODE = '45' AND MASTER.VENUE_1 >= '1' AND MASTER.VENUE_1 <= '40')

MASTER and DIARYINT tables are joined by 'FILENO'
 
BoberFett

BoberFett

Lifer
Oct 9, 1999
37,562
9
81
You can add any additional columns you need to @CodeTable and join on those as well.

Code: 
DECLARE @CodeTable TABLE (CODE INT, VENUEMIN INT, VENUEMAX INT)
INSERT INTO @CodeTable (CODE) VALUES(45, 1, 40)

SELECT
    TTB.CODE,
    TTB.VENUEMIN,
    TTB.VENUEMAX,
    MIN(DATE) AS 'Oldest',
    COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) < -5) THEN FILENO END) AS '> 5 Days Behind',
Days Ahead',
    COUNT(*) AS "TOTAL"
FROM 
    DIARYINT JOIN MASTER ON DIARYINT.FILENO = MASTER.FILENO
    RIGHT OUTER JOIN @CodeTable as TTB ON TTB.CODE = DIARYINT.CODE AND MASTER.VENUE_1 >= TTB.MINVENUE AND MASTER.VENUE_1 <= TTB.MAXVENUE
GROUP BY 
    TTB.CODE, TTB.VENUEMIN, TTB.VENUEMAX

Or something along those lines. I can't vouch for syntax because I don't have SQL running on anything at the moment, but hopefully you get the idea.
 
Last edited:
Homerboy

Homerboy

Lifer
Mar 1, 2000
30,890
5,001
126
BoberFett said:
You can add any additional columns you need to @CodeTable and join on those as well.

Code: 
DECLARE @CodeTable TABLE (CODE INT, VENUEMIN INT, VENUEMAX INT)
INSERT INTO @CodeTable (CODE) VALUES(45, 1, 40)

SELECT
    TTB.CODE,
    MIN(DATE) AS 'Oldest',
    COUNT(CASE WHEN (DATEDIFF(DAY,GETDATE(),DATE) < -5) THEN FILENO END) AS '> 5 Days Behind',
Days Ahead',
    COUNT(*) AS "TOTAL"
FROM 
    @CodeTable as TTB LEFT OUTER JOIN DIARYINT ON TTB.CODE = DIARYINT.CODE [B]AND DIARYINT.VENUE_1 >= TTB.MINVENUE AND DIARYINT.VENUE_1 <= TTB.MAXVENUE[/B]
GROUP BY 
    TTB.CODE[B], TTB.VENUEMIN, TTB. VENUEMAX[/B]
Click to expand...

This might just do the trick Mr. Swanson.
I will mess around with it after lunch. I have Excel right now doing all that jibberish for me, but its slow and painful.
 
Homerboy

Homerboy

Lifer
Mar 1, 2000
30,890
5,001
126
Throwing the following error:


Msg 110, Level 15, State 1, Line 2
There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.


(I had to clean up that extra line you had in there of "Days Ahead',").. I assume that was just a bad copy/paste you had.
 
BoberFett

BoberFett

Lifer
Oct 9, 1999
37,562
9
81
My bad

Code: 
DECLARE @CodeTable TABLE (CODE INT, VENUEMIN INT, VENUEMAX INT)
INSERT INTO @CodeTable (CODE, VENUEMIN, VENUEMAX) VALUES(45, 1, 40)
 
Homerboy

Homerboy

Lifer
Mar 1, 2000
30,890
5,001
126
How lame.

SSMS won't let me save that query that you guys laid out for me as a view.
and Excel chokes on it if I use it as a straight SQL database query.
 
K

Kr@n

Member
Feb 25, 2010
44
0
0
You cannot have TRANSACT-SQL in a view definition.

You can use a table function or a view instead to build your @CodeTable.

Code: 
CREATE FUNCTION [dbo].[fn_SEQUENCE] ( @START float, @NB int, @STEP float )
RETURNS @SEQUENCE TABLE (SEQ float)
AS
BEGIN

DECLARE @V float
DECLARE @C int
SET @V = @START
SET @C = 0

WHILE @C < @NB
BEGIN
INSERT INTO @SEQUENCE
VALUES (@V)
SELECT @V = @V + @STEP, @C = @C + 1
END
(a more complex "sequence" can be build, even involving other tables, dates, several columns, etc.)

The function solution is especially useful if you can programmatically build your CodeTable (codes being a kind of computable sequence). If not, create a view with several SELECT clauses and UNION ALL statements :

Code: 
SELECT 45, 1, 40
UNION ALL SELECT 315, 40, 50
UNION ALL SELECT 100, 50, 70
...
 
You must log in or register to reply here.

TRENDING THREADS

COMPANY
RESOURCES
FOLLOW
Top Bottom