SQL Rob

Displaying a running total in SQL Server is very straightforward, thanks to window functions and the OVER clause.
We’ll set up an example with some records for a payment amount over several days.

drop table if exists #Test;

create table #Test (
PaymentDate date not null primary key,
Amount int not null
);

insert into #Test(PaymentDate, Amount) 
values ('2018-05-29', 25), ('2018-05-30',10), ('2018-05-31',20), 
	('2018-06-01',30), ('2018-06-02',40), ('2018-06-03', 15);

select * from #Test;

Adding a running total is just a matter of SUM with an OVER clause.

select PaymentDate, Amount,
	sum(Amount) over (order by PaymentDate) as RunningTotal
from #Test
order by PaymentDate;

The RunningTotal column will show the total of all payments made on that day and any earlier date.

We can also use PARTITION to display a running total for just that month as well.

select PaymentDate, Amount, datename(month, PaymentDate) as MonthName,
	sum(Amount) over (partition by month(PaymentDate) order by PaymentDate) as MonthRunningTotal
from #Test
order by PaymentDate;

In the first example we used default settings for the OVER clause, which is for the current row plus all rows before it. Here is the first query with the default explicitly given.

select PaymentDate, Amount,
	sum(Amount) over (order by PaymentDate rows unbounded preceding) as RunningTotal
from #Test
order by PaymentDate;

We can change this setting to return other combinations. In the final example, we’ll return the running total as the payment amount for the current row plus the amount from the day before.

select PaymentDate, Amount,
	sum(Amount) over (order by PaymentDate rows 1 preceding) as RunningTotal
from #Test
order by PaymentDate;

Window Functions
SELECT – OVER Clause

When assigning values to a variable, we can use SET or SELECT. SET is the ANSI standard method. If we have a set value we want to assign to a variable, either method works just as well as the other. When we are retrieving values from a table or view, then there are some differences in behavior.
In order to run through some examples, we’ll set up a temp table with 1 column and 2 rows of data.

drop table if exists #Test;
create table #Test (RecordId int not null primary key);
insert into #Test(RecordId) values (1), (2);

When selecting by the primary key, we know we’ll get one value, so things are straightforward.

declare @VarSelect int;
declare @VarSet int;

select @VarSelect = RecordId from #Test where RecordId = 1;
set @VarSet = (select RecordId from #Test where RecordId = 1);

select @VarSelect as VarSelect, @VarSet as VarSet;

Let’s try an example of retrieving a value that doesn’t exist in the table. We’ll set 0 as the default value for the variables.

declare @VarSelect int = 0;
declare @VarSet int = 0;

select @VarSelect = RecordId from #Test where RecordId = 3;
set @VarSet = (select RecordId from #Test where RecordId = 3);

select @VarSelect as VarSelect, @VarSet as VarSet;

Using the SET method will return NULL, where the SELECT will leave the default value in place. If we’re using the variable one time, this behavior may not cause issues, but reusing the variable could cause confusion.
In the following example, the first select will return 1. The second select won’t find a value, so it will also return 1.

declare @VarSelect int = 0;

select @VarSelect = RecordId from #Test where RecordId = 1;
select @VarSelect as VarSelect;

select @VarSelect = RecordId from #Test where RecordId = 3;
select @VarSelect as VarSelect;

When returning multiple values, a SET will raise this error:
“Msg 512, Level 16, State 1, Line x
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.”
The SELECT will return one of the values from the result set.

declare @VarSelect2 int = 0;
declare @VarSet2 int = 0;

select @VarSelect2 = RecordId from #Test;
set @VarSet2 = (select RecordId from #Test);

select @VarSelect2 as VarSelect, @VarSet2 as VarSet;

In this case, I would rather an error be thrown that to just return one of the result set values.
Overall, it seems like SET it the better choice. We’ll get an error if multiple values are returned, and if no value is returned then we’ll get a NULL.

A coworker and I were looking at a request where we needed to write float values to a file, but have the values be of a uniform length (In this case, the float column was storing a monetary value, which isn’t a good idea, but that’s what we had to work with). In this case, all values were out to 4 decimal places, so it was a matter of padding the value to the left of the decimal to get everything to an equal length. The first thought was to convert the value to a string, pad the from with zeros, and from that take a string of the specified length.
To my surprise, it turns out that casting a float to a string data type (a varchar in this case) ends up rounding the value in some cases, and converting to scientific notation in others. With a little Googling we found out that using the STR function is the recommended way to make this conversion. STR takes has length and decimal value parameters to control how many digits are output.
I had used the FORMAT function for dates before, but it also handles numbers as well. This turned out to be the approach that was used, since in the FORMAT function we can specify padding to a certain length.
Here’s SQL for some test data long with the different approaches.

create table dbo.FloatTest(
FloatValue float not null
);

insert into dbo.FloatTest values (123.45678);
insert into dbo.FloatTest values (2.66993256);
insert into dbo.FloatTest values (0.00001);
insert into dbo.FloatTest values (55555.84);
insert into dbo.FloatTest values (321.0987654);

select  FloatValue as OriginalValue, 
        cast(FloatValue as varchar(15)) as CastValue,
	str(FloatValue, 20, 10) as StrValue, 
	format(FloatValue, 'F9') as FormatValue,
	format(FloatValue, '0000000000.##########') as CustomFormatValue
from dbo.FloatTest;

go

With FORMAT, we can specify a standard format for floats with F, followed by the number of decimal digits. Or we can create a custom format. Using a 0 for a required digit and a # for an optional digit allows us greater control over what is returned.