#wennsoft

LLR Partners Invests in WennSoft

New Berlin, Wis. (PRWEB) April 16, 2015

LLR Partners, a private equity firm investing in middle market growth companies, announced a strategic investment in WennSoft Inc., an innovative field services software provider, to drive a market leadership position in the field service and specialty construction markets worldwide.

“WennSoft has an impressive, 20-year track record of delivering proven…

This post is to provide some follow up information to the following LinkedIn question (here): 

I am writing a query for an SSRS report and have run into a roadblock. I need to pull all of the service calls from a specific contract. I am pulling them from the most recent contract (the one with the most recent WSCONTSQ number from the SV00500 table). When I use that query as a dataset with multi valued parameters, it throws an error. When I enter just one contract in the parameter, it works fine. It only aborts processing when there are multiple contracts to look across. My dataset query is as follows: SELECT Type_Call_Short, Contract_Number, Call_Time, Service_Call_ID, ADRSCODE, CUSTNMBR FROM SV00300 WHERE Contract_Number = @Contract_Number AND Type_Call_Short = 'MC' and WSCONTSQ = (select WSCONTSQ from SV00500 where Contract_Number = @Contract_Number)

The problem occurs because the "SELECT WSCONTSQ FROM SV00500 WHERE Contract_Number = @Contract_Number" sub query could return multiple records, which cannot be used with the "=" clause.  You would have to use MAX in the subquery in order to ensure only a single record would return like this:

SELECT sc.Type_Call_Short, sc.Contract_Number, sc.Call_Time, sc.Service_Call_ID, sc.ADRSCODE, sc.CUSTNMBR FROM dbo.SV00300 sc WHERE Type_Call_Short IN ('MC','MCC') AND Contract_Number = @Contract_Number AND WSCONTSQ = (SELECT MAX(WSCONTSQ) FROM dbo.SV00500 WHERE Contract_Number = @Contract_Number)

This would solve the problem because the subquery would only return a single WSCONTSQ.

A couple of thoughts around this query:

1. Contract_Number is not a strong enough link between the SV00300 (Service Call) and SV00500 (Open Maintenance Contracts) tables.  You would be better off using:  CUSTNMBR, ADRSCODE, Contract_Number, and WSCONTSQ to ensure a solid join.  Keep in mind if Contract_Number is ALWAYS unique what is above shouldn't be a problem...just know your dataset.

2. Make sure to include "MC" and "MCC":  both are related to a Maintenance contract.  "MCC" calls are dropped by WennSoft automatically based on a schedule.  "MC" calls are created by users.

3. I would leverage a common table expression (cte) and determine the MAX(WSCONTSQ) from the SV00500 and then join onto the SV00300 since Contract_Number is indexed in the SV00500 and is typically a smaller table.

4. I would recommend adding the "dbo." prefix before your table names so the execution plan doesn't have to figure it out.

Factoring all these in and assuming that you have unique Contract Numbers I would have written the query like this:

;WITH cteContract AS ( SELECT CUSTNMBR, ADRSCODE, Contract_Number, MAX(WSCONTSQ) WSCONTSQ FROM dbo.SV00500 WHERE Contract_Number = @Contract_Number GROUP BY CUSTNMBR, ADRSCODE, Contract_Number ) SELECT sc.Type_Call_Short, sc.Contract_Number, sc.Call_Time, sc.Service_Call_ID, sc.ADRSCODE, sc.CUSTNMBR FROM dbo.SV00300 sc JOIN cteContract c ON sc.CUSTNMBR = c.CUSTNMBR AND sc.ADRSCODE = c.ADRSCODE AND sc.Contract_Number = c.Contract_Number AND sc.WSCONTSQ = c.WSCONTSQ WHERE Type_Call_Short IN ('MC','MCC')