Multiple parameters in a select statement and exclude it if it is null

SELECT *

  FROM AdminUnitTemplates aut

  WHERE ID = COALESCE(@ID, ID)

    AND TempName = COALESCE(@TempName, TempName)

    AND Title = COALESCE(@Title, Title)

       AND AdminUnitID = COALESCE(@AdminUnitID, AdminUnitID)

Using the previous query, you can skip using if statements to generate the query. 

How to download files from Slide share

 1. Go to the slideshare and inspect the source and get the link and use the following code in python 

import urllib.request

# URL of the image to be downloaded

# Loop over a range of numbers with a different starting and ending value

for i in range(1, 311):

   url = 'https://image.slidesharecdn.com/an14v1consar-160210214304/75/annex-14-icao-arabic-version-'+str(i)+'-2048.jpg'

   filename = str(i)+'.jpg'

   urllib.request.urlretrieve(url, filename)

Convert the images to pdf using the following code 

from reportlab.lib.pagesizes import letter

from reportlab.pdfgen import canvas

from PIL import Image

# List of image filenames to combine

image_files =[]

for i in range(1, 311):

   image_files.append(str(i)+'.jpg')

# Create a new PDF file

pdf_file = canvas.Canvas('images.pdf', pagesize=letter)

# Loop over the image files and add them to the PDF

for image_file in image_files:

    # Open the image file using PIL

    image = Image.open(image_file)

    # Calculate the aspect ratio of the image

    width, height = image.size

    aspect_ratio = height / width

    # Add the image to the PDF

    pdf_file.setPageSize((letter[0], letter[0] * aspect_ratio))

    pdf_file.drawImage(image_file, 0, 0, letter[0], letter[0] * aspect_ratio)

    # Add a new page to the PDF for the next image

    pdf_file.showPage()

# Save the PDF file

pdf_file.save()

Statistical fundamentals and terminology for model building and validation

1. Mean: This is a simple arithmetic average, which is computed by taking the aggregated sum of values divided by a count of those values. The mean is sensitive to outliers in the data. An outlier is the value of a set or column that is highly deviant from the many other values in the same data; it usually has very high or low values. 

2. Median: This is the midpoint of the data, and is calculated by either arranging it in ascending or descending order. If there are N observations. 

3. Mode: This is the most repetitive data point in the data

import numpy as np

import statistics as stats

data = np.array([4,5,1,2,7,2,6,9,3,9,9,2])

# Calculate Mean

dt_mean = np.mean(data) ; print ("Mean :",round(dt_mean,2))

# Calculate Median

dt_median = np.median(data) ; print ("Median :",dt_median)

# Calculate Mode

dt_mode = stats.multimode(data);

print(dt_mode)

Result:

Mean : 4.92 Median : 4.5 Mode: [2, 9]

After real test, WMS is faster than WMTS using openlayer 6

 To run the following code you have to import the required js and css files 

HTML

Python grouping

 import numpy as np

import pandas as pd

from pandas import Series, DataFrame

import seaborn as sn

address = 'mtcars.csv'

cars = pd.read_csv(address)

cars.columns = ['car_names', 'mpg', 'cyl', 'disp', 'hp', 'drat', 'wt', 'qsec', 'vs', 'am', 'gear', 'carb']

cars.head()

address = 'mtcars.csv'

cars = pd.read_csv(address)

cars.columns = ['car_names', 'mpg', 'cyl', 'disp', 'hp', 'drat', 'wt', 'qsec', 'vs',

                'am', 'gear', 'carb']

cars.head()

cars_groups = cars.groupby(cars['cyl'])

#cars_groups.mean()

sn.barplot(x='cyl', y = 'disp', data = cars)

cars['cyl'].value_counts().plot.bar();

cars['gear'].value_counts().plot.bar();

cars_groups = cars.groupby(cars['gear'])

cars_groups.mean()

cars_groups = cars.groupby(cars['gear'])

cars_groups.count()

Python read json data from API

import pandas as pd

import requests

d = requests.get("https://www.yourwebsite.com/controller?ApiKey=value").json()

df = pd.DataFrame.from_dict(d['records'])

df.shape

How to transpose data in MSSQL

 

Declare @UserID nvarchar(256)

Declare       @MsgType int

 

set @UserID = userID

set @MsgType = 3

 

CREATE TABLE #tmpBus3

(

   AdminUnitID int,

   AdminUnitName nvarchar(256),

   CategoryName nvarchar(256),

   CategoryID int,

   CategoryLevelID int,

   ParentID int,

   IsMainAdminUnit bit,

   PersonID int,

   Name nvarchar(256),

   UserID nvarchar(256),

   level int,

   LevelName nvarchar(256)

)

       INSERT INTO #tmpBus3

       Exec [Archv].Cust_GetContactsbyUserID @UserID,@MsgType;

 

       DECLARE @tmpTable3 AS nvarchar(max);

       SELECT @tmpTable3 = CONCAT(COALESCE(@tmpTable3 + ']  nvarchar(255), [','['),CONCAT('Col', ROW_NUMBER() OVER(ORDER BY LevelName ASC)))

         from #tmpBus3

         set @tmpTable3='CREATE TABLE #tmpTable3 ( '+@tmpTable3+']  nvarchar(255) )'

           DECLARE @levels AS nvarchar(max);

SELECT @levels = CONCAT(COALESCE(+@levels + ''',N''',''), AdminUnitName)

  from #tmpBus3

  set @levels=''''+@levels+''''

 

         DECLARE @SqlStatement1 NVARCHAR(MAX)

         SET @SqlStatement1 = N''+@tmpTable3+' insert into #tmpTable3 values ('+@levels+') select * from #tmpTable3';     

         print @SqlStatement1

          EXEC(@SqlStatement1)

Drop Table #tmpBus3

The result will be as in the following screenshot