Monday 27 December 2021

SQL - Using CASE Statements IN SQL Statements

How To Use IF Statements In Your SET BASED SQL

By Strictly-Software

Now if you are new to SQL you may not know the benefits of the CASE statement. It is basically the way you can implement an IF statement within your SET Based SQL.

I do a lot of work with Horse Racing and I like to rank my horses on a number of variables adding up a "Rating" score by appending values to a default value so that runners with the best score appear at the top.

An example of this would be the simplified cut down version below where I am adding points to a Ratings column that will be displayed in the final results. I used multiple CASE WHEN THEN ELSE END, statements to check various stats a horse may have and if they meet the criteria I append a score to a rolling count e.g

This example uses a couple of CASE statements to show me the horses Last Races Official Rating, but also creates a SCORE using a number of factors which I can then order by easily at the end.

The SQL

SELECT RacePK,RunnerPK,Racedatetime,IsHandicap,CourseName + N' - ' + RaceTime + ' - ' + HorseName + ' @ ' + CurrentOdds as Info,OfficialRating,
		CASE WHEN LastRaceFK>0 THEN (SELECT OfficialRating FROM RACE_RUNNERS WITH (NOLOCK)  WHERE RaceFK=run.LastRaceFK AND HorseNameCountry=run.HorseNameCountry) ELSE 0 END as LastRaceOfficialRating,Class		
		CASE ClassChange WHEN 1 THEN 'UP IN CLASS' WHEN 2 THEN 'DOWN IN CLASS' WHEN 0 THEN 'SAME CLASS' ELSE 'NA' END as ClassChange,
		CASE	WHEN LastRaceFK = 0 THEN 'NA'
			WHEN LastRaceFK > 0 AND (SELECT Class FROM RACES WITH (NOLOCK) WHERE RacePK=LastRaceFK) = '' THEN 'NO LAST CLASS INFO' 
			WHEN LastRaceFK > 0 THEN 'LAST RACE CLASS: ' + (SELECT MyClass + ' - ' + RaceName FROM RACES WITH (NOLOCK) WHERE RacePK=LastRaceFK) ELSE 'NA' END as LastRaceClass, Going, GoingCode,
		CASE	WHEN LastRaceFK = 0 THEN 'NA'
			WHEN LastRaceFK > 0 THEN 'LAST RACE GOING: ' + (SELECT UPPER(GOINGCODE) FROM RACES WITH (NOLOCK) WHERE RacePK=LastRaceFK)	
			WHEN LastRaceFK > 0 AND (SELECT GOINGCODE FROM RACES WITH (NOLOCK) WHERE RacePK=LastRaceFK) = '' THEN 'NO LAST RACE GOING INFO' END as LastRaceGoing,
		LastFinishPos, FORM,  
		Score = CASE WHEN Favourite = 1 THEN 50 
			     WHEN RunnerPK = [dbo].[udf_GET_RUNNER_POSITION](RacePK,2) THEN 35
			     WHEN RunnerPK = [dbo].[udf_GET_RUNNER_POSITION](RacePK,3) THEN 20 
			     WHEN RunnerPK = [dbo].[udf_GET_RUNNER_POSITION](RacePK,4) THEN 10 
			     ELSE 0 END +
			-- 0 = no change, 1 = up in grade, 2 down in grade, 3 = unknown, -1 default value				
			CASE WHEN ClassChange = 1 AND KeyWinForm LIKE '%L%' AND (HasMaxOfficialRating = 1 AND Favourite = 1) AND LastFinishPos = 1 THEN 50
			     WHEN ClassChange = 1 AND KeyWinForm LIKE '%L%' AND (HasMaxOfficialRating = 1 AND Favourite = 1) THEN 45
		             WHEN ClassChange = 1 AND LastFinishPos = 1 AND (HasMaxOfficialRating = 1 OR Favourite = 1) THEN 40
		             WHEN ClassChange = 1 AND KeyWinForm LIKE '%L%' THEN 35
			     WHEN ClassChange = 1 AND KeyWinForm LIKE '%L%' THEN 30			     
		             WHEN ClassChange = 1 AND KeyForm LIKE '%L%' AND LastFinishPos BETWEEN 2 AND 4 THEN 27						 		
			     WHEN ClassChange = 1 AND KeyForm LIKE '%L%' THEN 25
			-- down in grade could have won at this grade before
			     WHEN ClassChange = 2 AND KeyWinForm LIKE '%L%' AND LastFinishPos = 1 THEN 35 
			     WHEN ClassChange = 2 AND KeyWinForm LIKE '%L%' AND LastFinishPos BETWEEN 2 AND 4 THEN 30
			     WHEN ClassChange = 2 AND KeyWinForm LIKE '%L%' THEN 27
			     WHEN ClassChange IN(0,1) THEN 5
			     WHEN ClassChange = 2 THEN 3
			     WHEN ClassChange IN(3,-1) THEN - 5
			     ELSE 0
			     END + 
			-- new IF for no of runs the more win percentage the better 
			CASE WHEN Runs > 5 AND WinPerc = 0 THEN -20   
			     WHEN Runs > 5 AND WinPerc = 100 THEN 100
			     WHEN Runs > 2 AND WinPerc = 100 THEN 50
			     WHEN Runs > 5 AND PlacePerc = 100 THEN 60
			     WHEN Runs > 2 AND PlacePerc = 100 THEN 30
			     WHEN Runs > 5 AND PlacePerc > 70 THEN 50
			     WHEN Runs > 2 AND PlacePerc > 70 THEN 30
			     WHEN Runs > 2 AND LosePerc = 100 THEN -50
			     WHEN Runs > 5 AND LosePerc > 60 THEN -60
			     WHEN Runs = 0 OR LOSEPERC > 70 THEN -50 ELSE -10 END +
			 -- Official Rating higher the better
			CASE WHEN OfficialRating > 170 THEN 300
			     WHEN OfficialRating > 160 THEN 275
			     WHEN OfficialRating > 150 THEN 250
			     WHEN OfficialRating > 125 THEN 200
			     WHEN OfficialRating > 110 THEN 175
			     WHEN OfficialRating > 105 THEN 150
			     WHEN OfficialRating > 105 AND IsHandicap = 1 THEN 100
			     WHEN HasMaxOfficialRating = 1 THEN 50
			     ELSE 10 END
