What is a Microsoft MVP?

I was recently interviewed by Carlos Chacon (@CarlosLChacon) and Steve Stedman (@SQLEmt) for their SQL Data Partners podcast. I had chatted with Carlos at PASS Summit a couple of years back, and we finally got around to doing an interview about being an MVP.

NOTE: One aspect of the MVP program changed between when the podcast was recorded and when it was published. MVPs are no longer awarded on a quarterly basis. It is now monthly.

So, first question: Just what the heck is a Microsoft MVP anyway? MVP stands for Most Valuable Professional. This is an award that Microsoft gives to people in the community who share their knowledge about Microsoft technologies. Microsoft employees are not eligible. While it is an award, it’s also a status. The award is given for one year, so you can call yourself an MVP as long as you have been awarded within the past year.

How might you be eligible? You must have a substantial impact on the technical community. That could be by blogging, answering questions, writing books, teaching public classes, presenting, creating an open source tool, and more. You don’t have to do everything, but the things you do should impact lots of people. Hopefully, they will be things that you love to do. I enjoy presenting and writing, so those are what I do the most.

To be considered, someone, usually another MVP, must nominate you. Your contributions over the past year will be reviewed. How someone is chosen is not really public knowledge, but you must be doing a lot of community work.

I truly believe that being an MVP is a bonus for doing the things that you love to do and would do even without the MVP award.

Do you have to be an expert in everything? No, there are several technical areas for MVP status. I am a Data Platform MVP. Previously, this was called SQL Server, but because of the many data related technologies on Azure that don’t use SQL Server, the name was changed in 2015. Actually, Microsoft revamped all the categories at that time.

What are the benefits of being an MVP? Mostly, it is an honor to be chosen. There are some great benefits, too, such as an MSDN license and the MVP Summit. Being able to list the award on your resume and greater exposure if you are a consultant are some of the non-tangible benefits.

Be sure to listen to the podcast. I was even able to bring up Star Trek!

 

 

 

 

 

 

Posted in Life in IT | Leave a comment

Data Types in SSRS Mobile Reports Category Charts

There are several chart types found in traditional paginated SSRS reports. The charts have many properties, but these are configured the same from chart type to chart type. SSRS Mobile report charts are very different. Each chart has a specific purpose and unique properties.

The Category Chart displays a value over a series. For example, you may want to display total sales by territory. The category, or series, is required to be one of the character data types (char, nvarchar, etc.).

To demonstrate, I created a new shared dataset called SalesCountByYear. Here is the query using the AdventureWorks2014 database:

SELECT COUNT(*) AS SalesCount, YEAR(OrderDate) AS OrderYear 
FROM Sales.SalesOrderHeader 
GROUP BY YEAR(OrderYear);

After launching Mobile Report Publisher, I added a Category Chart.

dt2

After switching to the Data page, this is how the simulated data looks:

dt3.PNG

Notice that the series field can be only Category or FilterKey. These both contain character data. Next, I added the SalesCountByYear dataset to the Mobile Report.

dt4

Notice that OrderYear displays decimal points. I switched the dataset in the Series field name property, and found that neither of the columns in the dataset can be used.

dt5

Numeric columns cannot be set as a Series name field. To work around this, I modified the dataset, casting OrderYear as a CHAR(4).

dt7

You can refresh the dataset in the Mobile Report, but I found better results by deleting it and adding it back. Once that was done, the OrderYear values looked better without the decimal places, and OrderYear was available to use as the Series name field.

dt8.PNG

This is how the chart looked after making the data type conversion:

dt9.PNG

 

 

The Comparison Category chart works similarly to the Comparison Chart. The Comparison Category Chart allows you to compare two values over the series. You will see the same data type requirements for the Series name field property.

Would you like to learn more about SSRS 2016 Mobile Reports? Check out my Pluralsight course!

 

Posted in SSRS | 1 Comment

T-SQL Puzzler

UPDATE: I have included an alternate solution from one of my readers!

