Posts tagged: sql

Aug 25 2017

Understanding the recursive CTE termination check

In this sqlfiddle...

http://sqlfiddle.com/#!6/b6587/6

I am getting the following error....

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

I understand that there needs to be a "termination check" in the where clause of the second select of the CTE. Even if you uncomment the WHERE clause I get the same error.

I am just trying to understand 1) why it is needed at all...after all each order row has a relationship to each customer row and 2) since a "termination check" IS required what would that look like to get this example to work.

BTW, the output I would like to see for this query is as follows.

1,'George', 'Patton','',''
1,'','','<some date>', 'tank'
1,'','','<some date>', 'plane'
2,'Lewie', 'Puller','',''
2,'','','<some date>', 'Rifle'
2,'','','<some date>', 'Hand Grenade'

By the way, feel free to comment on other ways to get this kind of result (without using a recursive cte) but also without using a cursor or temp table. I would like to do this with a set-based operation.

EDIT

For the record, I know a join would work but I had a reason for asking about recursion. To give you context, I am working on a result set that is an EXPORT file. Each row is only one column called LINE. Furthermore, the export file needs to output in the order of my example...details need to be under header rows and that pattern needs to repeat...header/details ... header/details. I thought that maybe recursion could solve this problem. But I know simple join could solve it too as long as I can get the rows into the correct order...header / detail ... header / detail, maybe by having some way to do a column for sorting that numbers the output in the right way.

My export will NOT be depending on the calling app to format the data...the stored procedure needs to format the data.

Aug 25 2017

Understanding the recursive CTE termination check

In this sqlfiddle...

http://sqlfiddle.com/#!6/b6587/6

I am getting the following error....

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

I understand that there needs to be a "termination check" in the where clause of the second select of the CTE. Even if you uncomment the WHERE clause I get the same error.

I am just trying to understand 1) why it is needed at all...after all each order row has a relationship to each customer row and 2) since a "termination check" IS required what would that look like to get this example to work.

BTW, the output I would like to see for this query is as follows.

1,'George', 'Patton','',''
1,'','','<some date>', 'tank'
1,'','','<some date>', 'plane'
2,'Lewie', 'Puller','',''
2,'','','<some date>', 'Rifle'
2,'','','<some date>', 'Hand Grenade'

By the way, feel free to comment on other ways to get this kind of result (without using a recursive cte) but also without using a cursor or temp table. I would like to do this with a set-based operation.

But mostly I am just trying to understand recursive CTEs.

Nov 08 2016

Is there a way to aggregate a variable range of dates in SQL using a SET operation

I have a table like this one....

CREATE TABLE AbsentStudents
(
    Id int not null primary key identity(1,1),
    StudentId int not null,
    AbsentDate datetime not null
)

This is a very large table that has 1 row for each student for each day that they were absent.

I have been asked to write a stored procedure that gets student absences by date range. What makes this query tricky is that I have to filter/aggregate by "absence episodes". The number of days that constitutes an "absence episode" is a procedure parameter so it can vary.

So for example, I need to get a list of students who were absent between 1/1/2016 to 1/17/2016 but only if they were absent for more than @Days (2 or 3 or whatever the parameter dictates) days.

I think that alone I could figure out. However, within the date range a student can have more than one "absence episode". So a student might have been absent for 3 days at the beginning of the date range, 2 days in the middle of the date range, and 4 days at the end of the date range and each of those constitutes a different "absence episodes". Assuming that my @Days parameter is 2, that should return 3 rows for that student. And, each returned row should calculate how many days the student was absent for that "absence episode."

So I would like my procedure require 3 parameters (@StartDate datetime,@EndDate datetime, @Days int) and return something like this...

StudentId, InitialAbsentDate, ConsecutiveDaysMissed

And ideally it would do this using a SET operation and avoid cursors. (Although cursors are fine if that is the only option.)

UPDATE (by Shnugo)

A test scenario

DECLARE @AbsentStudents TABLE(
    Id int not null primary key identity(1,1),
    StudentId int not null,
    AbsentDate datetime not null
);
INSERT INTO @AbsentStudents VALUES
--student 1
 (1,{d'2016-10-01'}),(1,{d'2016-10-02'}),(1,{d'2016-10-03'}) --three days 
