Convert SQL Server Query to MySQL

[stanl]

(Jim probably)

I am trying to assess dialing levels by time of day (Time is EST but calls can be made across entire USA) - but pivoting to only show numbers that were dialed multiple times on a given day [output is Excel].  The actual pseudo-CDR tables are in MySQL, but for backup they are aggregated to SQL Server:  Below is code I call from a compiled WB script based on the SQL Server tables. |t1| is a variable replaced with a data from a date picker.  SQL Server has a Pivot function starting with 2005 and the Over(Partition...) was added to 2012.  This works fine for previous data but would like to get a more real-time crosstab, and therefore query MySQL Directly. I have MySQL code that can obtain all dials and the hour they were dialed in, but pivoting it is challenging.  On average the data has over 40,000 daily dials, and up to 70 purposes for the call.

Code Select Expand


;With CTE as ( SELECT [Date]
         ,DATEPART(hh,DateTime) as HR
         ,[Purpose]
         ,[Dialed]
         ,Count(Dialed) Over(Partition By [Date],[Dialed]) Total_CT
  FROM [Data].[Dial].[Calls] with(nolock)
  WHERE [Date]='|t1|'
  and DATEPART(hh,DateTime) between 9 and 22

  ),

CTE2 as (
  Select [Date]
  ,[Purpose]
  ,[Dialed]
  ,HR
  From CTE
  Where Total_CT > 1

)

Select [Date]
  ,[Purpose]
  ,[Dialed]
   ,[9] AS [9 AM]
  ,[10] AS [10 AM]
  ,[11] AS [11 AM]
  ,[12] AS [Noon]
  ,[13] AS [1 PM]
  ,[14] AS [2 PM]
  ,[15] AS [3 PM]
  ,[16] AS [4 PM]
  ,[17] AS [5 PM]
  ,[18] AS [6 PM]
  ,[19] AS [7 PM]
  ,[20] AS [8 PM]
  ,[21] AS [9 PM]
  ,[22] AS [10 PM]
  From CTE2
  Pivot (

       Count(HR) FOR HR in ([9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22])

  ) as pvt


[JTaylor]

Just to make sure I understand what this query returns...It is returning a list of the numbers dialed, along with the other columns of course, with the last one being a count of how many times the number was dialed, correct?  I haven't used the Over() function but think I understand from the documentation.  If so, how is this query different from using GROUP BY?   If not, please explain a bit more about what you need as the end result.   From what you have said it sounds like you want a count of dials grouped by the hour but only for those numbers that were dialed multiple times.    The problem is I'm not seeing how the query listed will produce that information, unless you are doing the rest in Excel, so am guessing I am missing something.

Sorry if I am being dense.

Jim

[stanl]

GROUP BY (in this instance) would be expensive. I have attached an snapshot of fake data but how it comes our from the query. It goes into Excel like that [CopyfromRecordset from the WB script] and then the Excel Pivot and Slicer functions are used to further break down data and make it pretty.

As I said, this is SQL Server 2012, and is handled nicely in-memory. When I looked into converting to MySQL, there seemed to be no native pivot and the examples I looked at created 2 or more temp tables then used prepared statements to process the rest. So basically I want the same output from the SQL Server but with MySQL.

[JTaylor]

Okay.  That is helpful and different than what I was expecting.   

Hmmmmmmmmmm....let me ponder it a bit.   I am not aware of anything that clean in MySQL and my earlier searching showed ways of doing a bit of what you want but I think in only one column and not splitting it over the times you list.   I can think of one way to do this but not sure how costly in resources it would be when running.  Let me do a bit more research and testing and I'll post something.

Jim

[JTaylor]

Here is one approach, assuming you have the ability to create "Views".  I can think of another way but it wouldn't be very pretty and I am not sure of the costs while running.   If this isn't helpful let me know and I'll make another suggestion.  This is clean and reasonably straightforward if you have the rights to implement.

http://stratosprovatopoulos.com/web-development/mysql/pivot-a-table-in-mysql/

Jim

[stanl]

That was the first article I looked at. I can create views in the MySQL Workbench, not sure about ADO and a WB script

[JTaylor]

Yes.  It creates the Views just fine via WB.  I assume once they are in place you wouldn't need to change them very often.   Not sure on passing variables to them though since you would be querying the last one.  Guess the first one may need to be dropped/created to change dates and such.

Jim