Message Boards Message Boards

0
|
12658 Views
|
1 Reply
|
0 Total Likes
View groups...
Share
Share this post:

Guidance for assigning sql variables collected from InputForm control.

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
POSTED BY: Joey Drews
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[yourSQLconnection]
Not tested you asked for a place to start. You may want to use the DSN connection route.
POSTED BY: Hans Michel
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
Attachments
Remove
or Discard

Group Abstract Group Abstract