How to detect changed rows and Bound Changes Between Row Values in Table?

Input Data

emp_id	WorkingDay	first_name	Department	UpdateTimeStamp
1	3/03/2019	EMP1	D1	3/04/2019 12:01:31
1	3/03/2019	EMP1	D1	3/04/2019 12:11:31
1	3/03/2019	EMP2	D2	3/04/2019 12:21:31
1	3/03/2019	EMP1	D2	3/04/2019 12:26:31
1	3/03/2019	EMP1	D3	3/04/2019 12:29:31
1	3/03/2019	EMP1	D3	3/04/2019 12:31:31
2	3/03/2019	EMP2	D1	3/04/2019 12:01:31
2	3/03/2019	EMP2	D1	3/04/2019 12:11:31
2	3/03/2019	EMP2	D1	3/04/2019 12:21:31
2	3/03/2019	EMP2	D1	3/04/2019 12:31:31
1	2/03/2019	EMP1	D1	2/04/2019 12:01:31
1	2/03/2019	EMP1	D1	2/04/2019 12:11:31
1	2/03/2019	EMP1	D2	2/04/2019 12:21:31
1	2/03/2019	EMP1	D2	2/04/2019 12:26:31
1	2/03/2019	EMP1	D3	2/04/2019 12:31:31

DROP TABLE employee;


CREATE TABLE employee (emp_id INT , WorkingDay date NOT NULL,
                                                    first_name VARCHAR(30) NOT NULL,
                                                                           Department varchar(10) NOT NULL,
                                                                                                  UpdateTimeStamp datetime NOT NULL);


INSERT INTO employee
VALUES (1,'2019-03-03','EMP1','D1',DATEADD(MINUTE,-30, GETDATE())) ,
       (1,'2019-03-03','EMP1','D1',DATEADD(MINUTE,-20, GETDATE())) ,
       (1,'2019-03-03','EMP2','D2',DATEADD(MINUTE,-10, GETDATE())) ,
       (1,'2019-03-03','EMP1','D2',DATEADD(MINUTE,-5, GETDATE())) ,
       (1,'2019-03-03','EMP1','D3',DATEADD(MINUTE,-2, GETDATE())) ,
       (1,'2019-03-03','EMP1','D3',DATEADD(MINUTE,-0, GETDATE())) ,
       (2,'2019-03-03','EMP2','D1',DATEADD(MINUTE,-30, GETDATE())) ,
       (2,'2019-03-03','EMP2','D1',DATEADD(MINUTE,-20, GETDATE())) ,
       (2,'2019-03-03','EMP2','D1',DATEADD(MINUTE,-10, GETDATE())) ,
       (2,'2019-03-03','EMP2','D1',DATEADD(MINUTE,-0, GETDATE())) ,
       (1,'2019-03-02','EMP1','D1',dateAdd(DAY,-1,(DATEADD(MINUTE,-30, GETDATE())))) ,
       (1,'2019-03-02','EMP1','D1',dateAdd(DAY,-1,(DATEADD(MINUTE,-20, GETDATE())))) ,
       (1,'2019-03-02','EMP1','D2',dateAdd(DAY,-1,(DATEADD(MINUTE,-10, GETDATE())))) ,
       (1,'2019-03-02','EMP1','D2',dateAdd(DAY,-1,(DATEADD(MINUTE,-5, GETDATE())))) ,
       (1,'2019-03-02','EMP1','D3',dateAdd(DAY,-1,(DATEADD(MINUTE,-0, GETDATE()))));

WITH GroupedData AS
  (SELECT emp_id,
          WorkingDay,
          first_name,
          Department,
          UpdateTimeStamp,
          dense_rank() OVER (
                             ORDER BY emp_id,
                                      WorkingDay) Group1 --making a group to identify which record to compare with in a Group
,
                            ROW_NUMBER() OVER (
                                               ORDER BY emp_id,
                                                        WorkingDay,
                                                        first_name,
                                                        Department,
                                                        UpdateTimeStamp) rownum
   FROM employee --order by Group1
),
     FirstRowOfChangeData AS
  (SELECT g1.*,
          ROW_NUMBER() OVER (
                             ORDER BY g1.emp_id,
                                      g1.WorkingDay,
                                      g1.first_name,
                                      g1.Department,
                                      g1.UpdateTimeStamp) rownum1 -- another rownum over full dataset to compare 1 row with next one when doing self join

   FROM GroupedData g1
   LEFT OUTER JOIN GroupedData g2 ON g1.Group1=g2.Group1
   AND g1.emp_id=g2.emp_id
   AND g1.WorkingDay=g2.WorkingDay
   AND g1.emp_id=g2.emp_id
   AND g1.Department=g2.Department
   AND g2.rownum=g1.rownum-1 --Join rownumber with 1 less rownumber to detect the row where data is changed.

   WHERE g2.emp_id IS NULL -- pick the 1st row where data was changed.
 )
SELECT f1.*,
       f2.UpdateTimeStamp