,(1,{d'2016-10-05'}) --one day
,(1,{d'2016-10-07'}),(1,{d'2016-10-08'}) --two days
--student 2
,(2,{d'2016-10-01'}),(2,{d'2016-10-02'}),(2,{d'2016-10-03'}),(2,{d'2016-10-04'}) --four days
,(2,{d'2016-10-08'}),(2,{d'2016-10-09'}),(2,{d'2016-10-10'}) --three days
,(2,{d'2016-10-12'}); --one day

DECLARE @startDate DATETIME={d'2016-10-01'};
DECLARE @endDate DATETIME={d'2016-10-31'};
DECLARE @Days INT = 3;
Nov 08 2016

Is there a way to aggregate a variable range of dates in SQL using a SET operation

I have a table like this one....

CREATE TABLE AbsentStudents
(
    Id int not null primary key identity(1,1),
    StudentId int not null,
    AbsentDate datetime not null
)

This is a very large table that has 1 row for each student for each day that they were absent.

I have been asked to write a stored procedure that gets student absences by date range. What makes this query tricky is that I have to filter/aggregate by "absence episodes". The number of days that constitutes an "absence episode" is a procedure parameter so it can vary.

So for example, I need to get a list of students who were absent between 1/1/2016 to 1/17/2016 but only if they were absent for more than @Days (2 or 3 or whatever the parameter dictates) days.

I think that alone I could figure out. However, within the date range a student can have more than one "absence episode". So a student might have been absent for 3 days at the beginning of the date range, 2 days in the middle of the date range, and 4 days at the end of the date range and each of those constitutes a different "absence episodes". Assuming that my @Days parameter is 2, that should return 3 rows for that student. And, each returned row should calculate how many days the student was absent for that "absence episode."

So I would like my procedure require 3 parameters (@StartDate datetime,@EndDate datetime, @Days int) and return something like this...

StudentId, InitialAbsentDate, ConsecutiveDaysMissed

And ideally it would do this using a SET operation and avoid cursors. (Although cursors are fine if that is the only option.)

UPDATE (by Shnugo)

A test scenario

DECLARE @AbsentStudents TABLE(
    Id int not null primary key identity(1,1),
    StudentId int not null,
    AbsentDate datetime not null
);
INSERT INTO @AbsentStudents VALUES
--student 1
 (1,{d'2016-10-01'}),(1,{d'2016-10-02'}),(1,{d'2016-10-03'}) --three days 
,(1,{d'2016-10-05'}) --one day
,(1,{d'2016-10-07'}),(1,{d'2016-10-08'}) --two days
--student 2
,(2,{d'2016-10-01'}),(2,{d'2016-10-02'}),(2,{d'2016-10-03'}),(2,{d'2016-10-04'}) --four days
,(2,{d'2016-10-08'}),(2,{d'2016-10-09'}),(2,{d'2016-10-10'}) --three days
,(2,{d'2016-10-12'}); --one day

DECLARE @startDate DATETIME={d'2016-10-01'};
DECLARE @endDate DATETIME={d'2016-10-31'};
DECLARE @Days INT = 3;
Nov 08 2016

Is there a way to aggregate a variable range of dates in SQL using a SET operation

I have a table like this one....

CREATE TABLE AbsentStudents
(
    Id int not null primary key identity(1,1),
    StudentId int not null,
    AbsentDate datetime not null
)

This is a very large table that has 1 row for each student for each day that they were absent.

I have been asked to write a stored procedure that gets student absences by date range. What makes this query tricky is that I have to filter/aggregate by "absence episodes". The number of days that constitutes an "absence episode" is a procedure parameter so it can vary.

So for example, I need to get a list of students who were absent between 1/1/2016 to 1/17/2016 but only if they were absent for more than @Days (2 or 3 or whatever the parameter dictates) days.

I think that alone I could figure out. However, within the date range a student can have more than one "absence episode". So a student might have been absent for 3 days at the beginning of the date range, 2 days in the middle of the date range, and 4 days at the end of the date range and each of those constitutes a different "absence episodes". Assuming that my @Days parameter is 2, that should return 3 rows for that student. And, each returned row should calculate how many days the student was absent for that "absence episode."

So I would like my procedure require 3 parameters (@StartDate datetime,@EndDate datetime, @Days int) and return something like this...

StudentId, InitialAbsentDate, ConsecutiveDaysMissed

And ideally it would do this using a SET operation and avoid cursors. (Although cursors are fine if that is the only option.)

UPDATE (by Shnugo)

A test scenario

