Trying to add a running total to my query

Question

Post reply

Trying to add a running total to my query

NineIron

SSChampion

Points: 12529
More actions
January 7, 2019 at 11:06 am

#376723

I'm having trouble adding the running total to my query. See attached for data. Here's where I'm at now but, it isn't working. Department represents a physician, FSC is primary insurance. Units represents the MTD total for the CPT for that provider/FSC/location.
selectt.YearMonth,
t.Department,
t.CPT,
t.FSC,
t.Location,
t.Units,
YTDUnits
from #T t
inner join (selectDepartment,
CPT,
FSC,
Location,
sum(Units) over(partition by Department, CPT, FSC, Location order by CPT) as YTDUnits
from #T
) t1
on t.Department=t1.Department
and t.CPT=t1.CPT
andt.FSC=t1.FSC
andt.Location=t1.Location
order by YearMonth,
FSC,
Location

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Look at the windowing functions. I don't have anything readily available.

NineIron SSChampion Points: 12529 More actions · Answer 2

That's what I'm using in the sub query. Just noticed that I should be using the YTD stuff as the main query and left joining to the MTD stuff. Some months may not have a particular CPT code in the mix.

Mike01 SSChampion Points: 11393 More actions · Answer 3

select t.YearMonth, t.Department, t.CPT, t.FSC, t.Location, t.Units, SUM (Units) OVER (ORDER BY CPT, yearMonth, Department, FSC) AS RunningTotal from #T t

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

pietlinden SSC Guru Points: 63428 More actions · Answer 4

Here's an example I shamelessly copied out of Itzik Ben-Gan's T-SQL Querying book:
SELECT custID, orderID, orderDate, val, SUM(val) OVER (PARTITION BY custid, YEAR(orderDate) ORDER BY orderDate RANGE UNBOUNDED PRECEDING) AS YTD_val FROM dbo.OrderValues;
and here's my attempt at an answer...
SELECT Yr , YearMonth , Units , SUM(Units) OVER (PARTITION BY Yr ORDER BY FOM ROWS UNBOUNDED PRECEDING) AS YTD_Total , SUM(Units) OVER (PARTITION BY YearMonth ORDER BY FOM ROWS UNBOUNDED PRECEDING) AS MTD_Total FROM ( SELECT CAST(LEFT(YearMonth,4) AS INT) AS Yr , DATEFROMPARTS (CAST(LEFT(YearMonth,4) AS INTEGER), CAST(RIGHT(YearMonth,2) AS INTEGER), 1) AS FOM , YearMonth , Department , CPT , FSC , [Location] , Units , Charges FROM #T ) x;

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 5

For a running total - you need to set the row boundaries in the windowing function so that row only calculates for the data from the beginning to the current row.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

NineIron SSChampion Points: 12529 More actions · Answer 6

NineIron

SSChampion

Points: 12529

January 8, 2019 at 3:56 am

#2018265

Thanx for your help.