asked on

TSQL - Rollup data set

Per my clients request, they need to roll-up to one single line where the CPTCode and Revenue Code are the same. For the Date of Service column, it needs to be the earliest (MIN) date and for the Units and Fee a subtotal of the CPTCode Revenue Code combination.

My current Result Set:

My current Code:

SET NOCOUNT ON

DECLARE @PatientVisitId INT;
SET @PatientVisitId = ( SELECT PatientVisitId	 FROM PatientVisit	 WHERE TicketNumber = '1337186' );

CREATE TABLE #RollUp
 ( 
	  DateOfServiceFrom DATETIME
	, CPTCode           VARCHAR(10)
	, Fee					  MONEY	
	, Units             FLOAT
	, RevenueCode       VARCHAR(10)
	, ListOrder         INT
	, RN                TINYINT
 );

INSERT INTO #RollUp
 ( 
     DateOfServiceFrom
	, CPTCode
	, Fee
	, Units
	, RevenueCode
	, ListOrder
	, RN
 )

SELECT 
	  pvp.DateOfServiceFrom
	, pvp.CPTCode
	, pvp.Fee
	, pvp.Units
	, pvp.RevenueCode
	, pvp.ListOrder
	, ROW_NUMBER() OVER(PARTITION BY pv.PatientVisitId
											 , pvp.ProceduresId 
											 , pvp.RevenueCode ORDER BY pvp.ProceduresId) AS RN
FROM 
	  PatientVisitProcs pvp
	  JOIN PatientVisit pv ON pvp.PatientVisitId = pv.PatientVisitId

WHERE 
	 pv.PatientVisitId = @PatientVisitId

ORDER BY 
	  pvp.ListOrder;

SELECT 
	 * 
FROM #RollUp

DROP TABLE #RollUp


/*************************************************

Expected Results

10/03/2016   99213   172.00   2   0521     
10/03/2016   20610   220.00   2   0521
10/03/2016   73565   165.00   1   NULL
10/03/2016   73560   46.00    1   0320
10/03/2016   J1885   85.00    1   0636
10/28/2016   J1885   85.00    1   0636

**************************************************/

Open in new window

Pawan Kumar

Could you please post few rows from PatientVisit table and its schema?

Pawan Kumar

Try..

, SUM(Fee) OVER (PARTITION BY pvp.CPTCode) FeesSum
, COUNT(*) OVER (PARTITION BY pvp.CPTCode) Counts

--

SET NOCOUNT ON

DECLARE @PatientVisitId INT;
SET @PatientVisitId = ( SELECT PatientVisitId	 FROM PatientVisit	 WHERE TicketNumber = '1337186' );

CREATE TABLE #RollUp
 ( 
	  DateOfServiceFrom DATETIME
	, CPTCode           VARCHAR(10)
	, Fee					  MONEY	
	, Units             FLOAT
	, RevenueCode       VARCHAR(10)
	, ListOrder         INT
	, RN                TINYINT
 );

INSERT INTO #RollUp
 ( 
     DateOfServiceFrom
	, CPTCode
	, Fee
	, Units
	, RevenueCode
	, ListOrder
	, RN
 )

SELECT 
	  pvp.DateOfServiceFrom
	, pvp.CPTCode
	, pvp.Fee
	, pvp.Units
	, pvp.RevenueCode
	, pvp.ListOrder
	, SUM(Fee) OVER (PARTITION BY pvp.CPTCode) FeesSum
	, COUNT(*) OVER (PARTITION BY pvp.CPTCode) Counts
	, ROW_NUMBER() OVER(PARTITION BY pv.PatientVisitId
											 , pvp.ProceduresId 
											 , pvp.RevenueCode ORDER BY pvp.ProceduresId) AS RN
FROM 
	  PatientVisitProcs pvp
	  JOIN PatientVisit pv ON pvp.PatientVisitId = pv.PatientVisitId

WHERE 
	 pv.PatientVisitId = @PatientVisitId

ORDER BY 
	  pvp.ListOrder;

SELECT 
	 * 
FROM #RollUp

DROP TABLE #RollUp


--

Open in new window

Pawan Kumar

try..

--

SELECT 
	 DISTINCT 
	 CAST(pvp.DateOfServiceFrom AS DATE) DateOfServiceFrom
	, pvp.CPTCode		
	, SUM(Fee) OVER (PARTITION BY pvp.CPTCode,CAST(pvp.DateOfServiceFrom AS DATE) ) FeesSum
	, COUNT(*) OVER (PARTITION BY pvp.CPTCode,CAST(pvp.DateOfServiceFrom AS DATE) ) Counts	
	, pvp.RevenueCode
FROM 
	  PatientVisitProcs pvp
	  JOIN PatientVisit pv ON pvp.PatientVisitId = pv.PatientVisitId

WHERE 
	 pv.PatientVisitId = @PatientVisitId

ORDER BY 
	  pvp.ListOrder;

--

Open in new window

Jeff S

ASKER

This gets me pretty close .... but not 100% there just yet.

If the Revenue Code equals '0636' I do not want it to roll them up or aggregate the fee and units. I need those left alone and treated alone.

SET NOCOUNT ON

DECLARE @PatientVisitId INT;
SET @PatientVisitId = ( SELECT PatientVisitId	 FROM PatientVisit	 WHERE TicketNumber = '1337186' );

CREATE TABLE #RollUp
 ( 
	  DateOfServiceFrom DATETIME
	, CPTCode           VARCHAR(10)
	, Fee					  MONEY	
	, Units             FLOAT
	, RevenueCode       VARCHAR(10)
	, ListOrder         INT
	, RN                TINYINT
 );

INSERT INTO #RollUp
 ( 
     DateOfServiceFrom
	, CPTCode
	, Fee
	, Units
	, RevenueCode
	, ListOrder
	, RN
 )

SELECT 
	  pvp.DateOfServiceFrom
	, pvp.CPTCode
	, SUM(Fee) OVER (PARTITION BY pvp.CPTCode) FeesSum
	, COUNT(*) OVER (PARTITION BY pvp.CPTCode) Counts
	, pvp.RevenueCode
	, pvp.ListOrder
	, ROW_NUMBER() OVER(PARTITION BY pv.PatientVisitId
											 , pvp.ProceduresId 
											 , pvp.RevenueCode ORDER BY pvp.ProceduresId) AS RN
FROM 
	  PatientVisitProcs pvp
	  JOIN PatientVisit pv ON pvp.PatientVisitId = pv.PatientVisitId

WHERE 
	 pv.PatientVisitId = @PatientVisitId


ORDER BY 
	  pvp.ListOrder;

DELETE FROM #RollUp WHERE 
RN <> 1 AND RevenueCode <> '0636'

SELECT 
	 * 
FROM #RollUp

DROP TABLE #RollUp

Open in new window

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeff S

ASKER

Awesome, THANKS!

Pawan Kumar

Welcome Jeff !!