sql query

magic_bern

Hi all,
i am trying to write a query to return the number of pm that have been compled on time each month for the past year along i the month in which they were done.
here is the open method i have and that is working with maximo:

*****************************************
maximoDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());
maximoDataSet.open();

var sqlText = new String();

// Create a copy of the SiteName parameter
var site = params["SiteName"];

// If the site variable starts with an "=" sign
if(site.substring(0,1) == "=") {
// Change site to remove the "=" sign
site = site.substring(1);
}
else {

}
var month = "datepart (month, targstartdate)";
var day1 = MXReportSqlFormat.getStartDayTimestampFunction(params["StartDate"]);

// Add query to sqlText variable.
sqlText = "select "+ month
+ " as month, count(wonum) as numberofpm from workorder"
+ " where siteid ='" + site + "'"
+ " and worktype in ('PM','SHE','UTY')and istask = 0 and status in ('comp','close')"
+ " and targstartdate >= DATEADD(yy, DATEDIFF(yy,0,"+ day1 +"), 0)"
// to ensure that all pm are only taken from current year
// this is because i am grouping by month number and once we go to
// a different year, the query will return simmilar month of
// of different as 1 value i.e. jan 09 and jan 08 will both be counted as
// january pm
+ " and targstartdate <= " + MXReportSqlFormat.getEndDayTimestampFunction(params["EndDate"])
+ " group by "+ month + " order by " + month + " asc"

;
maximoDataSet.setQuery(sqlText);
****************************************************

this does work only if at least 1 pm is done on time each month. but if none were, i would like it to return 0. at the moment it just doesn't show anything if no pm were done on time.

Thanks for the help

mwilliams

Hi magic_bern,

One thing you might be able to do would be to make another dataSet that lists the appropriate months for the range selected by the user or however you determine this. Then, do an outer join to pair the two dataSets together. Then, you'd just create a computed column to display either 0 or the count returned from your SQL query.