I have presented on T-SQL Window Functions at least 30 times over the past five years. I love the comments I get after the presentations. Without fail, at least one person will tell me how the session helped them figure out the solution to a problem they have been struggling with for months. Sometimes, a person will ask my advice about a particular query. At least for me, it’s difficult to just come up with a solution in a couple of minutes while I am trying to clear the area for the next presenter. At Cleveland SQL Saturday, a gentleman presented me with a puzzle that I really enjoyed solving. He gave me permission to share it with you.

The problem involves a call center. Each employee answers calls until time for the next employee to go on duty. Sometimes, an employee will come back later in the day. For example, employee 1 has two shifts on the first day.

Here is a script to create a table and populate it with some sample data:

CREATE TABLE calllist(empID int, CallDate date, CallTime time);

INSERT INTO calllist
VALUES (1,’1/1/2017′,’9:15′),(1,’1/1/2017′,’9:18′),(1,’1/1/2017′,’9:20′),(1,’1/1/2017′,’12:00′),(2,’1/1/2017′,’13:00′),(2,’1/1/2017′,’13:15′),(2,’1/1/2017′,’13:18′),(2,’1/1/2017′,’13:20′),(1,’1/1/2017′,’15:00′),(1,’1/1/2017′,’15:15′),(3,’1/1/2017′,’18:00′),(3,’1/1/2017′,’18:15′),(3,’1/1/2017′,’18:30′),(3,’1/2/2017′,’9:15′),(3,’1/2/2017′,’9:18′),(3,’1/2/2017′,’9:20′),(3,’1/2/2017′,’12:00′),(1,’1/2/2017′,’13:00′),(1,’1/2/2017′,’13:15′),(1,’1/2/2017′,’13:18′),(2,’1/2/2017′,’13:20′),(1,’1/2/2017′,’15:00′),(1,’1/2/2017′,’15:15′),(3,’1/2/2017′,’17:00′),(3,’1/2/2017′,’17:15′),(3,’1/2/2017′,’19:30′);

The required outcome will display each employee shift along with the first and last call:

ntsqp1

To come up with a solution, I first thought about how to solve it with a traditional cursor. I won’t provide a cursor solution here, but it would involve looping through the rows in order and finding the first and last employee rows. When the last EmpID doesn’t match the next EmpID, then the previous row is the last call for the previous EmpID and the current row is the first call for the next EmpID.

NOTE: The solution to this problem assumes that there are at least two calls per shift.

In 2012, four T-SQL window functions became available that let you look at expressions from different rows: LAG, LEAD, FIRST_VALUE, and LAST_VALUE. For this puzzle, LAG and LEAD seemed to be the answer.  In this case, I used the optional default parameter to replace any NULLs with -1.

SELECT *, 
    LAG(empID,1,-1) OVER(PARTITION BY CallDate ORDER BY calltime)   
    AS   PrevEmpID, 
    LEAD(empID,1,-1) OVER(PARTITION BY CallDate ORDER BY calltime)  AS NextEmpID
FROM calllist
ORDER BY CallDate, CallTime;

Here are the partial results:

ntsqp2

The next step is to compare the current EmpID with the previous and next values. When they don’t match, then the row is either the first call or the last call for that employee. To solve this in a step-by-step manner, the previous query is moved to a common table expression (CTE).

WITH CTE1 AS (
    SELECT *,
       Lag(empID,1,-1) OVER(PARTITION BY CallDate ORDER BY calltime) AS  
         PrevEmpID,
    LEAD(empID,1,-1) OVER(PARTITION BY CallDate ORDER BY calltime)  AS NextEmpID
FROM calllist
)
SELECT empID, CallDate, CallTime,
    CASE WHEN EmpID  PrevEmpID THEN 1 END AS FirstCall,
    CASE WHEN EmpID  NextEmpID THEN 1 END AS LastCall
FROM CTE1;

ntsqp3

The next step is to eliminate the rows that are not a first or last call. Once again, the previous outer query is added to a CTE.

WITH CTE1 AS (
    SELECT *,
      LAG(empID,1,-1) OVER(PARTITION BY CallDate ORDER BY calltime) AS     
         PrevEmpID,
      LEAD(empID,1,-1) OVER(PARTITION BY CallDate ORDER BY calltime) AS   
         NextEmpID
FROM calllist
),
CTE2 AS (
    SELECT empID, CallDate, CallTime,
       CASE WHEN EmpID  PrevEmpID THEN 1 END AS FirstCall,
       CASE WHEN EmpID  NextEmpID THEN 1 END AS LastCall
FROM CTE1)
SELECT empID, CallDate, CallTime, FirstCall, LastCall
FROM CTE2
WHERE FirstCall IS NOT NULL OR LastCall IS NOT NULL;