DECLARE @AbsentStudents TABLE(
    Id int not null primary key identity(1,1),
    StudentId int not null,
    AbsentDate datetime not null
);
INSERT INTO @AbsentStudents VALUES
--student 1
 (1,{d'2016-10-01'}),(1,{d'2016-10-02'}),(1,{d'2016-10-03'}) --three days 
,(1,{d'2016-10-05'}) --one day
,(1,{d'2016-10-07'}),(1,{d'2016-10-08'}) --two days
--student 2
,(2,{d'2016-10-01'}),(2,{d'2016-10-02'}),(2,{d'2016-10-03'}),(2,{d'2016-10-04'}) --four days
,(2,{d'2016-10-08'}),(2,{d'2016-10-09'}),(2,{d'2016-10-10'}) --three days
,(2,{d'2016-10-12'}); --one day

DECLARE @startDate DATETIME={d'2016-10-01'};
DECLARE @endDate DATETIME={d'2016-10-31'};
DECLARE @Days INT = 3;
Oct 18 2016

Does SQL short-circuit the evaluation of an OR in the WHERE CLAUSE?

Looking at other questions here on SO and google seems to indicate MS Sql Server does not guarantee to short circuit the expressions in the WHERE clause.

Does that mean that a WHERE expression like this this one cannot be trusted to work...?

... WHERE (@include_voided = 1 OR mytable.void = 0) AND ... 

I frequently use this kind of expression in my stored procedures. Having researched short-circuiting for another reason I am now wondering if this should be replaced with ...

... WHERE mytable.void = case when @include_voided=1 then mytable.void else 0 END

...as all of the articles seem to indicate that CASE statements are the only ones guaranteed to short-circuit.

I am hoping that the first expression is fine just because it is more readable and easier to type.

Oct 18 2016

Does SQL short-circuit the evaluation of an OR in the WHERE CLAUSE? [duplicate]

This question already has an answer here:

Looking at other questions here on SO and google seems to indicate MS Sql Server does not guarantee to short circuit the expressions in the WHERE clause.

Does that mean that a WHERE expression like this this one cannot be trusted to work...?

... WHERE (@include_voided = 1 OR mytable.void = 0) AND ... 

I frequently use this kind of expression in my stored procedures. Having researched short-circuiting for another reason I am now wondering if this should be replaced with ...

... WHERE mytable.void = case when @include_voided=1 then mytable.void else 0 END

...as all of the articles seem to indicate that CASE statements are the only ones guaranteed to short-circuit.

I am hoping that the first expression is fine just because it is more readable and easier to type.

Jan 03 2013

Can SQL Server perform an update on rows with a set operation on the aggregate max or min value?

I am a fairly experienced SQL Server developer but this problem has me REALLY stumped. I have a FUNCTION. The function is referencing a table that is something like this...
PERFORMANCE_ID, JUDGE_ID, JUDGING_CRITERIA, SCORE  
--------------------------------------------------  
101, 1, 'JUMP_HEIGHT', 8   
101, 1, 'DEXTERITY', 7  
101, 1, 'SYNCHRONIZATION', 6  
101, 1, 'SPEED', 9  
101, 2, 'JUMP_HEIGHT', 6   
101, 2, 'DEXTERITY', 5  
101, 2, 'SYNCHRONIZATION', 8  
101, 2, 'SPEED', 9  
101, 3, 'JUMP_HEIGHT', 9   
101, 3, 'DEXTERITY', 6  
101, 3, 'SYNCHRONIZATION', 7  
101, 3, 'SPEED', 8  
101, 4, 'JUMP_HEIGHT', 7   
101, 4, 'DEXTERITY', 6  
101, 4, 'SYNCHRONIZATION', 5  
101, 4, 'SPEED', 8  
In this example there are 4 judges (with IDs 1, 2, 3, and 4) judging a performance (101) against 4 different criteria (JUMP_HEIGHT, DEXTERITY, SYNCHRONIZATION, SPEED).
(Please keep in mind that in my real data there are 10+ criteria and at least 6 judges.) I want to aggregate the results in a score BY JUDGING_CRITERIA and then aggregate those into a final score by summing...something like this...
SELECT SUM (Avgs) FROM
(SELECT AVG(SCORE) Avgs 
  FROM PERFORMANCE_SCORES
  WHERE PERFORMANCE_ID=101
  GROUP BY JUDGING_CRITERIA) result 
