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

It's about time...

It's been a while since last post, and it is not as frequent as I wish, but it's about time or more lack of time.

First I want to celebrate Microsoft SQL Server for yet another amazing release. Now it is Microsoft SQL Server 2019, there are so many new features I guess I must start writing some about everything I love in this product. And that is almost a promise, I'll try to do more posts, and feature all this goodness.

Now over to todays topic, time. I found a question on the SQL Server 2008 - 2019 Facebook group, https://www.facebook.com/groups/7193059737/10157742825254738. And it was from Islam Selim:

Hi all
I need to create time column in view calculate differeance between two other datetime columns.. Plz help

And my response was this:

CREATE FUNCTION dbo.fn_TimeBetween (
 @start datetime,
 @end datetime,
 @days int  = 0 -- 0 for 00H,00M,00S (Default), 1 for 00D,00H,00M,00S
 )
RETURNS varchar(max)
AS
BEGIN
DECLARE
 @distance int = DATEDIFF(second, @start, @end),
 @result varchar(max);
DECLARE
 @seconds int = @distance % 60
SET
 @distance = (@distance - @seconds) / 60
DECLARE
 @minutes int = @distance % 60
SET
 @distance = (@distance - @minutes) / 60
DECLARE
 @hours int = @distance % 24
SET
 @distance = (@distance - @hours) / 24
IF @days = 0
 SET @result = CAST(@distance * 24 + @hours AS varchar(max)) + 'H,' +
 RIGHT('0' +  CAST(@minutes as varchar(max)), 2)    + 'M,' +
 RIGHT('0' +  CAST(@minutes as varchar(max)), 2)    +'S'
ELSE
 SET @result = CAST(@distance AS varchar(max))     + 'D,' +
 RIGHT('0' +  CAST(@hours AS varchar(max)), 2)    + 'H,' +
 RIGHT('0' +  CAST(@minutes as varchar(max)), 2)    + 'M,' +
 RIGHT('0' +  CAST(@minutes as varchar(max)), 2)    + 'S'
RETURN @result
END

Changes

This is another not about SQL post. As of march 1st of 2019 I'm working for Axians IoT Operations and are adding value on top of their amazing solution microServiceBus, which connects any device to any IOT hub. Or most major ones at least.

For me this is about changing life, and adding a balance between work and life. In todays' ever changing world it is crucial to reflect over life. I did for sure and am truly grateful for this opportunity.

I will get back to blogging, answering questions at forums, and interact with the community as well as product groups. I will also start blueprinting the book I've been thinking about for years.

More to come...

Yours truly,
Mattias

Inside the fake science factory...

I just saw an interesting DEF CON session. https://www.youtube.com/watch?v=ras_VYgA77Q

Inside the fake science factory, it's about fake news ugly sibling. There are organisations that offer a "platform" for "scientists" to publish their "scientific papers" and hosting "scientific conferences" to gain credibility to the "reports" and "scientists".

See it, it might help you to discover if what you are looking at is published through at least a few of these fake science factories.

Try it, enter below search in your google search. Replace "your search object" with what you want to check up inside the quotes:

"your search object" site:omicsonline.org OR site:sciencedomain.org OR
site:omicsgrouponline.org OR site:waset.us OR
site:waset.org OR site:waet.org OR
site:academicjournals.org OR site:imed.pub OR
site:thescipub.com OR site:austinpublishinggroup.com OR
site:iosrjournals.org OR site:alliedacademies.com OR
site:conferenceseries.com OR
site:sciencepublishinggroup.com OR site:davidpublisher.org

N.B. just because you find "scientific stuff or people" with the search doesn't mean it is fake. It only means they probably made the effort publishing their "science stuff" with a "fake science factory" and that they paid money for it.

Are you missing the Database Diagrams from the new SQL Server Management Studio?

If you just installed the latest preview of SSMS 18.0 you might have realized that the Database Diagrams are gone. The feature is deprecated.

Download the preview here:
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

Scroll down to the SSMS 18.0 (Preview) and try it out, there is also improvements.

So, what to do? Remember there is a bunch of INFORMATION_SCHEMA-views you can use.
This is an example how you can do it, the example is not perfect but help you out a bit.

As Always, I do not take any responsibility for any code you might find on my blog. I write queries for fun and business. Small snippets might end up here.

SQL Server vNext

Just want to tell you all that next awesomeness of SQL Server is available to test out. Check this out, https://www.microsoft.com/en-us/sql-server/sql-server-2019.

Trust me, you will love it!