Gotcha with the LEAD analytical function and date datatype

The other day some code that used the LEAD analytical function stopped working suddenly. It started to throw an error.

Msg 206, Level 16, State 2, Line 1

Operand type clash: date is incompatible with int

The code wasn't touched in a long time... so what could be the issue. Let's reproduce it here

First create the following table

CREATE TABLE testRunningTotal(Id tinyint,SomeDate datetime, Charge decimal(20,2))

INSERT testRunningTotal
SELECT 1,'20120101',1000
UNION ALL
SELECT 1,'20120401',200
UNION ALL
SELECT 1,'20120501',300
UNION ALL
SELECT 1,'20120601',600
UNION ALL
SELECT 2,'20120101',100
UNION ALL
SELECT 2,'20130101',500
UNION ALL
SELECT 2,'20140101',-800
UNION ALL
SELECT 3,'20120101',100

Running this query to see what is in this table

SELECT * FROM testRunningTotal

Id SomeDate Charge

1 2012-01-01 00:00:00.000 1000.00

1 2012-04-01 00:00:00.000 200.00

1 2012-05-01 00:00:00.000 300.00

1 2012-06-01 00:00:00.000 600.00

2 2012-01-01 00:00:00.000 100.00

2 2013-01-01 00:00:00.000 500.00

2 2014-01-01 00:00:00.000 -800.00

3 2012-01-01 00:00:00.000 100.00

Now run our fancy query...