BUT... that is not quite what I want IN THAT I want to EXCLUDE from the AVG the highest and lowest values for each JUDGING_CRITERIA grouping. That is the part that I can't figure out. The AVG should be applied only to the MIDDLE values of the GROUPING FOR EACH JUDGING_CRITERIA. The HI value and the LO value for JUMP_HEIGHT should not be included in the average. The HI value and the LO value for DEXTERITY should not be included in the average. ETC. I know this could be accomplished with a cursor to set the hi and lo for each criteria to NULL. But this is a FUNCTION and should be extremely fast. I am wondering if there is a way to do this as a SET operation but still automatically exclude HI and LO from the aggregation? Thanks for your help. I have a feeling it can probably be done with some advanced SQL syntax but I don't know it. One last thing. This example is actually a simplification of the problem I am trying to solve. I have other constraints not mentioned here for the sake of simplicity. Seth
Jan 03 2013

Can SQL Server perform an update on rows with a set operation on the aggregate max or min value?

        <p>I am a fairly experienced SQL Server developer but this problem has me REALLY stumped.</p>

I have a FUNCTION. The function is referencing a table that is something like this...

PERFORMANCE_ID, JUDGE_ID, JUDGING_CRITERIA, SCORE  
--------------------------------------------------  
101, 1, 'JUMP_HEIGHT', 8   
101, 1, 'DEXTERITY', 7  
101, 1, 'SYNCHRONIZATION', 6  
101, 1, 'SPEED', 9  
101, 2, 'JUMP_HEIGHT', 6   
101, 2, 'DEXTERITY', 5  
101, 2, 'SYNCHRONIZATION', 8  
101, 2, 'SPEED', 9  
101, 3, 'JUMP_HEIGHT', 9   
101, 3, 'DEXTERITY', 6  
101, 3, 'SYNCHRONIZATION', 7  
101, 3, 'SPEED', 8  
101, 4, 'JUMP_HEIGHT', 7   
101, 4, 'DEXTERITY', 6  
101, 4, 'SYNCHRONIZATION', 5  
101, 4, 'SPEED', 8  

In this example there are 4 judges (with IDs 1, 2, 3, and 4) judging a performance (101) against 4 different criteria (JUMP_HEIGHT, DEXTERITY, SYNCHRONIZATION, SPEED).
(Please keep in mind that in my real data there are 10+ criteria and at least 6 judges.)

I want to aggregate the results in a score BY JUDGING_CRITERIA and then aggregate those into a final score by summing...something like this...

SELECT SUM (Avgs) FROM
(SELECT AVG(SCORE) Avgs 
  FROM PERFORMANCE_SCORES
  WHERE PERFORMANCE_ID=101
  GROUP BY JUDGING_CRITERIA) result 

BUT... that is not quite what I want IN THAT I want to EXCLUDE from the AVG the highest and lowest values for each JUDGING_CRITERIA grouping. That is the part that I can't figure out. The AVG should be applied only to the MIDDLE values of the GROUPING FOR EACH JUDGING_CRITERIA. The HI value and the LO value for JUMP_HEIGHT should not be included in the average. The HI value and the LO value for DEXTERITY should not be included in the average. ETC.

I know this could be accomplished with a cursor to set the hi and lo for each criteria to NULL. But this is a FUNCTION and should be extremely fast.

I am wondering if there is a way to do this as a SET operation but still automatically exclude HI and LO from the aggregation?

Thanks for your help. I have a feeling it can probably be done with some advanced SQL syntax but I don't know it.

One last thing. This example is actually a simplification of the problem I am trying to solve. I have other constraints not mentioned here for the sake of simplicity.

Seth

Oct 13 2012

How can I pass @VAR + magic numbers to a stored procedure parameter in a sql script?

Lets say I have a stored proc call MyStoredProc which recieved an int (@MyParam INT) datatype.

Let's say I have an int declared in a script like this...

DECLARE @MyVar INT ;
SET @MyVar = 101 ;

I just wrote a long sql script where I do a LOT of this...

EXEC MyStoredProc @MyVar + 1  ;

I am shocked that this is causing a syntax error warning.

I can do this...

EXEC MyStoredProc @MyVar ;

and I can do this...

EXEC MyStoredProc 101 ;

but I can't do this...

EXEC MyStoredProc @MyVar + 1  ;

This is going to make my script a LOT harder to write unless I am missing something. This is for SQL 2005.