Running totals through SQL Expression.

[SusanStoHelit]

Running totals through SQL.

I have a pharmacy claims database. I have a CRW 10 report using OLE DB to access a SQL server.

My report identifies members who have hit a total drug spend limit and pulls their entire claims history.

The report works great. However, now the report requestor would like me to add the date that the member hit the limit. The report is grouped by member name and drug name, not date, so any running total would be non-sequential date-wise.

I'd like to do a SQL running total formula to identify the date when the member hits the limit. This is what I have so far:

SELECT a1.ClaimID, a1.DateFill, (a1.COPAY+a1.AMOUNTDUE) as Total_Cost, SUM((a2.COPAY+a2.AMOUNTDUE)) as Running_Total 
FROM TableName a1, TableName a2 
WHERE Total_Cost <= Running_Total 
or (Total_Cost  = Running_Total and a1.ClaimID=a1.ClaimID) 
GROUP BY a1.DateFill, a1.ClaimID, Total_Cost 
ORDER BY a1.DateFill, Total_Cost DESC, a1.ClaimID DESC

But I keep getting "Invalid column name" errors.

Any ideas?

 

[lbass]

You won't be able to do this in a SQL expression. You could insert a subreport in the member group header (and linked on that field) and then in the subreport add the date field and the cost field, sort ascending on date, and then use a formula like this in the detail section:

whileprintingrecords;
numbervar sumcost := sumcost + {table.amtdue} + {table.copay};
numbervar limit;
numbervar cnt;
datevar datex;

if sumcost > 6000 then //replace 6000 with the limit
(
limit := sumcost;
cnt := cnt + 1
);
if cnt = 1 then
datex := {table.date};

In the subreport footer, use a formula like this:

whileprintingrecords;
datevar datex;

Suppress all sections within the subreport but the subreport footer.

-LB