SELECT id, someDate as StartDate,
LEAD(SomeDate -1,1,'99991231') 
 OVER (PARTITION BY id ORDER BY SomeDate) as Enddate,
  Charge,
  SUM(Charge) OVER (PARTITION BY id ORDER BY SomeDate 
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS RunningTotal
  FROM testRunningTotal
  ORDER BY id, SomeDate

Here are our results

id StartDate Enddate Charge RunningTotal

1 2012-01-01 00:00:00.000 2012-03-31 00:00:00.000 1000.00 1000.00

1 2012-04-01 00:00:00.000 2012-04-30 00:00:00.000 200.00 1200.00

1 2012-05-01 00:00:00.000 2012-05-31 00:00:00.000 300.00 1500.00

1 2012-06-01 00:00:00.000 9999-12-31 00:00:00.000 600.00 2100.00

2 2012-01-01 00:00:00.000 2012-12-31 00:00:00.000 100.00 100.00

2 2013-01-01 00:00:00.000 2013-12-31 00:00:00.000 500.00 600.00

2 2014-01-01 00:00:00.000 9999-12-31 00:00:00.000 -800.00 -200.00

3 2012-01-01 00:00:00.000 9999-12-31 00:00:00.000 100.00 100.00

To see what the query actually does, visit this post Easy running totals with windowing functions

Let's take a closer look at the LEAD analytical function, this is what Books On Line has on its page

  LEAD ( scalar_expression [ ,offset ] , [ default ] ) 

    OVER ( [ partition_by_clause ] order_by_clause )

offset

The number of rows forward from the current row from which to obtain a value. If not specified, the default is 1. offset can be a column, subquery, or other expression that evaluates to a positive integer or can be implicitly converted to bigint. offset cannot be a negative value or an analytic function.

Looks all cool to me..nothing that stands out that could cause a problem...

What will happen if someone changes the column from a datetime to a date? Maybe they looked at the data and saw that all the values had only dates and no time components... why use 8 bytes of storage when you can use 3 instead?

To find out exactly what will happen, run the following DDL statement....

ALTER TABLE testRunningTotal ALTER COLUMN SomeDate date

Now go run your query again

SELECT id, someDate as StartDate,
LEAD(SomeDate -1,1,'99991231') 
 OVER (PARTITION BY id ORDER BY SomeDate) as Enddate,
  Charge,
  SUM(Charge) OVER (PARTITION BY id ORDER BY SomeDate 
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS RunningTotal
  FROM testRunningTotal
  ORDER BY id, SomeDate

Here is the error you get

  Msg 206, Level 16, State 2, Line 1

Operand type clash: date is incompatible with int

The error is the same as if you would run the following

SELECT CONVERT(DATE,GETDATE() )-1

Instead what you have to do is use the DATEADD function to do arithmetic with dates

SELECT DATEADD(dd,-1,CONVERT(DATE,GETDATE()))

So to fix our query from before, we want to change the -1 to be DATEADD(dd,-1 instead, here is what our query looks like

 SELECT id, someDate as StartDate,
LEAD(dateadd(dd,-1,SomeDate),1,'99991231') 
 OVER (PARTITION BY id ORDER BY SomeDate) as Enddate,
  Charge,
  SUM(Charge) OVER (PARTITION BY id ORDER BY SomeDate 
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS RunningTotal
  FROM testRunningTotal
  ORDER BY id, SomeDate

And here are our results, it's all good now

id StartDate Enddate Charge RunningTotal

1 2012-01-01 2012-03-31 1000.00 1000.00

1 2012-04-01 2012-04-30 200.00 1200.00

1 2012-05-01 2012-05-31 300.00 1500.00

1 2012-06-01 9999-12-31 600.00 2100.00

2 2012-01-01 2012-12-31 100.00 100.00

2 2013-01-01 2013-12-31 500.00 600.00

2 2014-01-01 9999-12-31 -800.00 -200.00

3 2012-01-01 9999-12-31 100.00 100.00

I still think that maybe the LEAD function should do this conversion internally since all we are doing is specifying the OFFSET...what is your opinion?

Easy running totals with windowing functions

Back in the pre SQL Server 2012 days, if you wanted to do a running count, you either had to run a subquery or you could use a variable. This was slow because for each row the query that did the sum would be executed. With the additions to the windowing functions in SQL Server 2012, this is now running much faster. 

Let's take a look, first create the following table

CREATE TABLE #test(Id tinyint,SomeDate date, Charge decimal(20,10))

insert #test
SELECT 1,'20120101',1000
UNION ALL
SELECT 1,'20120401',200
UNION ALL
SELECT 1,'20120501',300
UNION ALL
SELECT 1,'20120601',600
UNION ALL
SELECT 2,'20120101',100
UNION ALL
SELECT 2,'20130101',500
UNION ALL
SELECT 2,'20140101',-800
UNION ALL
SELECT 3,'20120101',100

let's check that data we just inserted into the temporary table

SELECT * FROM #test

The output looks like this

Id SomeDate Charge
1 2012-01-01 1000.0000000000
1 2012-04-01 200.0000000000
1 2012-05-01 300.0000000000
1 2012-06-01 600.0000000000
2 2012-01-01 100.0000000000
2 2013-01-01 500.0000000000
2 2014-01-01 -800.0000000000
3 2012-01-01 100.0000000000

What we want is the following

id StartDate Enddate         Charge         RunningTotal
1 2012-01-01 2012-03-31 1000.0000000000 1000.0000000000
1 2012-04-01 2012-04-30 200.0000000000 1200.0000000000
1 2012-05-01 2012-05-31 300.0000000000 1500.0000000000
1 2012-06-01 9999-12-31 600.0000000000 2100.0000000000
2 2012-01-01 2012-12-31 100.0000000000 100.0000000000
2 2013-01-01 2013-12-31 500.0000000000 600.0000000000
2 2014-01-01 9999-12-31 -800.0000000000 -200.0000000000
3 2012-01-01 9999-12-31 100.0000000000 100.0000000000

For each row, we want to have the date that the row starts on and also the date when it end, we also want a running total as well. If there is no row after the current row for that id, we want the end date to be 9999-12-31.

So we will use a couple of functions. The first one is LEAD, LEAD accesses data from a subsequent row in the same result set without the use of a self-join. So the LEAD part looks like this

LEAD(dateadd(dd,-1,SomeDate),1,'99991231')  OVER (PARTITION BY id ORDER BY SomeDate) as Enddate,

What we are doing is subtracting 1 from the date in the subsequent row (ateadd(dd,-1,SomeDate))
We are using 1 as the offset since we want to apply this to the next row. Finally if there is no subsequent row, we want to use the date 9999-12-31 instead of NULL

To do the running count, we will do the following

SUM(Charge) OVER (PARTITION BY id ORDER BY SomeDate
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
          AS RunningTotal

What this means in English is for each id ordered by date, sum up the charge values for the rows between the preceding rows and the current row. Here is what all that stuff means.

ROWS BETWEEN
Specifies the rows that make up the range to use as implied by

UNBOUNDED PRECEDING
Specifies that the window starts at the first row of the partition. UNBOUNDED PRECEDING can only be specified as window starting point.

CURRENT ROW
Specifies that the window starts or ends at the current row when used with ROWS or the current value when used with RANGE.
CURRENT ROW can be specified as both a starting and ending point.

And here is the query

SELECT id, someDate as StartDate,
LEAD(dateadd(dd,-1,SomeDate),1,'99991231') 
 OVER (PARTITION BY id ORDER BY SomeDate) as Enddate,
  Charge,
  SUM(Charge) OVER (PARTITION BY id ORDER BY SomeDate 
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS RunningTotal
  FROM #test
  ORDER BY id, SomeDate

And running that query, gives us the running count as well as the dates

id StartDate Enddate         Charge         RunningTotal
1 2012-01-01 2012-03-31 1000.0000000000 1000.0000000000
1 2012-04-01 2012-04-30 200.0000000000 1200.0000000000
1 2012-05-01 2012-05-31 300.0000000000 1500.0000000000
1 2012-06-01 9999-12-31 600.0000000000 2100.0000000000
2 2012-01-01 2011-12-31 100.0000000000 100.0000000000
2 2012-01-01 2011-13-31 500.0000000000 600.0000000000
2 2012-01-01 9999-12-31 -800.0000000000 -200.0000000000
3 2012-01-01 9999-12-31 100.0000000000 100.0000000000

That's all for this post...

Dropping all objects in a schema

I had the need to drop all objects that I created in a specific schema as part of a unit test. Then I decided to test my script on the Adventure Works 2014 sample database and I got a couple of errors. Some of these errors were that there were foreign key constraints from a different schema pointing to a table in this schema. I also got an error complaining about an XML SCHEMA collection living in my schema.

If you want to follow along by running the scripts, first download the Adventure Works 2014 sample database

After you are done downloading the database, create a folder named training on your C drive.
Open up SSMS, open a query window, run the following script

USE [master]

--DROP DATABASE AdventureWorks2014
GO

RESTORE DATABASE AdventureWorks2014
FROM disk= 'C:\Training\AdventureWorks2014.bak'
WITH MOVE 'AdventureWorks2014_data' TO 'C:\Training\AdventureWorks2014.mdf',
MOVE 'AdventureWorks2014_Log' TO 'C:\Training\AdventureWorks2014.ldf'
,REPLACE, stats =20

Now that the DB is available, we can look at what the script looks like. Connect to the database you just created and open up a new query window. Before running the script make sure that you are in text mode, just hit CTRL + T in your  SSMS query window

Here is what the script looks like

   SET NOCOUNT ON
   DECLARE  @SchemaName NVARCHAR(100) = 'HumanResources'


   SELECT 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(t.schema_id))
   +'.'+ QUOTENAME(OBJECT_NAME(s.parent_object_id))
   + ' DROP CONSTRAINT ' +  QUOTENAME(s.name)   
   FROM sys.foreign_keys s
   JOIN sys.tables t on s.parent_object_id = t.object_id
   JOIN sys.tables t2 on s.referenced_object_id = t2.object_id
    WHERE t2.schema_id = SCHEMA_ID(@SchemaName)

    SELECT 'DROP ' +
                    CASE WHEN type IN ('P','PC') THEN 'PROCEDURE'
                         WHEN type =  'U' THEN 'TABLE'
                         WHEN type IN ('IF','TF','FN') THEN 'FUNCTION'
                         WHEN type = 'V' THEN 'VIEW'
                     END +
                   ' ' +  QUOTENAME(SCHEMA_NAME(schema_id))+'.'+QUOTENAME(name)  
                   FROM sys.objects
             WHERE schema_id = SCHEMA_ID(@SchemaName)
    AND type IN('P','PC','U','IF','TF','FN','V')
    ORDER BY  CASE WHEN type IN ('P','PC') THEN 4
                         WHEN type =  'U' THEN 3
                         WHEN type IN ('IF','TF','FN') THEN 1
                         WHEN type = 'V' THEN 2
                       END


  SELECT 'DROP XML SCHEMA COLLECTION '
  + QUOTENAME(SCHEMA_NAME(schema_id))+'.'+QUOTENAME(name)
  FROM sys.xml_schema_collections
  WHERE schema_id = SCHEMA_ID(@SchemaName)

  SELECT 'DROP SCHEMA ' + QUOTENAME(@SchemaName)

As you can see, there are 4 things going on here.

1. Remove the constraints
2. Drop Views, Function, Stored Procedures and Tables, drop functions and views first, procs last
3. Drop XML Schema Collections
4. Drop the Schema

Of course if you have a view in  another database or schema referencing your objects inside this schema you will have problems...I will get to this later


Running that script will give you the following output

ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] 
DROP CONSTRAINT [FK_EmployeeDepartmentHistory_Shift_ShiftID]
ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] 
DROP CONSTRAINT [FK_EmployeeDepartmentHistory_Department_DepartmentID]
ALTER TABLE [Production].[Document] 
DROP CONSTRAINT [FK_Document_Employee_Owner]
ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] 
DROP CONSTRAINT [FK_EmployeeDepartmentHistory_Employee_BusinessEntityID]
ALTER TABLE [HumanResources].[EmployeePayHistory] 
DROP CONSTRAINT [FK_EmployeePayHistory_Employee_BusinessEntityID]
ALTER TABLE [HumanResources].[JobCandidate] 
DROP CONSTRAINT [FK_JobCandidate_Employee_BusinessEntityID]
ALTER TABLE [Purchasing].[PurchaseOrderHeader] 
DROP CONSTRAINT [FK_PurchaseOrderHeader_Employee_EmployeeID]
ALTER TABLE [Sales].[SalesPerson] 
DROP CONSTRAINT [FK_SalesPerson_Employee_BusinessEntityID]


DROP VIEW [HumanResources].[vEmployee]
DROP VIEW [HumanResources].[vEmployeeDepartment]
DROP VIEW [HumanResources].[vEmployeeDepartmentHistory]
DROP VIEW [HumanResources].[vJobCandidate]
DROP VIEW [HumanResources].[vJobCandidateEmployment]
DROP VIEW [HumanResources].[vJobCandidateEducation]
DROP TABLE [HumanResources].[Shift]
DROP TABLE [HumanResources].[Department]
DROP TABLE [HumanResources].[Employee]
DROP TABLE [HumanResources].[EmployeeDepartmentHistory]
DROP TABLE [HumanResources].[EmployeePayHistory]
DROP TABLE [HumanResources].[JobCandidate]
DROP PROCEDURE [HumanResources].[uspUpdateEmployeeHireInfo]
DROP PROCEDURE [HumanResources].[uspUpdateEmployeeLogin]
DROP PROCEDURE [HumanResources].[uspUpdateEmployeePersonalInfo]


DROP XML SCHEMA COLLECTION [HumanResources].[HRResumeSchemaCollection]


DROP SCHEMA [HumanResources]

Run that and it should all run fine, the schema should be gone

Now change @SchemaName NVARCHAR(100) = 'HumanResources' to @SchemaName NVARCHAR(100) = 'Sales'

Run the script again, run the output from the script

Finally change the variable to Person @SchemaName NVARCHAR(100) = 'Person'

Run the script, run the output.  All 3 schemas we specified should be gone now



Now restore the database again, This time run the code to remove the Sales Schema, Then run the code to remove the Person Schema.

Here is the error you will receive


Msg 6328, Level 16, State 1, Line 39
Specified collection 'AdditionalContactInfoSchemaCollection' cannot be dropped because it is used by object 'HumanResources.vEmployee'.

However, it is really used by the Person.Person table which we just dropped. In this case you will need to drop the view. If you have stuff like this in the database, you probably don't want to just start dropping objects in another schema that point to objects in your schema

I am not having these kind of issues so the code is good enough for me to wipe out objects that have been created as part of my unit testing