Convert Code block to a pivot display

Working on converting this to display as a pivot.

WITH Hours
     AS (
     SELECT 0 AS hour
     UNION ALL
     SELECT hour + 1
     FROM Hours
     WHERE hour + 1 < 24)
     SELECT CONVERT(VARCHAR, h.hour) + ' - ' + CASE
                                               WHEN h.hour + 1 = 24
                                               THEN 'Midnight'
                                               ELSE CONVERT(VARCHAR, h.hour + 1)
                                           END+CASE
                                                   WHEN h.Hour >= 12
                                                   THEN ' PM'
                                                   ELSE ' AM'
                                               END as 'Hour',
            --convert(varchar, ccc.Agency) + ' - ' +
			  a.DepartmentTitle as 'Department'
     FROM  Agency a, Hours h
          LEFT JOIN ClosedCallControl ccc ON DATEPART(HH, ccc.InitiationTime) BETWEEN h.hour AND h.hour + 1
                                           AND ccc.InitiationTime BETWEEN '01-01-2018 00:00:00' AND '01-01-2018 23:59:59'
										   order by Department, Hour

Open in new window

Results.pngTimes-for-Agency.png
MikeM670Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MikeM670Author Commented:
This is what I have so far but am getting a syntax error

Msg 102, Level 15, State 1, Line 50
Incorrect syntax near ','.

--a.DepartmentTitle is full text name of agency
-- a.agencyid is department id number
--- ccc.agency is department id number

select
isnull(a.DepartmentTitle, 'TOTAL') Department,
SUM(isnull([0 - 1 AM], 0)) AS [0 - 1 AM],
SUM(isnull([1 - 2 AM], 0)) AS [1 - 2 AM],
SUM(isnull([2 - 3 AM], 0)) AS [2 - 3 AM],
SUM(isnull([3 - 4 AM], 0)) AS [3 - 4 AM],
SUM(isnull([4 - 5 AM], 0)) AS [4 - 5 AM],
SUM(isnull([5- 6 AM], 0)) AS [5 - 6 AM],
SUM(isnull([6 - 7 AM], 0)) AS [6 - 7 AM],
SUM(isnull([7 - 8 AM], 0)) AS [7 - 8 AM],
SUM(isnull([8 - 9 AM], 0)) AS [8 - 9 AM],
SUM(isnull([9 - 10 AM], 0)) AS [9 - 10 AM],
SUM(isnull([11 - 12 PM], 0)) AS [11 - 12 AM],
SUM(isnull([12 - 13 PM], 0)) AS [12 - 13 PM],
SUM(isnull([13 - 14 PM], 0)) AS [13 - 14 PM],
SUM(isnull([14 - 15 PM], 0)) AS [14 - 15 PM],
SUM(isnull([15 - 16 PM], 0)) AS [15 - 16 PM],
SUM(isnull([16 - 17 PM], 0)) AS [16 - 17 PM],
SUM(isnull([17 - 18 PM], 0)) AS [17 - 18 PM],
SUM(isnull([18 - 19 PM], 0)) AS [18 - 19 PM],
SUM(isnull([19 - 20 PM], 0)) AS [19 - 20 PM],
SUM(isnull([20 - 21 PM], 0)) AS [20 - 21 PM],
SUM(isnull([21 - 22 PM], 0)) AS [21 - 22 PM],
SUM(isnull([22 - 23 PM], 0)) AS [12 - 23 PM],
SUM(isnull([23 - Midnight PM], 0)) AS [23 - Midnight PM],
SUM(isnull([0 - 1 AM], 0) + isnull([1 - 2 AM], 0) + isnull([2 - 3 AM], 0) + isnull([3 - 4 AM], 0)
 + isnull([4 - 5 AM], 0) + isnull([5 - 6 AM], 0) + isnull([6 - 7 AM], 0) + isnull([7 - 8 AM], 0)
 + isnull([8 - 9 AM], 0) + isnull([9 - 10 AM], 0) + isnull([10 - 11 AM], 0) + isnull([11- 12 AM], 0)
 + isnull([12 -13 PM], 0) + isnull([13 - 14 PM], 0) + isnull([14 - 15 PM], 0) + isnull([15 - 16 PM], 0)
 + isnull([16 - 17 PM], 0) + isnull([17 - 18 PM], 0) + isnull([18 - 19 PM], 0) + isnull([19 -20 PM], 0)
 + isnull([20 - 21 PM], 0) + isnull([21 - 22 PM], 0) + isnull([22 - 23 PM], 0) + isnull([12 - 23 PM], 0) ) AS Total