FROM FirstRowOfChangeData f1
LEFT OUTER JOIN FirstRowOfChangeData f2 ON f2.rownum1=f1.rownum1+1
AND f2.Group1=f1.Group1 --now lets calculate how much time each employe Spent in a department each day.

Output Format

emp_id	WorkingDay	first_name	Department	StartTime	EndTime
1	2/03/2019	EMP1	D1	2/04/2019 12:01:31	2/04/2019 12:21:31
1	2/03/2019	EMP1	D2	2/04/2019 12:21:31	2/04/2019 12:31:31
1	2/03/2019	EMP1	D3	2/04/2019 12:31:31	NULL
1	3/03/2019	EMP1	D1	3/04/2019 12:01:31	3/04/2019 12:26:31
1	3/03/2019	EMP1	D2	3/04/2019 12:26:31	3/04/2019 12:29:31
1	3/03/2019	EMP1	D3	3/04/2019 12:29:31	3/04/2019 12:21:31
1	3/03/2019	EMP2	D2	3/04/2019 12:21:31	NULL
2	3/03/2019	EMP2	D1	3/04/2019 12:01:31	NULL

You could use the above approach close endTime where it's NULL when the day ends.

IsEmpty or IsBlank function like ISNULL in SQL Server?

Try below in select clause.

select ISNULL( nullif(EmptyOrNullOrBlankColumn,'' ),NULL)

Below in Where Clause.

where ISNULL( nullif(EmptyOrNullOrBlankColumn,'' ),NULL) is not null

How to calculate difference in hours (decimal) between two dates in SQL Server?

DATEDIFF(hour, start_date, end_date) will give you the number of hour boundaries crossed between start_date and end_date

If you need the number of fractional hours, you can use DATEDIFF at a higher resolution(like seconds)  and divide the result:

DATEDIFF(second, start_date, end_date) / 3600.0

The documentation for DATEDIFF is available on MSDN:

http://msdn.microsoft.com/en-us/library/ms189794%28SQL.105%29.aspx

{oj} - what does this mean? -Understanding the use of curly braces and "OJ"

ODBC supports the SQL-92 left, right, and full outer join syntax. The escape sequence for outer joins is

{oj outer-join}

where outer-join is

table-reference {LEFT | RIGHT | FULL} OUTER JOIN {table-reference | outer-join} ON search-condition

table-reference specifies a table name, and search-condition specifies the join condition between the table-references.

An outer join request must appear after the FROM keyword and before the WHERE clause (if one exists). For complete syntax 

https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/outer-joins?view=sql-server-2017

Arithmetic overflow error converting expression to data type int.

Try commenting out various aggregations to identify the problem with

SUM(someIntegerMeasure)

The sum is exceeding the maximum int. Try replacing it with

SUM(CAST(someIntegerMeasure AS BIGINT))

Open Query Vs Linked Server - Run SQL on Remote Server (Remotely or Locally)

By default, when you run a distributed query using a linked server, the query is processed locally.

This may or may not be efficient, depending on how much data must be sent from the remote server to the local server for processing. Sometimes it is more efficient to pass through the query so that it is run on the remote server. This way, if the query must process many rows, it can process them on the remote server, and only return to the local server the results of the query.


The OPENQUERY function is used to specify that a distributed query be processed on the remote server instead of the local server.

How to List/Find All Columns with Table Names - find sql table name with a particular column

SELECT c.name AS ColName,
       t.name AS TableName
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%ColYouWantTofind%';

SQL Server - List All Tables - How to Show All Tables - Table List

You could Use any of the following SQL's


To return all tables and views in one query, execute the following TSQL statement:

SELECT *
FROM INFORMATION_SCHEMA.TABLES;

If you only wish to retrieve actual tables and filter out views from the results, add a WHERE TABLE_TYPE = 'BASE TABLE' clause:

SELECT *
FROM databaseName.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';

-------------------------------------Or---------------------------------------------------

The tableSYSOBJECTS houses a couple dozen columns of data since it must hold information about virtually everything added to the server over time. o find a list of user-created tables (thus ignoring system tables), we’ll need to find results where the xtype column (which specifies the object type for that row) is equal to the value U, which stands for user table.

SELECT *
FROM SYSOBJECTS
WHERE xtype = 'U';

Below is a list of other object types you can search for as well:

• AF: Aggregate function (CLR)
• C: CHECK constraint
• D: Default or DEFAULT constraint
• F: FOREIGN KEY constraint
• L: Log
• FN: Scalar function
• FS: Assembly (CLR) scalar-function
• FT: Assembly (CLR) table-valued function
• IF: In-lined table-function
• IT: Internal table
• P: Stored procedure
• PC: Assembly (CLR) stored-procedure
• PK: PRIMARY KEY constraint (type is K)
• RF: Replication filter stored procedure
• S: System table
• SN: Synonym
• SQ: Service queue
• TA: Assembly (CLR) DML trigger
• TF: Table function
• TR: SQL DML Trigger
• TT: Table type
• U: User table
• UQ: UNIQUE constraint (type is K)
• V: View
• X: Extended stored procedure

------------------------------------Or----------------------------------------------------

EXEC sp_msforeachtable 'print ''?'''