ntsqp4

Now we can take advantage of the LEAD function again to grab the CallTime from the following row. Again, the previous outer query is a new CTE level.

WITH CTE1 AS (
    SELECT *,
       LAG(empID,1,-1) OVER(PARTITION BY CallDate ORDER BY calltime) AS    
         PrevEmpID,
       LEAD(empID,1,-1) OVER(PARTITION BY CallDate ORDER BY calltime) AS 
         NextEmpID
FROM calllist
),
CTE2 AS (
    SELECT empID, CallDate, CallTime,
       CASE WHEN EmpID  PrevEmpID THEN 1 END AS FirstCall,
       CASE WHEN EmpID  NextEmpID THEN 1 END AS LastCall
FROM CTE1),
CTE3 AS (
    SELECT empID, CallDate, CallTime, FirstCall, LastCall
    FROM CTE2
    WHERE FirstCall IS NOT NULL OR LastCall IS NOT NULL)
SELECT EmpID, CallDate, CallTime, FirstCall,
    LEAD(CallTime) OVER(PARTITION BY CallDate ORDER BY CallTime) AS NextCallTime
FROM CTE3;

ntsqp5

The final step is to eliminate the rows where FirstCall is null.

WITH CTE1 AS (
    SELECT *,
       LAG(empID,1,-1) OVER(PARTITION BY CallDate ORDER BY calltime) AS
         PrevEmpID,
       LEAD(empID,1,-1) OVER(PARTITION BY CallDate ORDER BY calltime) AS   
         NextEmpID
FROM calllist
),
CTE2 AS (
    SELECT empID, CallDate, CallTime,
        CASE WHEN EmpID  PrevEmpID THEN 1 END AS FirstCall,
        CASE WHEN EmpID  NextEmpID THEN 1 END AS LastCall
FROM CTE1),
CTE3 AS (
    SELECT empID, CallDate, CallTime, FirstCall, LastCall
    FROM CTE2
    WHERE FirstCall IS NOT NULL OR LastCall IS NOT NULL),
CTE4 AS (
    SELECT EmpID, CallDate, CallTime, FirstCall,
    LEAD(CallTime) OVER(PARTITION BY CallDate ORDER BY CallTime) AS NextCallTime
FROM CTE3)
SELECT EmpID, CallDate, CallTime AS FirstCallTime, NextCallTime AS LastCallTime
FROM CTE4
WHERE FirstCall IS NOT NULL;

Here is the final result:

ntsp6

It’s possible to eliminate one CTE level by using LEAD to find the next call time for every row in step 1. Maybe there is even a better way to solve this query. Add a comment if you have figured out another solution.

To learn more about T-SQL window functions, check out my book or Pluralsight course!

After posting this, one of my readers, Uri Dimant, contacted me about another solution. His solution is a mix of CTE and nested subquery, so I have rearranged it to just use CTEs. There is no difference really, except that I think it is easier to understand with CTEs. This solution is very similar to the Islands technique:

WITH cte1 AS ( 
	SELECT *,ROW_NUMBER () OVER (ORDER BY CallDate,CallTime) ordinal 
	FROM calllist ), 	
cte2 AS (	
	SELECT *, ordinal - ROW_NUMBER() OVER(PARTITION BY empid ORDER BY ordinal) AS grp 
	FROM cte1 )
SELECT empid,CallDate,MIN(CallTime) FirstCallTime,MAX(CallTime) LastCallTime 
FROM cte2 
GROUP BY CallDate,empid,grp ORDER BY MIN(ordinal) ;

This solution uses the difference in two ROW_NUMBERS to create groupings. Once you have the groups, you can just get the MIN and MAX. This solution is very straightforward. At least for me, I understand the solution that I came up with, but both are valid.

Posted in T-SQL, T-SQL Window Functions | 2 Comments