Filtering a Result Set based on changed columns SQL

user_5513917 0
Viewed 127

Using the following sample code I'm trying to remove rows that have not had columns changed based on the scalar @CompareFields

create table #ResultSet_fields(
 claimId int,
 adjustmentVersion int,
 ServiceDateFrom date,
 ServiceDateTo date,
 ProcedureCode varchar(10),
 PlaceOfService varchar(3)
 )

declare @CompareFields varchar(max)

select @CompareFields = 'ProcedureCode,PlaceOfService'

insert into #ResultSet_fields (ClaimId,adjustmentVersion,ServiceDateFrom,ServiceDateTo,ProcedureCode,PlaceOfService) select 1010,1,'5/5/2015','5/5/2015',92213,11

insert into #ResultSet_fields (ClaimId,adjustmentVersion,ServiceDateFrom,ServiceDateTo,ProcedureCode,PlaceOfService) select 1010,2,'5/5/2015','5/5/2015',92213,11

insert into #ResultSet_fields (ClaimId,adjustmentVersion,ServiceDateFrom,ServiceDateTo,ProcedureCode,PlaceOfService) select 1010,3,'5/5/2015','5/5/2015',92214,11

insert into #ResultSet_fields (ClaimId,adjustmentVersion,ServiceDateFrom,ServiceDateTo,ProcedureCode,PlaceOfService) select 1011,1,'5/5/2015','5/5/2015',5555,11

insert into #ResultSet_fields (ClaimId,adjustmentVersion,ServiceDateFrom,ServiceDateTo,ProcedureCode,PlaceOfService) select 1012,1,'5/7/2015','5/7/2015',66666,11

insert into #ResultSet_fields (ClaimId,adjustmentVersion,ServiceDateFrom,ServiceDateTo,ProcedureCode,PlaceOfService) select 1012,2,'5/7/2015','5/7/2015',66666,13

insert into #ResultSet_fields (ClaimId,adjustmentVersion,ServiceDateFrom,ServiceDateTo,ProcedureCode,PlaceOfService) select 1013,1,'5/7/2015','5/7/2015',99999,11

insert into #ResultSet_fields (ClaimId,adjustmentVersion,ServiceDateFrom,ServiceDateTo,ProcedureCode,PlaceOfService) select 1014,1,'5/9/2015','5/9/2015',99999,11

I only want rows that have had a column change of 'ProcedureCode' and 'PlaceOfService' between adjustment versions.

ex. since the row with ClaimId '1010' had a 'ProcedureCode' change and row with claimId '1012' had a 'PlaceOfService' change I would only like those rows to remain in the Result Set.

I have thought about using the EXCEPT clause or UPDATE() trigger but I'm having trouble forming the syntax.

Can someone point me to the right direction on how to accomplish this?

tsql sql-server sql
1 Answers

You can use LAG to get the previous row's value to compare to.

SELECT
  ClaimId,
  adjustmentVersion,
  ServiceDateFrom,
  ServiceDateTo,
  ProcedureCode,
  PlaceOfService
FROM (
    SELECT *,
      ServiceDateFrom_prev = LAG(rs.ServiceDateFrom) OVER (PARTITION BY rs.ClaimId ORDER BY rs.adjustmentVersion),
      ServiceDateTo_prev   = LAG(rs.ServiceDateTo  ) OVER (PARTITION BY rs.ClaimId ORDER BY rs.adjustmentVersion),
      ProcedureCode_prev   = LAG(rs.ProcedureCode  ) OVER (PARTITION BY rs.ClaimId ORDER BY rs.adjustmentVersion),
      PlaceOfService_prev  = LAG(rs.PlaceOfService ) OVER (PARTITION BY rs.ClaimId ORDER BY rs.adjustmentVersion)
    FROM #ResultSet_fields rs
) rs
WHERE (
    @CompareFields LIKE '%ServiceDateFrom%' AND ServiceDateFrom <> ServiceDateFrom_prev
 OR @CompareFields LIKE '%ServiceDateTo%'   AND ServiceDateTo   <> ServiceDateTo_prev
 OR @CompareFields LIKE '%ProcedureCode%'   AND ProcedureCode   <> ProcedureCode_prev
 OR @CompareFields LIKE '%PlaceOfService%'  AND ProcedureCode   <> PlaceOfService_prev
);

If you want to use indexes, or you have a lot of columns ot compare, you can use dynamic SQL

DECLARE @lagCols nvarchar(max), @whereFilters nvarchar(max);

SELECT
  @lagCols = STRING_AGG(CAST(
'    ' + QUOTENAME(c.name + '_chg') + ' = LAG(rs.' + QUOTENAME(c.name) + ') OVER (PARTITION BY rs.ClaimId ORDER BY rs.adjustmentVersion)
    '      AS nvarchar(max)), ',')

 ,@whereFilters = STRING_AGG(CAST(
    QUOTENAME(c.name + '_chg') + ' <> ' + QUOTENAME(c.name)
      AS nvarchar(max)), ' OR
    ')

  FROM STRING_SPLIT(@CompareFields, ',') s
  JOIN tempdb.sys.columns c ON c.name = TRIM(s.value)  -- make sure to get the right database
  WHERE c.object_id = OBJECT_ID('tempdb..#ResultSet_fields');

DECLARE @sql nvarchar(max) = '
SELECT
  ClaimId,
  adjustmentVersion,
  ServiceDateFrom,
  ServiceDateTo,
  ProcedureCode,
  PlaceOfService
FROM (
    SELECT *,
' + @lagCols + '
    FROM #ResultSet_fields rs
) rs
WHERE (
    ' + @whereFilters + '
);
';

PRINT @sql; -- for testing
EXEC sp_executesql @sql;
user_148689970
Related