FROM	RACES as ra  with (nolock)
JOIN	RACE_RUNNERS as run with (nolock)  
	ON	ra.RacePK = run.RaceFK 
JOIN	COURSES as c  with (nolock) 
	ON	c.CoursePK = r.CourseFK
WHERE	RacePK = 269330 -- current race I am looking at


The Results

Below are the results from that query outputted in MS SQL Management Studio.




Another Example


This is where I am using a CASE WHEN THEN ELSE END, statement in the ORDER By clause to show the output of a race card. 

The race could be finished and in that case I would want the ORDER of the results to be from no 1 (winner) to the horse that finished last. I would also want any runners not finishing who would get a  finish position of 0 appearing last. 

I would also want any non-runners, horses who were pulled out of the event, before the race to appear at the bottom of the results.

Also with racing, in the flat, horses start the race in stalls, so if it is a flat race I want to show the stall they are in, if the race is a jump or NHF (Bumper or National Hunt Flat Race), then they use no stalls so I would not order by stall no.

The SQL

SELECT	RunnerPK,Racename,raceType,HorseName,Jockey,NonRunner,FinalOdds,CurrentOdds,Draw,Finishposition,favourite,officialrating
FROM	RACE_RUNNERS as run  WITH (nolock)
JOIN	RACES as ra  WITH (nolock)
	ON ra.RacePK=run.RaceFK
WHERE	RacePK=270423
ORDER BY CASE WHEN nonrunner=1 THEN 150
	      WHEN EXISTS(select top(1) runnerPK from race_runners as r1 WITH (nolock) where r1.Racefk=ra.RacePK and finishposition>0) THEN
		   CASE WHEN finishposition=0 then 100 ELSE finishposition END 
	      ELSE CASE when ra.racetype IN('NH','B') THEN horsename else draw END
	 END 

The Results

Below are the results from that query outputted in MS SQL Management Studio.




As I hope you can see the use of a CASE statement either in a SELECT clause or an ORDER BY is very useful for adding an IF statement into a SET based query. 

You can also use them in your JOINS to help decide which table you actually JOIN onto, for example if I had two tables, one for RACES that had not been run yet, and one for RACES that had finished I could add a CASE statement into the JOIN so that if there existed a runner with a finish position or a RACE with a finishing time I would join to the RACE_RESULTS table and for RACES not run yet I would join to the RACE_CARD table (if they were named like that for races before running and after a result was in).

There are actually lots of ways you can use CASE statements in your SQL and it may help you to be able to CONVERT looping code or procedural code with actual IF statements in a ROW by ROW CURSOR or LOOP into a SET BASED SQL statement that will perform a lot quicker.

Please post your favourite CASE statement usages in the comments section.


By Strictly-Software

Tuesday 7 December 2021

Test Your Anti Virus Software

A Quick Way To Test If Your Computers Anti-Virus Software Is Working

By Strictly-Software

This is a quick and quite a simple way of testing if your PC is protected properly from files that may contain viruses or malware. It has apparently been around for a long time but I only stumbled across it tonight and when I tested it against my AV tools I was surprised at the results.

The test is called a Eicar test file is a file, developed by the EICAR organization, that is used in testing anti-virus scanners for their integrity in detecting viruses. The actual file is simply a text file of either 68 or 70 bytes that can be created using any text editing program like Notepad. It is not actually a virus but the characters within the file should flag a hit in most Anti Virus tools making them think it is one.