from ClosedCallControl ccc, Agency a;
WITH Hours
     AS (
     SELECT 0 AS hour
     UNION ALL
     SELECT hour + 1
     FROM Hours
     WHERE hour + 1 < 24)
     SELECT CONVERT(VARCHAR, h.hour) + ' - ' + CASE
                                               WHEN h.hour + 1 = 24
                                               THEN 'Midnight'
                                               ELSE CONVERT(VARCHAR, h.hour + 1)
                                           END+CASE
                                                   WHEN h.Hour >= 12
                                                   THEN ' PM'
                                                   ELSE ' AM'
                                               END as 'Hour',
            --convert(varchar, ccc.Agency) + ' - ' +
			  a.DepartmentTitle as 'Department'
     FROM ( Agency a, Hours h
          LEFT JOIN ClosedCallControl ccc ON DATEPART(HH, ccc.InitiationTime) BETWEEN h.hour AND h.hour + 1
                                           AND ccc.InitiationTime BETWEEN '01-01-2018 00:00:00' AND '01-01-2018 23:59:59'
										   and ccc.agency = a.AgencyID
										  )

										   pivot(sum([a.departmenttitle) for h.hour in ([0 - 1 AM],
																						[1 - 2 AM],
																						[2 - 3 AM],
																						[3 - 4 AM],
																						[4 - 5 AM],
																						[6 - 7 AM],
																						[7 - 8 AM],
																						[8 - 9 AM],
																						[9 - 10 AM],
																						[10 - 11 AM],
																						[11 - 12 AM],
																						[12 - 13 PM],
																						[14 - 15 PM],
																						[15 - 16 PM],
																						[16 - 17 PM],
																						[17 - 18 PM],
																						[18 - 19 PM],
																						[19 - 20 PM],
																						[20 - 21 PM],
																						[21 - 22 PM],
																						[22 - 23 PM],
																						[23 - Midnight PM])) as DepartmentPivot
																							order by Department, Hour

Open in new window


Edited the code to correct some time references and add a total for the row.
PortletPaulEE Topic AdvisorCommented:
>>"AND ccc.InitiationTime BETWEEN '01-01-2018 00:00:00' AND '01-01-2018 23:59:59'"

When you are dealing with date/time information that is precise below 1 second 23:59:59 is NOT the end of a day, it is 1 full second short of that. Data could fall into that second and your query will simply ignore that data.

This is a far more precise way to define a date range:

          AND ccc.InitiationTime >= '01-01-2018' AND ccc.InitiationTime < '01-02-2018'

and YYYYMMDD date literals are the safest to use in T-SQL, so:

          AND ccc.InitiationTime >= '20180101' AND ccc.InitiationTime < '20180201'

note how the second date reference is now "the next day" (i.e. anything less than the first of the next month in this particular query)

For more on this topic see: https://www.experts-exchange.com/articles/11210/Beware-of-Between.html

For your query I suggest you just use "conditional aggregates" to achieve the wanted layout, it's a lot simpler I feel:
SELECT
      a.DepartmentTitle AS 'Department'
    , COALESCE(COUNT(CASE WHEN DATEPART(HH, ccc.InitiationTime) = 0  THEN 1 END),0) AS [0 - 1AM]
    , COALESCE(COUNT(CASE WHEN DATEPART(HH, ccc.InitiationTime) = 1  THEN 1 END),0) AS [1 - 2AM]
    , COALESCE(COUNT(CASE WHEN DATEPART(HH, ccc.InitiationTime) = 2  THEN 1 END),0) AS [2 - 3AM]
    , COALESCE(COUNT(CASE WHEN DATEPART(HH, ccc.InitiationTime) = 3  THEN 1 END),0) AS [3 - 4AM]
    , COALESCE(COUNT(CASE WHEN DATEPART(HH, ccc.InitiationTime) = 4  THEN 1 END),0) AS [4 - 5AM]
    , COALESCE(COUNT(CASE WHEN DATEPART(HH, ccc.InitiationTime) = 5  THEN 1 END),0) AS [5 - 6AM]
    , COALESCE(COUNT(CASE WHEN DATEPART(HH, ccc.InitiationTime) = 6  THEN 1 END),0) AS [6 - 7AM]
    , COALESCE(COUNT(CASE WHEN DATEPART(HH, ccc.InitiationTime) = 7  THEN 1 END),0) AS [7 - 8AM]
    , COALESCE(COUNT(CASE WHEN DATEPART(HH, ccc.InitiationTime) = 8  THEN 1 END),0) AS [8 - 9AM]
    , COALESCE(COUNT(CASE WHEN DATEPART(HH, ccc.InitiationTime) = 9  THEN 1 END),0) AS [9 - 10AM]
    , COALESCE(COUNT(CASE WHEN DATEPART(HH, ccc.InitiationTime) = 10 THEN 1 END),0) AS [10 - 11AM]
    , COALESCE(COUNT(CASE WHEN DATEPART(HH, ccc.InitiationTime) = 11 THEN 1 END),0) AS [11 - 12AM]
    , COALESCE(COUNT(CASE WHEN DATEPART(HH, ccc.InitiationTime) = 12 THEN 1 END),0) AS [12 - 13PM]
    , COALESCE(COUNT(CASE WHEN DATEPART(HH, ccc.InitiationTime) = 13 THEN 1 END),0) AS [13 - 14PM]
    , COALESCE(COUNT(CASE WHEN DATEPART(HH, ccc.InitiationTime) = 14 THEN 1 END),0) AS [14 - 15PM]
    , COALESCE(COUNT(CASE WHEN DATEPART(HH, ccc.InitiationTime) = 15 THEN 1 END),0) AS [15 - 16PM]
    , COALESCE(COUNT(CASE WHEN DATEPART(HH, ccc.InitiationTime) = 16 THEN 1 END),0) AS [16 - 17PM]
    , COALESCE(COUNT(CASE WHEN DATEPART(HH, ccc.InitiationTime) = 17 THEN 1 END),0) AS [17 - 18PM]
    , COALESCE(COUNT(CASE WHEN DATEPART(HH, ccc.InitiationTime) = 18 THEN 1 END),0) AS [18 - 19PM]
    , COALESCE(COUNT(CASE WHEN DATEPART(HH, ccc.InitiationTime) = 19 THEN 1 END),0) AS [19 - 20PM]
    , COALESCE(COUNT(CASE WHEN DATEPART(HH, ccc.InitiationTime) = 20 THEN 1 END),0) AS [20 - 21PM]
    , COALESCE(COUNT(CASE WHEN DATEPART(HH, ccc.InitiationTime) = 21 THEN 1 END),0) AS [21 - 22PM]
    , COALESCE(COUNT(CASE WHEN DATEPART(HH, ccc.InitiationTime) = 22 THEN 1 END),0) AS [22 - 23PM]
    , COALESCE(COUNT(CASE WHEN DATEPART(HH, ccc.InitiationTime) = 23 THEN 1 END),0) AS [23 - Midnight PM]
    , COUNT(*) as 'Total'
FROM Agency a
LEFT JOIN ClosedCallControl ccc on ccc.agency = a.AgencyID
              AND ccc.InitiationTime >= '20180101' AND ccc.InitiationTime < '20180201'
GROUP BY
      a.DepartmentTitle AS 'Department'
;

Open in new window

By the way, whilst I see may use SUM() when they want to count I strongly prefer to use COUNT() because this function ignores NULLs i.e. if there is no data to meet a condition it will not count it.
Mark WillsTopic AdvisorCommented:
OK, a few problems with the structure

do your CTE first For example
;with CTE_Hours as
( <do the hours> and cross join with distinct departments>
) select <result set columns>
  from  
  ( select <raw data>
    from cte_hours c
    left outer join Agency a on c.department = a.department and hours..........and.... Where....) src
  PIVOT
 ( aggregation for src.column in (<result columns>)) pvt

Open in new window

Will post a full response in a short while.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Mark WillsTopic AdvisorCommented:
Now contrary to what I said above, this time you dont actually need a cte (and often you wont)

Because the Hours are going to be controlled by the list of columns in <result columns> so no need to seed those values...

In which case all you need is datepart(hour,InitiationTime) and then a list of hours in <result columns>

Also noticed that you are SUM the individual columns, which means there needs to be an aggregation on department - and assume you want column totals - hence the isnull(department,'TOTAL')

Have a look at
select isnull(Department,'TOTAL') as Department,
       SUM(isnull([0], 0)) AS [0 - 1 AM],
       SUM(isnull([1], 0)) AS [1 - 2 AM],
       SUM(isnull([2], 0)) AS [2 - 3 AM],
       SUM(isnull([3], 0)) AS [3 - 4 AM],
       SUM(isnull([4], 0)) AS [4 - 5 AM],
       SUM(isnull([5], 0)) AS [5 - 6 AM],
       SUM(isnull([6], 0)) AS [6 - 7 AM],
       SUM(isnull([7], 0)) AS [7 - 8 AM],
       SUM(isnull([8], 0)) AS [8 - 9 AM],
       SUM(isnull([9], 0)) AS [9 - 10 AM],
       SUM(isnull([11], 0)) AS [11 - 12 AM],
       SUM(isnull([12], 0)) AS [12 - 13 PM],
       SUM(isnull([13], 0)) AS [13 - 14 PM],
       SUM(isnull([14], 0)) AS [14 - 15 PM],
       SUM(isnull([15], 0)) AS [15 - 16 PM],
       SUM(isnull([16], 0)) AS [16 - 17 PM],
       SUM(isnull([17], 0)) AS [17 - 18 PM],
       SUM(isnull([18], 0)) AS [18 - 19 PM],
       SUM(isnull([19], 0)) AS [19 - 20 PM],
       SUM(isnull([20], 0)) AS [20 - 21 PM],
       SUM(isnull([21], 0)) AS [21 - 22 PM],
       SUM(isnull([22], 0)) AS [12 - 23 PM],
       SUM(isnull([23], 0)) AS [23 - Midnight PM],
       SUM(isnull([0], 0) + isnull([1], 0) + isnull([2], 0) + isnull([3], 0) + isnull([4], 0) + isnull([5], 0) + isnull([6], 0) + isnull([7], 0) + isnull([8], 0) 
         + isnull([9], 0) + isnull([10], 0) + isnull([11], 0) + isnull([12], 0) + isnull([13], 0) + isnull([14], 0) + isnull([15], 0) + isnull([16], 0) + isnull([17], 0)
         + isnull([18], 0) + isnull([19], 0) + isnull([20], 0) + isnull([21], 0) + isnull([22], 0) + isnull([23], 0) ) AS Total
from
( select a.department, datepart(hour,c.InitiationTime) as HH, 1 as [Counter]
  from ClosedCallControl c
  inner join agency a on a.agency = c.agency
  where c.InitiationTime >= '20180101'
  and c.InitiationTime < '20180102' ) src
PIVOT
( sum(counter) for HH in ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])) pvt

