Question: How to set up a variable on the root directory for the url link in ColdFusion?

I set up the variable on the root directory for the url link, appweb\instance.cfm:

```
<CFSCRIPT>
 ...
 ...     
  Application.MainLink = "http://www.ucdavis.edu/index.html";

</CFSCRIPT>

```

I have a file under appweb/costsharing/footer.cfm to use the variable for url link, but it doesn't work. 

```
<br />
<br />
<div align="center">
<b>Need Help?  Visit the <a href="#Application.MainLink#" target=_blank>Cost    Sharing Help</a></b>
</div>
<br />
<br />
<CFInclude Template="../OnRequestEnd.cfm">

</body>
</html>

```

Any suggestion?

Answer: 
This is it, the query logic is as simple as it gets, the performance better than inner joins:

```
select date, sum(sales) - coalesce(lag(sum(sales), 1) over (order by date), 0)
from my_sales
group by date
order by date

```

Use window function `lag`. Play with it: <http://sqlfiddle.com/#!6/bebab/8> and read about it: <https://msdn.microsoft.com/en-us/library/hh231256.aspx>

briefly, `lag(sum(sales), 1) over (order by date)` means "get sum(sales) column of previous record of this query, ordered by date", `coalesce(XXX, 0)` means "when XXX is null, let's pretend it was a zero"
use a self join

```
declare @t table (item varchar(10), [date] date,country  char(2),  sales int)
insert into @t (item, [date],country,  sales) values
('motorola','2015-01-01','US',10),
('motorola','2015-01-01','UK',20),
('motorola','2015-01-02','US',40),
('motorola','2015-01-02','UK',80),
('motorola','2015-01-03','US',120),
('motorola','2015-01-03','UK',150),
('motorola','2015-01-04','US',170),
('motorola','2015-01-04','US',180)
;with a as (select row_number() over (order by [date]) r,[date],sum(sales) n from @t group by [date]) 

select a.[date],a.n-isnull(a1.n,0) dailyDelta from a  join a a1 on a.r =a1.r+1

```