Wednesday, November 13, 2013

Pivoting SQL Data for Instant Reporting

I always get a bit of geeky enjoyment out of finding an excuse to take advantage of features introduced into newer versions of SQL server. Today, a coworker asked me to help him pivot some data from an issue tracking table he was using. He didn’t know what “pivot” was, but that is what he wanted.
Here’s the table with sample data:
CREATE TABLE [dbo].[Issues](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [WorkItem] [nvarchar](50) NOT NULL,
    [DateSubmitted] [date] NOT NULL,
    [DateFixed] [date] NULL,
    [Id] ASC

INSERT [dbo].[Issues](
('Test', '20130310', '20130310'),
('Test1', '20130310', NULL),
('Test2', '20130311', '20130313'),
('Test3', '20130311', '20130311'),
('Test4', '20130311', '20130320'),
('Test5', '20130312', '20130314'),
('Test6', '20130314', '20130315'),
('Test7', '20130314', '20130315'),
('Test8', '20130316', '20130319'),
('Test9', '20130319', '20130330'),
('Test10', '20130320', NULL)

He asked me to go through the table, and for each day count how many issues were submitted and how many were fixed. When he manually counted the values, he got the following results:
Date Submitted Fixed
3/10/2013 2 1
3/11/2013 3 1
3/12/2013 1 0
3/13/2013 0 1
3/14/2013 2 1
3/15/2013 0 2
3/16/2013 1 0
3/19/2013 1 1
3/20/2013 1 1
3/30/2013 0 1
After staring at the tables for a few moments, I realized that the easiest way to transform the table into the results was to use PIVOT.
Select [Date]
, ISNULL([Submitted],0) as [Submitted]
, ISNULL([Fixed],0) as [Fixed]
  (Select DateFixed as [Date], COUNT(Id) as ct, 'Fixed' as md
     from dbo.[Issues]
    where DateFixed is not NULL
group by DateFixed
   Select DateSubmitted as [Date], COUNT(Id) as ct, 'Submitted' as md
     from dbo.[Issues]
    where DateSubmitted is not NULL
group by DateSubmitted) as SourceTable
   for md in ([Submitted], [Fixed])) as pvt
order by [Date]

And there you go. Fun day with new PIVOT-y toys.

Power BI: M Query Basics (Part 2)

This is the second part of my series on Power BI M Query Basics. In Part 1 , I defined M Query and talked about the structure of how an M Qu...