Tag: SQL

Using Dynamic Values in the PIVOT Operator

I recently worked on a report for a client in which a date input parameter was used to determine the end dates of the previous four calendar quarters. Each time the report ran, it returned data for all four of those quarters.

I ended up using PIVOT in the course of the stored procedure for the report. The first time I did so, I hard-coded the four quarter end dates I needed to work with. This worked fine for my testing, but I needed the PIVOT to use whatever four quarters end dates were derived from the input parameter. I was fortunate that I knew ahead of time that I would always be pivoting on four and only four date values.

With that consistency in mind, I was able to create a common tables expression with a derived column to consistently “alias” the first quarter as 1, the second quarter as 2, and so forth.

Here is the breakdown of how this works using data on the dining habits of Hobbits. In this example, the report involved requires looking at the first three days of the month. This provides the consistency required to make this particular technique work. It could be possible to modify this procedure to work on value with less consistency but I have not yet tried it.

-- Create the Hobbit table to house the meal data

CREATE TABLE Hobbit

(

        Hobbit varchar(20)

      , MealDate date

      , MealCount int

)

 

-- Populate Hobbit table with meal data

INSERT INTO Hobbit VALUES('Bilbo', '2009-01-01', 4)

INSERT INTO Hobbit VALUES('Bilbo', '2009-01-02', 5)

INSERT INTO Hobbit VALUES('Bilbo', '2009-01-03', 3)

INSERT INTO Hobbit VALUES('Bilbo', '2009-02-01', 6)

INSERT INTO Hobbit VALUES('Bilbo', '2009-02-02', 7)

INSERT INTO Hobbit VALUES('Bilbo', '2009-02-03', 3)

INSERT INTO Hobbit VALUES('Samwise', '2009-01-01', 4)

INSERT INTO Hobbit VALUES('Samwise', '2009-01-02', 8)

INSERT INTO Hobbit VALUES('Samwise', '2009-01-03', 7)

INSERT INTO Hobbit VALUES('Samwise', '2009-02-01', 4)

INSERT INTO Hobbit VALUES('Samwise', '2009-02-02', 8)

INSERT INTO Hobbit VALUES('Samwise', '2009-02-03', 7)

INSERT INTO Hobbit VALUES('Meriadoc', '2009-01-01', 6)

INSERT INTO Hobbit VALUES('Meriadoc', '2009-01-02', 9)

INSERT INTO Hobbit VALUES('Meriadoc', '2009-01-03', 12)

INSERT INTO Hobbit VALUES('Meriadoc', '2009-02-01', 4)

INSERT INTO Hobbit VALUES('Meriadoc', '2009-02-02', 8)

INSERT INTO Hobbit VALUES('Meriadoc', '2009-02-03', 7)

 

-- Pivot with hard coded January values

SELECT

        Hobbit

      , [2009-01-01]

      , [2009-01-02]

      , [2009-01-03]

FROM 

(

SELECT 

        Hobbit

      , MealDate

      , MealCount

FROM Hobbit) h

PIVOT

(

SUM(MealCount)

FOR MealDate IN

(

        [2009-01-01]

      , [2009-01-02]

      , [2009-01-03]

)

) AS pvt

 

Results of January hard coded PIVOT:

 

Hobbit               2009-01-01  2009-01-02  2009-01-03

-------------------- ----------- ----------- -----------

Bilbo                4           5           3

Meriadoc             6           9           12

Samwise              4           8           7

 

-- Pivot with hard coded February values

SELECT

        Hobbit

      , [2009-02-01]

      , [2009-02-02]

      , [2009-02-03]

FROM 

(

SELECT 

        Hobbit

      , MealDate

      , MealCount

FROM Hobbit) h

PIVOT

(

SUM(MealCount)

FOR MealDate IN

(

        [2009-02-01]

      , [2009-02-02]

      , [2009-02-03]

)

) AS pvt

 

Results of February hard coded PIVOT:

 

Hobbit               2009-02-01  2009-02-02  2009-02-03

-------------------- ----------- ----------- -----------

Bilbo                6           7           3

Meriadoc             4           8           7

Samwise              4           8           7

 

As you can see from this pivot tables above, Hobbits eat often. The hard coded date values form the column headers. This is nice and all but requires a code change to report on a different set of dates.

Here we see how you can make a CTE with a derived column to pass in to the PIVOT operator. This allows the actual PIVOT to remain exactly the same while reporting on different dates.

-- Varbiable to hold the month number on which to PIVOT

DECLARE @MealMonth int

 

SET @MealMonth = 1 -- Using 1 to prepresent January

 

-- Common table expression to derive MealDay column

;

WITH HobbitMealDays AS

(

SELECT

        Hobbit

      , MealDate

      , DAY(MealDate) AS MealDay -- Using day datepart as integer value

      , MealCount

FROM Hobbit

WHERE MONTH(MealDate) = @MealMonth

)

 

SELECT

        Hobbit

      , [1] AS Day1 -- Alias to stand in for hard coded column headers

      , [2] AS Day2

      , [3] AS Day3

FROM 

(

SELECT 

        Hobbit

      , MealDay

      , MealCount

FROM HobbitMealDays) h

PIVOT

(

SUM(MealCount)

FOR MealDay IN

(

        [1] -- these values are known possible values of MealDay derived column

      , [2]

      , [3]

)

) AS pvt

 

Results of dynamic using @Mealmonth = 1 to represent January in PIVOT:

 

Hobbit               Day1        Day2        Day3

-------------------- ----------- ----------- -----------

Bilbo                4           5           3

Meriadoc             6           9           12

Samwise              4           8           7

 

Just to make the comparison easier, here are the hard coded January PIVOT results again.

