Migrating from Access to SQL Server - Pivot table help needed

Transact-SQL
1

Hello All,

I've been tasked with migrating an internally developed ASP.NET tool from Access to SQL Server. This sort of activity isn't my day job, but I'm 99% there thanks to a few books and research here. I have one query left to convert, and I cannot begin to wrap my brain around it.

The output displays a list of employees currently taking leads (who are not "inactive"). For the column headers, the date is shown for the previous seven days (if a lead was submitted on that day), and two totals are displayed under each date. One for the total number of New Orders received, and another for total number of Change Orders. It seems that there should be a better way to present this rather than a /, such as a split column under each date heading, however, I'd be overjoyed just to be able to reproduce what was done in Access.

Access Query that I need to convert to T-SQL:

TRANSFORM Sum([T_Leads]![OrderType]='New Order')-1 & " / " & Sum([T_Leads]![OrderType]='Change Order')-1 AS [New / Change]
SELECT Employees.EmployeeName as Name, Count(T_Leads.OrderType) AS Total
FROM Employees INNER JOIN T_Leads ON Employees.EmployeeID = T_Leads.EmployeeID
WHERE (((T_Leads.Date)>Date()-7))
and [Employees.LeadRotation] <> "Inactive"
GROUP BY Employees.EmployeeName
ORDER BY T_Leads.Date
PIVOT T_Leads.Date;

Produces this output:

+-------------+-------+----------+----------+----------+----------+-----------+
| Name        | Total | 4/5/2016 | 4/6/2016 | 4/7/2016 | 4/8/2016 | 4/11/2016 |
+-------------+-------+----------+----------+----------+----------+-----------+
| Doe, Jane   |  9    | 0/1      | 0/2      | 0/3      | /        | 0/3       |
+-------------+-------+----------+----------+----------+----------+-----------+
| Guy, Some   | 4     | 0/1      | 0/1      | /        | /        | 0/2       |
+-------------+-------+----------+----------+----------+----------+-----------+
| Doe, John   | 10    | 0/1      | 1/1      | 2/1      | 0/3      | 0/1       |
+-------------+-------+----------+----------+----------+----------+-----------+
| Boop, Betty | 1     | 0/1      | /        | /        | /        | /         |
+-------------+-------+----------+----------+----------+----------+-----------+
| Smith, Jeff | 7     | 1/1      | 0/1      | 0/1      | /        | 0/3       |
+-------------+-------+----------+----------+----------+----------+-----------+

Many thanks!

2

Try this sample query may it help yours

 SELECT *
FROM
(
  SELECT 
    CASE 
      WHEN sumUnits > 0 
      THEN SumAvgRent / sumUnits ELSE 0 
  END AS Expr1,
  Description,
  Period
  FROM temp
) t
PIVOT
(
  AVG(Expr1)
  FOR Period IN(Period1, Period2, Period3)
) p;
3

My knowledge of SQL is still remedial at best, unfortunately. I'm trying. I've studied your example, but I'm struggling to relate it to the query I need to convert.

I know that I can use the following to get the total number of Change Orders and New Orders for a given employee

SUM(CASE WHEN T_Leads.OrderType = 'New Order' THEN 1 ELSE 0 END) as [New],SUM(CASE WHEN T_Leads.OrderType='Change Order' THEN 1 ELSE 0 END) as [Change]

My knowledge falls apart after that. I can't make sense of the PIVOT function, or how to display two different values (New/Change) for each date. I haven't been able to find any examples of that.