Reports: Date range not finding all within date ranges.

[rssidlowski]

When selecting a date range in the PST Sheets report and I know there is an investigation on 5/3 but not showing in the results list.

Similar for other reports that allow user to select from and to date. Complaints Investigations by complainant for date range Investigations by status for date range

[gerrykelly]

works for me using query string, please see email

[rssidlowski]

Jesse, can you look at the logic for selecting the date range? Is it inclusive of the dates selected?

[jday3]

Are you getting different results in the activity manager grid than you are from when you open the report from the view printable report button?

[rssidlowski]

Yes, the results are different. The selection on the report page is not including the from and to date.
See the selection on the screen: And the results in the report generated:

[gerrykelly]

I'm not familiar with where the results in the activity manager come from, but we agree that the report returns the correct info, right? The report calls the stored procedure -- SP_PST_INVESTIGATIONS -- and in this case just the started/end dates, thus using this query:

SELECT * FROM [Pollution_Source_Tracking].[GIS_ADMIN].[V_PST_Investigations] WHERE convert(date,inv_compldate) <= convert(date, convert(date,@endDate)) and convert(date,inv_compldate) >= convert(date, convert(date,@startDate)) ;

Those convert functions ensure that we never miss records because of a the dates for comparison including a time component and the <=,>= makes sure we include inv_compldate dates of the start/end of the date range. Is the activity manager doing something different?

If it is .NET code doing this, besides the <=,>= ... you need to make sure only the date is being used (by either the convert function as part of the SQL statement, or like if it is in linq: DateTime sd = Convert.ToDateTime(dates[0]); DateTime ed = Convert.ToDateTime(dates[1]); inv = db.PST_Investigations.Where(u => EntityFunctions.TruncateTime(u.INV_COMPLDATE) >= sd && EntityFunctions.TruncateTime(u.INV_COMPLDATE) <= ed);

[jday3]

The activity manager is using the PST Web API to generate the results and also query the Complainant/Status domains. Currently, the GetStatus and GetComplainants endpoints on the Activity controller aren't returning anything so there must be an issue there. I am having trouble getting the Web API project to build, but will continue trying.

[jday3]

Working now on dev.

[rssidlowski]

Not working for Investigations by Status for Date Range Investigation by Complainant for Date Range PST Sheets Get this error below on Investigations with Discharge Classification An error has occurred during report processing. Cannot create a connection to data source 'DataSet1'. Exception has been thrown by the target of an invocation. Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

[jday3]

The date range is working correctly for me in dev where I pushed the fix. The url for the report for 'Investigations with Discharge Classification' is correct, but there is an issue with the report that Gerry developed.

[rssidlowski]

Is this the correct URL for dev: http://dpwdev.baltimorecity.gov/water/activitymanager/index.aspx?

[JMurk]

Yes. You can also get to it via the portal at dpwdev.baltimorecity.gov/dpwportal

From: rssidlowski [mailto:notifications@github.com] Sent: Thursday, June 25, 2015 6:53 AM To: rssidlowski/Pollution_Source_Tracking Subject: Re: [Pollution_Source_Tracking] Reports: Date range not finding all within date ranges. (#161)

Is this the correct URL for dev: http://dpwdev.baltimorecity.gov/water/activitymanager/index.aspx?

— Reply to this email directly or view it on GitHubhttps://github.com/rssidlowski/Pollution_Source_Tracking/issues/161#issuecomment-115208964.

[rssidlowski]

This is still not working in dev. Try pst sheet for ending date 5/7 and it is not finding the pst.

[jday3]

This is now working

[rssidlowski]

Verified.