With so many free anti-malware / virus software out there to use it can be a case that you have multiple apps installed to protect your computer as I have found that some AV products will detect some malware, and others malware that the previous products didn't find.

Therefore I have found that having more than one AV / Anti Malware product on my computer is the best policy just in case something is found by old school virus definitions or by heuristic behaviour detection which other AV products use.

I read something the other day about Bitcoin miners hacking into a quantum computer at some US university to leverage it's super power to mine coins. Obviously their standard AV software didn't detect whatever program was running so they actually came up with a new solution which I really don't understand why home PC users couldn't use as well.

It's a pretty simple concept in that the Quantum Computer just like any computer should only be running certain "allowed" programs.  System programs that operate the machine and then programs that have been installed on the machine by admin. All the new anti-virus tool did was hold a list of every program allowed to run on the machine, and then it would constantly scan the Task Manager or equivalent tool and if it found any program executing that wasn't on the "allowed" list it would terminate the process and quarantine or remove the executable.

To me this sounds like quite a simple solution to malware detection and a lot better than constantly updating AV software with definition lists. How hard would it be to create a program that logged all tasks and services that were OS-based, and then add to this whitelist any programs that were properly installed by an administrator. You could run the setup of this program in safe mode, or on a clean install so that it stored only safe OS programs in it's a whitelist and then when a new program was installed it would be added to the same list.

Then the AV tool would just run alongside your Task Manager looking for any process not in it's white list and flag it when found. The user could then inspect the properties and location of the file and decide whether it was legitimate or illegal and either add it to the white list or have it removed. I might even have a crack at making something like this.

Anyway this is about testing your current system to see if your AV software will detect the following file as malware which it should. I don't know what this does if anything, or whether it just matches a virus definition from some time back but I ran the test earlier and you can see my results below.

Run Your Own Anti Virus Test

1. Open Notepad -> Run -> Notepad

2. Type the following text into the file: 
X5O!P%@AP[4\PZX54(P^)7CC)7}$EICAR-STANDARD-ANTIVIRUS-TEST-FILE!$H+H*

3. Save the file somewhere on your PC as an executable, e.g. I saved my file in Documents as AVTest.exe

4. If you have multiple AV tools in your context menu. You can right click above the file and test each one to see if they detect the file as malware e.g


5. However if you are on Windows and haven't replaced Windows Defender with a premium AV tool it should always be running and detect threats as they come. It does on my PC despite me having other AV tools. As soon as I save the AVtest.exe file on my machine Windows Defender pops up a message saying it has detected malware on my machine in the top right corner and in the bottom right corner another message tells me that I don't need to do anything as "Detected threats are being cleaned" and within a minute the file disappears from my Documents folder. Therefore if you want to test other AV software you need to have them up and running and ready so you can do a quick single file virus test from your context menu before Windows Defender removes it.



Notice the two Windows Defender messages in the right hand corners of the screen when I save the file.

So I have a variety of AV tools on my system but keep Windows Defender running at all times. These were the results I got from my Context Menu test. Please let me know of any other free AV tools that either detect or DON'T detect this as a virus.

-Windows Defender: It detected the file as a virus as soon as I saved it. Showed me warning messages and said it would be removing it. Within a minute or so the file had gone.
-Malwarebytes Anti-Malware: Running a scan on this file from the context menu it detected it and quarantined it for me but a full scan did not find it.
-Norton PRO: used instead of Windows Defender on my other laptop, BT has changed from McAfee to Norton, and you get a pro version of the AV software for free from BT, I didn't even realise until they told me to update from McAfee to Norton, anyway - DID DETECT AND REMOVE IT a few seconds after I saved the file on my laptop.
-McAfee PRO: used  instead of Windows Defender as my main virus checker - DID NOT DETECT IT AS A VIRUS
-Emsisoft Security Center: - DID NOT DETECT IT AS A VIRUS.
-SuperAntiSpyware (Free Edition): - DID NOT DETECT IT AS A VIRUS

Now I cannot be a 100% sure that the reason that the last two apps did NOT detect it as a virus was that Windows Defender had somehow put a lock on the file so that they could not detect it or something however the free version of Malware Anti-Malware did detect it when running a single file scan from the right-click context menu.

Anyway, this is a good test just to make sure your computer is detecting virus files and it only takes a few minutes to copy that string of letters and place them in a text file, save it, and see which AV tool shoots up a message that a virus has been detected.

It's an old trick so I don't claim to be the first to know about it but I was just surprised at the results, it is always good to know your machine is protected and this is a very simple way without installing an actual virus.

Let me know of any other FREE AV / Anti Malware tools that don't detect it as malware in the comments.

By Strictly-Software