Category: sql-server

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.

Oct 29 2013

Great article on SQL Command utility.

I thought I knew about the Sql Server sqlcmd utility ... until I read this article... https://www.simple-talk.com/sql/sql-tools/sql-server-sqlcmd-basics/ One of the coolest things I learned is that DOS has an "if exist" command. Seth
Jun 17 2013

How do I update an XML column in sql server by checking for the value of two nodes including one which needs to do a contains (like) comparison

I have an xml column called OrderXML in an Orders table...
there is an XML XPath like this in the table...

/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail

There InternalOrderDetails contains many InternalOrderDetail nodes like this...

<InternalOrderDetails>
  <InternalOrderDetail>
    <Item_Number>FBL11REFBK</Item_Number>
    <CountOfNumber>10</CountOfNumber>
    <PriceLevel>FREE</PriceLevel>
  </InternalOrderDetail>
  <InternalOrderDetail>
    <Item_Number>FCL13COTRGUID</Item_Number>
    <CountOfNumber>2</CountOfNumber>
    <PriceLevel>NONFREE</PriceLevel>
  </InternalOrderDetail>
</InternalOrderDetails>

My end goal is to modify the XML in the OrderXML column IF the Item_Number of the node contains COTRGUID (like '%COTRGUID') AND the PriceLevel=NONFREE. If that condition is met I want to change the PriceLevel column to equal FREE.

I am having trouble with both creating the xpath expression that finds the correct nodes (using OrderXML.value or OrderXML.exist functions) and updating the XML using the OrderXML.modify function).

I have tried the following for the where clause:

WHERE OrderXML.value('(/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail/Item_Number/node())[1]','nvarchar(64)') like '%13COTRGUID'

That does work, but it seems to me that I need to ALSO include my second condition (PriceLevel=NONFREE) in the same where clause and I cannot figure out how to do it. Perhaps I can put in an AND for the second condition like this...

AND OrderXML.value('(/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail/PriceLevel/node())[1]','nvarchar(64)') = 'NONFREE'

but I am afraid it will end up operating like an OR since it is an XML query.

Once I get the WHERE clause right I will update the column using a SET like this:

UPDATE Orders SET orderXml.modify('replace value of (/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail/PriceLevel[1]/text())[1] with "NONFREE"')

However, I ran this statement on some test data and none of the XML columns where updated (even though it said zz rows effected).

I have been at this for several hours to no avail. Help is appreciated. Thanks.

Jun 17 2013

How do I update an XML column in sql server by checking for the value of two nodes including one which needs to do a contains (like) comparison

I have an xml column called OrderXML in an Orders table...
there is an XML XPath like this in the table...

/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail

There InternalOrderDetails contains many InternalOrderDetail nodes like this...

<InternalOrderDetails>
  <InternalOrderDetail>
    <Item_Number>FBL11REFBK</Item_Number>
    <CountOfNumber>10</CountOfNumber>
    <PriceLevel>FREE</PriceLevel>
  </InternalOrderDetail>
  <InternalOrderDetail>
    <Item_Number>FCL13COTRGUID</Item_Number>
    <CountOfNumber>2</CountOfNumber>
    <PriceLevel>NONFREE</PriceLevel>
  </InternalOrderDetail>
</InternalOrderDetails>

My end goal is to modify the XML in the OrderXML column IF the Item_Number of the node contains COTRGUID (like '%COTRGUID') AND the PriceLevel=NONFREE. If that condition is met I want to change the PriceLevel column to equal FREE.

I am having trouble with both creating the xpath expression that finds the correct nodes (using OrderXML.value or OrderXML.exist functions) and updating the XML using the OrderXML.modify function).

I have tried the following for the where clause:

WHERE OrderXML.value('(/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail/Item_Number/node())[1]','nvarchar(64)') like '%13COTRGUID'

That does work, but it seems to me that I need to ALSO include my second condition (PriceLevel=NONFREE) in the same where clause and I cannot figure out how to do it. Perhaps I can put in an AND for the second condition like this...

AND OrderXML.value('(/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail/PriceLevel/node())[1]','nvarchar(64)') = 'NONFREE'

but I am afraid it will end up operating like an OR since it is an XML query.

Once I get the WHERE clause right I will update the column using a SET like this:

UPDATE Orders SET orderXml.modify('replace value of (/Order/InternalInformation/InternalOrderBreakout/InternalOrderHeader/InternalOrderDetails/InternalOrderDetail/PriceLevel[1]/text())[1] with "NONFREE"')

However, I ran this statement on some test data and none of the XML columns where updated (even though it said zz rows effected).

I have been at this for several hours to no avail. Help is appreciated. Thanks.

Jun 07 2013

AWESOME sql script for monitor the progress of a db restore

SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time], CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min], CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min], CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours], CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2, CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END) FROM sys.dm_exec_sql_text(sql_handle))) FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE') Found at stackoverflow here...
http://stackoverflow.com/questions/152447/is-there-a-sql-script-that-i-can-use-to-determine-the-progress-of-a-sql-server-b
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