Archive for Database
Date functions MSSQL
This Getdate: Month Year Select DateName(Month,getdate())+’ ‘+ DateName(YYYY,getdate()) Result:
Read moreIntersect
Similar to the UNION command, INTERSECT also operates on two SQL statements. The difference is that, while UNION essentially acts as an OR operator (value is selected if it appears in either the first or the second statement), the INTERSECT command acts as an AND operator (value is selected only if it appears in both […]
Read moreSQL HAVING Clause
Having clause is used to filter data based on the group functions. This is similar to WHERE condition but is used with group functions. Group functions cannot be used in WHERE Clause but can be used in HAVING clause.
Read moreCase
CASE is used to provide if-then-else type of logic to SQL. Its syntax is: SELECT CASE (“column_name”) WHEN “condition1” THEN “result1” WHEN “condition2” THEN “result2” … [ELSE “resultN”] END FROM “table_name” “condition” can be a static value or an expression. The ELSE clause is optional.
Read moreCoalesce Function
The COALESCE function in SQL returns the first non-NULL expression among its arguments. It is the same as the following CASE statement: SELECT CASE (“column_name”) WHEN “expression 1 is not NULL” THEN “expression 1” WHEN “expression 2 is not NULL” THEN “expression 2” … [ELSE “NULL”] END FROM “table_name” For examples, say we have the […]
Read moreSQL Math Functions
he following is a reference to the main math functions in SQL: ABS(X) Absolute value-converts negative numbers to positive, or leaves positive numbers alone CEIL(X) X is a decimal value that will be rounded up. FLOOR(X) X is a decimal value that will be rounded down. GREATEST(X,Y) Returns the largest of the two values. LEAST(X,Y) […]
Read moreLansweeper: hardware, network and software inventory for windows networks
Lansweeper is an automated network discovery and asset management tool which scans all your computers and devices and displays them in an easy accessible web interface. There is no need to install any agents on the computers, all scanning is done by standard build-in functionality.
Read moreSummarizing by month and year with datepart
There are many ways of searching and doing date calculations. SELECT DATEPART(yyyy,sales_date) AS ‘Year’, MAX(DATENAME(m,sales_date)) AS ‘Month’, COUNT(sales_date) AS ‘Units per Month’ FROM sales GROUP BY DATEPART(yyyy,sales_date), DATEPART(mm,sales_date) ORDER BY DATEPART(yyyy,sales_date), DATEPART(mm,sales_date);
Read more