Results of January hard coded PIVOT:

 

Hobbit               2009-01-01  2009-01-02  2009-01-03

-------------------- ----------- ----------- -----------

Bilbo                4           5           3

Meriadoc             6           9           12

Samwise              4           8           7

 

If you set @MealMonth = 2 and run the PIVOT again, you will get the February results:

Results of dynamic using @Mealmonth = 2 to represent February in PIVOT:

 

 

Hobbit               Day1        Day2        Day3

-------------------- ----------- ----------- -----------

Bilbo                6           7           3

Meriadoc             4           8           7

Samwise              4           8           7

 

Again, for ease of comparison, here are the hard coded February results again.

Results of February hard coded PIVOT:

 

Hobbit               2009-02-01  2009-02-02  2009-02-03

-------------------- ----------- ----------- -----------

Bilbo                6           7           3

Meriadoc             4           8           7

Samwise              4           8           7

 

-- Cleaning up

DROP TABLE Hobbit

GO

 

As you can see above, the “dynamic” PIVOT returns the exact same values as the hard coded versions but offers the flexibility of reporting on different periods without changing code. You could just reference a parameter in the CTE instead of a variable as I did.

Have fun.

Things You Know Now…

I was tagged by Jason Strate for cool blog chain. I guess I’m it now.

  

Get the Last Date in a Calendar Quarter

I needed to accurately calculate the end dates for the four most recently completed calendar quarters prior to the date provided. I found this snippet online and thought it was a really slick way to accomplish finding the quarter end date. I tried to find the site again so i could give props to the person who posted it but cannot. So, my clever friend, I salute thee.

Let’s start by declaring our date variable and setting it to a fine day in history. My parents told me this was my birthday. I don’t really remember but I will give them the benefit of the doubt.

I have dissected the expression below.

DECLARE @testdate date

 

SET @testdate = '1975-01-26'

 

SELECT

 

-- Get the number of calendar quarters since 0

        DATEDIFF(qq,0,@testdate) AS Step1

 

-- Add 1 quarter

      , DATEDIFF(qq,0,@testdate)+1 AS Step2

 

-- Add the number of quarters in Step2 above to 0 to get the earliest date of next quarter

      , DATEADD(qq, DATEDIFF(qq,0,@testdate)+1,0) AS Step3

 

-- Subtract 3 milliseconds to get to the last datetime value of current quarter

      , dateadd(ms,-3,DATEADD(qq, DATEDIFF(qq,0,@testdate)+1,0)) AS Step4

 

-- Since i was working in SQL 2008, which supports separate data types for DATE and TIME, 

-- I converted the result in Step4 to DATE

      , CONVERT(date,dateadd(ms,-3,DATEADD(qq, DATEDIFF(qq,0,@testdate)+1,0))) AS QuarterEndDate

The query results are as follows:

Step1       Step2       Step3                   Step4                   QuarterEndDate

----------- ----------- ----------------------- ----------------------- --------------

300         301         1975-04-01 00:00:00.000 1975-03-31 23:59:59.997 1975-03-31

 

So, with this nifty expression, we have determined the end date of the quarter for my birthday (no gifts necessary).

We can also change the number of quarters added (or even subtracted) in Step2 to get the quarter end date for future or past quarters, too.

Make room for FLOOR and CEILING

On the same report referenced above, I also had to implement a business rule by rounding a value up to the nearest thousand. A colleague suggested the CEILING function, which returns the smallest integer that is greater than or equal to the provided numeric expression. As it turn out, the FLOOR function does the exact opposite, returning the largest integer that is less than or equal to the numeric expression.

Let’s look at both functions in action.

-- Let's create a tiny table

CREATE TABLE Smurf

(

        Smurf varchar(10)

      , HeightInApples dec(2,1)

)

 

-- Populate the table with a small number of records

INSERT INTO Smurf VALUES('Brainy',3.1)

INSERT INTO Smurf VALUES('Hefty',3.5)

INSERT INTO Smurf VALUES('Painter', 3.7)

 

-- Watch these smurfy functions do their thing

SELECT

        Smurf

      , HeightInApples

      , FLOOR(HeightInApples) AS FloorValue

      , CEILING(HeightInApples) AS CeilingValue

FROM Smurf

 

Results:

 

Smurf      HeightInApples    FloorValue  CeilingValue

---------- ----------------- ----------- -------------

Brainy     3.1               3           4

Hefty      3.5               3           4

Painter    3.7               3           4

 

-- Cleaning up after yourself is smurftastic

DROP TABLE Smurf

Notice that all values were rounded down by the FLOOR function and up by the CEILING function.

For my report, I divided my input values by 1000, ran them through CEILING, and the multiplied by 1000. This rounded my values up to the nearest thousand. I just figured the example above would be a little more fun.

I am tagging Dan English

The Coolness of the T-SQL NTILE Function

I had need of calculating metrics across an industry and then returning the quartile in which each company scored. So, i wrote up a function that took in the MinValue, MaxValue, and CompanyMetricValue as inputs and returned the quartile for the Company. Great. Then, when asking  someone else about this using this new function, the response was, “Why not just use NTILE?” Ug.

Here is the description from SQL 2008 Books Online:

Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row,  NTILE returns the number of the group to which the row belongs.

NTILE (integer_expression)    OVER ( [ <partition_by_clause> ] < order_by_clause > )

So, using NTILE, I was able to do the following:

SELECT NTILE(4) OVER(ORDER BY CompanyMetricValue ASC) AS Quartile

In the example above, i want quarters, so I use 4 as the (integer expression).

Lesson Learned: Before inventing a new wheel, check Books Online for wheels that already exist. 🙂