Filtering data using dates and SQL

[Former Member]

Post Author: Ivanbennett

CA Forum: Data Connectivity and SQL

Hi all

been struggling with this one all morning could do with a list help

I am using CR XI rel 2

I have 2 tables

Table one - AUDIT_LOG

PositionIdDateTimeStatCode

Table two - POSITION

PositionIdSiteID

Table Three - SiteID

SiteIDNameTownPostCode

I would like to have a user type in a start date and an end date and then the report will return, records from the Position table, where the PositionID does not appear in the Audit Log. I can establish who has not got an entry for the entire table but I now want a snapshot for a period typed in by the user.

This is the SQL used when I added a command from database expert

SELECT distinct POSITION.ID, POSITION.SITE_ID, SITE.NAME,AUDIT_LOG.DATETIMEFROM SITE INNER JOIN (POSITION LEFT outer JOIN AUDIT_LOG ON POSITION.ID = AUDIT_LOG.POSITION_ID) ON SITE.ID = POSITION.SITE_IDWHERE (((AUDIT_LOG.POSITION_ID) Is Null))

Current Output10/09/2007 ID SITE_ID NAMEAndy Arms 4 AB120002 Andy Arms 103 AB120002 Andy Arms 3 AB120002 Andy Arms 104 AB120002 Andy ArmsCharter Court 2 120001 Charter Court 101 120001 Charter Court 102 120001 Charter Court

Charter Court Test Site 60 129999 Charter Court Test Site

Forte Jester 7 200005 Forte Jester

here 48 123456789 here

Any help appreciated

[Former Member]

Post Author: foghat

CA Forum: Data Connectivity and SQL

you need to create 2 command parameters: start_date_from and start_date_to then add to your where clause:and datetime >= {?start_date_from}and datetime <= {?start_date_to}