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"
I don't really see much of a way around a self join. Here is how it would work:

```
select a.date
  , a.item
  , sum(a.sales) - sum(b.sales) as DailyDelta
from table a
  join table b on a.product = b.product
    and b.date = dateadd(day, -1, a.date)
group by a.date
  , a.item

```

Not great performance wise but it will get the job done.