GROUP BY ROLLUP(department)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MikeM670Author Commented:
Mark,

I made a few edits to your code example.

I changed:
 select isnull(Department,'TOTAL') as Department,  to select isnull(DepartmentTitle,'TOTAL') as Department,

Open in new window


( select a.department, datepart(hour,c.InitiationTime) as HH, 1 as [Counter] to ( select a.DepartmentTitle, datepart(hour,c.InitiationTime) as HH, 1 as [Counter]

Open in new window


GROUP BY ROLLUP(Department to GROUP BY ROLLUP(DepartmentTitle

Open in new window


select isnull(DepartmentTitle,'TOTAL') as Department,
       SUM(isnull([0], 0)) AS [0 - 1 AM],
       SUM(isnull([1], 0)) AS [1 - 2 AM],
       SUM(isnull([2], 0)) AS [2 - 3 AM],
       SUM(isnull([3], 0)) AS [3 - 4 AM],
       SUM(isnull([4], 0)) AS [4 - 5 AM],
       SUM(isnull([5], 0)) AS [5 - 6 AM],
       SUM(isnull([6], 0)) AS [6 - 7 AM],
       SUM(isnull([7], 0)) AS [7 - 8 AM],
       SUM(isnull([8], 0)) AS [8 - 9 AM],
       SUM(isnull([9], 0)) AS [9 - 10 AM],
       SUM(isnull([11], 0)) AS [11 - 12 AM],
       SUM(isnull([12], 0)) AS [12 - 13 PM],
       SUM(isnull([13], 0)) AS [13 - 14 PM],
       SUM(isnull([14], 0)) AS [14 - 15 PM],
       SUM(isnull([15], 0)) AS [15 - 16 PM],
       SUM(isnull([16], 0)) AS [16 - 17 PM],
       SUM(isnull([17], 0)) AS [17 - 18 PM],
       SUM(isnull([18], 0)) AS [18 - 19 PM],
       SUM(isnull([19], 0)) AS [19 - 20 PM],
       SUM(isnull([20], 0)) AS [20 - 21 PM],
       SUM(isnull([21], 0)) AS [21 - 22 PM],
       SUM(isnull([22], 0)) AS [12 - 23 PM],
       SUM(isnull([23], 0)) AS [23 - Midnight PM],
       SUM(isnull([0], 0) + isnull([1], 0) + isnull([2], 0) + isnull([3], 0) + isnull([4], 0) + isnull([5], 0) + isnull([6], 0) + isnull([7], 0) + isnull([8], 0) 
         + isnull([9], 0) + isnull([10], 0) + isnull([11], 0) + isnull([12], 0) + isnull([13], 0) + isnull([14], 0) + isnull([15], 0) + isnull([16], 0) + isnull([17], 0)
         + isnull([18], 0) + isnull([19], 0) + isnull([20], 0) + isnull([21], 0) + isnull([22], 0) + isnull([23], 0) ) AS Total
from
( select a.DepartmentTitle, datepart(hour,c.InitiationTime) as HH, 1 as [Counter]
  from ClosedCallControl c
  inner join agency a on a.agencyid = c.agency
  where c.InitiationTime >= '20180101'
  and c.InitiationTime < '20180102' ) src
PIVOT
( sum(counter) for HH in ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])) pvt

GROUP BY ROLLUP(DepartmentTitle)

Open in new window

MikeM670Author Commented:
My next step is to edit the date range to use parameter fields and add a day to the EndDate.   The users run these queries via a web application on their desktop using a datepicker and it would be confusing to them to have them add a date to their query.   Before I was just using the between and could just plug in the parameters:
'[PSIMSQQ Start_Date(Format: mm/dd/YYYY)] 00:00:00' AND '[PSIMSQQ End_Date(Format: mm/dd/YYYY)] 23:59:59'

Open in new window


Now I need to add a day to the '[PSIMSQQ End_Date(Format: mm/dd/YYYY)]'
MikeM670Author Commented:
Here is what I have and it works within the application:

  
  where c.InitiationTime >= '[PSIMSQQ End_Date(Format: mm/dd/YYYY)]'
  and   c.InitiationTime < Dateadd(Day, 1,'[PSIMSQQ End_Date(Format: mm/dd/YYYY)]')

Open in new window

Mark WillsTopic AdvisorCommented:
Looks good - well done...

Sorry about missing DepartmentTitle, but looks like you have it nailed.

And your params also look good.

The important thing is either cast(c.InitiationTime as date) and use =
or (as you have done)
Make the end date range 1 greater and test for less than. Which (as Paul pointed out "beware of between") is a better approach.

Well done :)
MikeM670Author Commented:
Thanks to both of you for the assistance.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.