Counting currency...

I was just stumbling over an problem on a forum. They wanted to count currency. Let us imagine you have an ATM that delivers every single coin and bill from a specific currency, and people will withdraw any specific amount, and you want to calculate how many of each to deliver.

Well, look no further.

DECLARE @Pengar int = 9888;
DECLARE
@Valörer TABLE (Värde int, Typ char(5), Benämning varchar(25))
INSERT INTO @Valörer (Värde, Typ, Benämning)
VALUES (1, 'Mynt', '1 kr')
, (2, 'Mynt', '2 kr')
, (5, 'Mynt', '5 kr')
, (10, 'Mynt', '10 kr')
, (20, 'Sedel', '20 kr')
, (50, 'Sedel', '50 kr')
, (100, 'Sedel', '100 kr')
, (200, 'Sedel', '200 kr')
, (500, 'Sedel', '500 kr')
, (1000, 'Sedel', '1000 kr');
WITH
cash AS (
SELECT
ROW_NUMBER() OVER(ORDER BY Värde DESC) AS Ordning,
Värde, Typ, Benämning
FROM @Valörer WHERE Värde <= @Pengar
),
b AS (
SELECT
Ordning, Värde, Typ, Benämning,
(@Pengar - (@Pengar % Värde))/Värde Antal,
@Pengar % Värde AS Rest
FROM cash a WHERE Ordning = 1
UNION ALL
SELECT
a.Ordning, a.Värde, a.Typ, a.Benämning,
(b.Rest - (b.Rest % a.Värde))/a.Värde,
b.Rest % a.Värde
FROM cash a INNER JOIN b ON a.Ordning = b.Ordning + 1
),
cashing as (
SELECT
Typ, Benämning, Antal,
Antal * Värde AS [Valör Värde],
SUM(Antal * Värde) OVER(ORDER BY Ordning ASC) AS [Totalt Värde]
FROM b WHERE Antal > 0
)
SELECT * FROM cashing;

Some ideas are great, and some are not, and a simple trick...

First of all I'd like to say thanks to the minority. Last week I was in Copenhagen and delivered the worst session ever. A minority liked it, and the majority didn't. So, to the majority I would like to say, I'm sorry. My idea for the session was about sharing some from the soft side of a large project, with some technology, and try to add some "I recognise this"-feeling in it. The idea might have been great in my mind, and I do Believe that some sessions need to be like that, but in the context my idea was not so great. So once again, I'm deeply, truly, from the bottom of my heart, I'm sorry.

Lesson learned!

To cite the King of Sweden, Carl XVI Gustav, about a not so great decision, "let us turn the page, to a new chapter, like when you are reading those books, the ones that make a sound when it's time to turn the page".

On the other hand, I'm looking forward to fulfill the promise I made. There will be a long series of posts and videos covering all the topics of the session. With details and easy to follow guides of implementing and solving issues. And I will try to get the first one done this week.

For the future, I also have a promise, next time you have the chance of attending a session of mine. Give me the opportunity to make it different back again. In the future I'll keep it back to only Technical, and majority of demos, and "normal".

There are some reasons behind this post, first of all the appology, secondly to remind myself, and thirdly to turn the page, and finally as launchpad for the series of posts and videos.

Actually, I'll start with the simplest of simple, the following is a copy of an unattended configuration file that install an instance of the database Engine. The parameters QUIETSIMPLE="True" set the install to automatic install, INDICATEPROGRESS="False" hides the progress bar. And by the way, create them with the SQL Server Installation Center, and edit them with Notepad:

;Configuration File
[OPTIONS]
ACTION="Install"
ENU="True"
QUIETSIMPLE="True"
UpdateEnabled="False"
ERRORREPORTING="False"
USEMICROSOFTUPDATE="False"
FEATURES=SQLENGINE,REPLICATION,FULLTEXT
UpdateSource="MU"
HELP="False"
INDICATEPROGRESS="False"
X86="False"
INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"
INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"
INSTANCENAME="InstanceName"
SQMREPORTING="False"
INSTANCEID="InstanceName"
INSTANCEDIR="C:\Program Files\Microsoft SQL Server"
AGTSVCACCOUNT="DOMAIN\groupManagedServiceAccount$"
AGTSVCSTARTUPTYPE="Automatic"
COMMFABRICPORT="0"
COMMFABRICNETWORKLEVEL="0"
COMMFABRICENCRYPTION="0"
MATRIXCMBRICKCOMMPORT="0"
SQLSVCSTARTUPTYPE="Automatic"
FILESTREAMLEVEL="0"
ENABLERANU="False"
SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"
SQLSVCACCOUNT="DOMAIN\groupManagedServiceAccount$"
SQLSYSADMINACCOUNTS="DOMAIN\groupSQLAdmins"
ADDCURRENTUSERASSQLADMIN="False"
TCPENABLED="1"
NPENABLED="0"
BROWSERSVCSTARTUPTYPE="Automatic"
FTSVCACCOUNT="NT Service\MSSQLFDLauncher$InstanceName"

And to start the unattended install, /IAcceptSQLServerLicenseTerms=true automatically accepts the license:
Setup.exe /ConfigurationFile=ConfigFile.INI /IAcceptSQLServerLicenseTerms=true