Guidance for assigning sql variables collected from InputForm control.

Questions
, , ,
1

Good Day,

I have a database that tracks support tickets. I have a SQL query that shows data over a specified date range. I’m hoping to use Mathematica to report on this data during weekly meetings. Below is the query…

DECLARE @StartTime DATEDECLARE @EndTime DATESET @StartTime = '2013-12-01’SET @EndTime = '2013-12-07’SELECT DISTINCT fdt.Agent, SUM(fdt.MinutesWorked) Over(Partition By fdt.agent) AS MinutesWorked, COUNT(TicketID) Over(Partition By fdt.agent) AS ClosedTicketCount, SUM(fdt.MinutesWorked) Over (Partition By fdt.agent) / COUNT(TicketID) Over(Partition By fdt.agent) AS AverageWorkTimeFROM TicketInformation fdtWHERE fdt.ClosedTime BETWEEN @StartTime AND @EndTimeGroup By
    fdt.Agent,fdt.MinutesWorked,fdt.TicketID

Is there a way I can use the InputForm control to assign date values to @StartTime and @EndTime? If not, is there a better way to do what I am looking to do?

Thanks,
Joey Drews

2

First your will need the DatabaseLink package. The package uses Java to make db connection so many of the Java (JDBC) prepared statement argument passing methods can be used such as “?”

Needs[“DatabaseLink`”]
You may want to figure out what JDBC drivers are already installed in your system.
JDBCDriverNames[]
Depending on how you wish to connect you can use datasource name (DSN) JDBCDrivers[“ODBC(DSN)”]
or connect to MSSQL using JDBCDrivers[“Microsoft SQL Server(jTDS)”]

yourSQLconnection = OpenSQLConnection[You need to work out this part",possible username, password issues]

Just using OpenSQLConnection[] will bring up a database connection wizard which you may wish to walk through once.

After you have solved your database connection issues; you can try this:

SQLExcecute[yourSQLconnection,
“SELECT DISTINCT fdt.Agent,
                                          SUM(fdt.MinutesWorked) OVER(PARTITION BY fdt.agent) AS MinutesWorked,
                                          COUNT(TicketID) OVER(PARTITION BY fdt.agent) AS ClosedTicketCount,
                                  SUM(fdt.MinutesWorked) OVER (PARTITION BY fdt.agent) / COUNT(TicketID) OVER(PARTITION BY fdt.agent) AS AverageWorkTime
FROM TicketInformation fdt
WHERE fdt.ClosedTime BETWEEN `1` AND `2`
GROUP BY fdt.Agent, fdt.MinutesWorked,fdt.TicketID”,
{
    SQLDateTime[{2013, 12, 01}],
    SQLDateTime[{2013, 12, 07}]
}
]

Or better yet create a stored procedure on the MSSQL database

CREATE PROCEDURE usp_GetTicketsByDate ( @StartTime AS DATETIME,
@EndTime AS DATETIME
)
AS
SELECT DISTINCT fdt.Agent,
                                         SUM(fdt.MinutesWorked) OVER(PARTITION BY fdt.agent) AS MinutesWorked,
                                  COUNT(TicketID) OVER(PARTITION BY fdt.agent) AS ClosedTicketCount,
                                   SUM(fdt.MinutesWorked) OVER (PARTITION BY fdt.agent) / COUNT(TicketID) OVER(PARTITION BY fdt.agent) AS AverageWorkTime
FROM TicketInformation fdt
WHERE fdt.ClosedTime BETWEEN @StartTime AND @EndTime
GROUP BY fdt.Agent, fdt.MinutesWorked,fdt.TicketID

and execute that instead of dynamically prepared SQL statement.

SQLExcecute[yourSQLconnection,
“EXECUTE dbo.usp_GetTicketsByDate `1`, `2`”,
{
    SQLDateTime[{2013, 12, 01}],
    SQLDateTime[{2013, 12, 07}]
}
]

Then close your connection
CloseSQLConnection
Not tested you asked for a place to start. You may want to use the DSN connection route.