Question 1: How do I retrieve the date of the first and last day of a date's month?
The following screen shot shows an Excel workbook containing a column (A) of dates from which we extract the first and last dates of the month of these dates:
by Mandy Doward - 20/4/2016
In Part 2 of our “SQL Server - Working With Dates” blog we use the DATEPART function.
DATEPART Function
DATEPART works in the same way as DATENAME, but returns numbers for the weekday and month instead of the word. (weekdays start with 1 for Sunday by default, but this can be changed).
The following example shows a comparison of DATENAME and DATEPART:
by Mandy Doward - 20/4/2016
Ian & I have received a lot of queries from past delegates about handling various date challenges when extracting data from SQL Server and Oracle databases, so we are adding them to our blog for your reference. There will be a series of posts and this is Part 1.
The following examples are for SQL Server, and are based on the AdventureWorks2014 sample database.
The following queries uses the DATENAME and DATEADD functions.
DATENAME Function
DATENAME can be used to extract a specified part of a date/time value. It takes two arguments:
by Mandy Doward - 17/3/2016
The following example is based on an Adventure Works cube and shows you how to create a measure that ranks countries according to sales revenue.
It implements the following MDX functions:
- Rank
- CurrentMember
- Children
- Order
Rank by Largest to Smallest
The MDX WITH MEMBER statement creates a temporary measure for the rank value based on total internet slaes in the calendar year 2006, and the associated SELECT part uses the rank to order the countries.
by Mandy Doward - 20/4/2016
I was recently asked by a delegate how they could calculate lost work hours due to sick days on a per month basis. This might sound fairly straight forward, but their database only stored the start date for the sickness period and the number of days for the sickness period. So, that means that if an employee was off sick for a period of 5 days starting October 28th, 3 of the days should be counted into October and 2 of the days should be counted in November. Not so simple after all!
by Mandy Doward - 14/3/2016
Last week I was asked the following question:
“Is there any way that you can find out which hosts or IP addresses have been accessing your Apache Web Server the most?”
The answer to this question is “YES!”.
Apache maintains an access log file, its default location is in the Apache logs directory, but this can be defined in the Apache httpd.conf file. The “out of the box” location for the Apache distribution with CentOS/= and RHEL 6 and 7 is: /etc/httpd/logs/access_log. Here is an example of the the default log entries for this file:
by Mandy Doward - 16/3/2016
In its out of the box configuration you may find that CentOS Linux has iptables configured to prevent inbound HTTP requests.
You can add access for inbound HTTP requests on port 80 with the following command:
iptables -I INPUT 4 -p tcp -m state --state NEW -m tcp -dport 80 -j ACCEPTLine 4 of the iptables -L output now shows that http is allowed inbound from anywhere:
by Mandy Doward - 14/3/2016
SQL Server 2012 introduced a RESULT SET clause to the EXECUTE statement.
It can be used to specify alternate data types and column names for result sets returned by an EXECUTED statement or Stored Procedure.
The examples in this article are based on the AdvetureWorks2014 database.
The following example shows its use with an ad-hoc query example.
by Mandy Doward - 14/3/2016
Did you know that you can use the SQL Server aggregate functions SUM, COUNT, MAX, MIN and AVG with an OVER Clause now?
Using an OVER clause you can produce individual record values along with aggregate values to different levels, without using a GROUP BY clause. You can also produce running totals, rolling averages, etc.
The examples in this article are based on the AdventureWorks2014 database.
Aggregate To Different Levels with OVER (PARTITION BY ….)
by Mandy Doward - 25/4/2018
The following question was put to me last week:
“In the following table there are month on month totals (perhaps for sales). I would like to show the red, yellow and green trend arrows against each value to show the change against the previous month’s value. Using the same logic I need to show the same coloured arrows for the same data expressed as a percentage change.”
In other words, changing this chart...