asked on

SQL date range question, needs to find the record within one year of one field based on another field but only in cases where there are multiples of another field...

Where there are multiple values of the same [LicenseeID] the query should only show those records where the [NewExpirationDate] (which is already filtered in the query to be after the current date) is within a year of [NewScheduleDate] but only in those cases where multiple values of the same [LicenseeID].

There may be other values in the [NewExpirationDate] where the values are greater than 1 year of [NewScheduleDate] (but in these instances there is only one value for the [LicenseeID])

SQL code is attached.
sql-unique-date.txt

Patrick Matthews

Try this:

SELECT l.LicenseeID, l.SalesPerson, d.NewScheduleID, d.NewOriginalLicenseDate, 
    d.NewScheduleDate, First(d.NewExpirationDate) AS FirstOfNewExpirationDate, 
    d.LicenseTerms, d.LicensePrice, d.Annual, d.[Semi-Annual], d.Quarterly, 
    d.[Natural Gas Bundle], d.[Petroleum Bundle], d.[Electric Bundle], d.Eastern, 
    d.Central, d.Western, d.USGOM, d.Canada, d.Mexico, d.[Middle East], 
    d.[WW Refineries], d.[North America], d.DataCoverageArea, d.Projection, 
    d.Datum, d.Units, d.New_DF_Expanded, d.New_DF_Standard, d.AutoCAD_dwg, 
    d.AutoCAD_dxf, d.[ArcInfo export_e00], d.ArcReader, d.Geodatabase_mdb, 
    d.Shapefiles_shp, d.SDEexport, d.MapInfo_mif, d.Notes, d.ArcGIS83, d.ArcGIS9, 
    d.ArcView3X, d.AutoCad2000, d.AutoCadV14, d.AutoCad2004, 
    d.NewSummarySheetArchived, d.[ArcMap Doc], d.Symbology, d.Metadata, 
    l.LicenseeName, l.LicenseType, l.BillToName, l.BillToTitle, l.BillToAddress, 
    l.BillToCity, l.BillToState, l.BillToPostalCode, l.BillToPhone, l.BillToFax, 
    l.BillToEmail, l.CountryName, l.ShipToName, l.ShipToTitle, l.ShipToAddress, 
    l.ShipToCity, l.ShipToState, l.ShipToPostalCode, l.ShipToCountryName, 
    l.ShipToPhone, l.ShipToFax, l.ShipToEmail, l.ShippingNotes, 
    l.LicenseSitePhysicalAddress, l.LicenseSiteCity, l.LicenseSiteState, 
    l.LicenseSitePostalCode, l.LicenseSiteCountryName, d.NewSummarySheetArchived
FROM T_Licensee l INNER JOIN 
    T_Data d ON l.LicenseeID = d.LicenseeID
WHERE d.Annual = False AND d.[Semi-Annual] = False AND d.Quarterly = True AND 
    d.NewSummarySheetArchived = False And d.NewExpirationDate >= Date() AND
    d.NewExpirationDate <= DateAdd("m", 12, Date())
GROUP BY l.LicenseeID, l.SalesPerson, d.NewScheduleID, d.NewOriginalLicenseDate, 
    d.NewScheduleDate, d.LicenseTerms, d.LicensePrice, d.Annual, d.[Semi-Annual], 
    d.Quarterly, d.[Natural Gas Bundle], d.[Petroleum Bundle], d.[Electric Bundle], 
    d.Eastern, d.Central, d.Western, d.USGOM, d.Canada, d.Mexico, d.[Middle East], 
    d.[WW Refineries], d.[North America], d.DataCoverageArea, d.Projection, 
    d.Datum, d.Units, d.New_DF_Expanded, d.New_DF_Standard, d.AutoCAD_dwg, 
    d.AutoCAD_dxf, d.[ArcInfo export_e00], d.ArcReader, d.Geodatabase_mdb, 
    d.Shapefiles_shp, d.SDEexport, d.MapInfo_mif, d.Notes, d.ArcGIS83, d.ArcGIS9, 
    d.ArcView3X, d.AutoCad2000, d.AutoCadV14, d.AutoCad2004, 
    d.NewSummarySheetArchived, d.[ArcMap Doc], d.Symbology, d.Metadata, 
    l.LicenseeName, l.LicenseType, l.BillToName, l.BillToTitle, l.BillToAddress, 
    l.BillToCity, l.BillToState, l.BillToPostalCode, l.BillToPhone, l.BillToFax, 
    l.BillToEmail, l.CountryName, l.ShipToName, l.ShipToTitle, l.ShipToAddress, 
    l.ShipToCity, l.ShipToState, l.ShipToPostalCode, l.ShipToCountryName, 
    l.ShipToPhone, l.ShipToFax, l.ShipToEmail, l.ShippingNotes, 
    l.LicenseSitePhysicalAddress, l.LicenseSiteCity, l.LicenseSiteState, 
    l.LicenseSitePostalCode, l.LicenseSiteCountryName, d.NewSummarySheetArchived
HAVING Count(*) = 1 OR 
    (Count(*) > 1 AND First(d.NewExpirationDate]) >= d.NewScheduleDate AND
    First(d.NewExpirationDate]) <= DateAdd("yyyy", 1, d.NewScheduleDate))
ORDER BY l.LicenseeName;

Open in new window

Oscar9ertango

ASKER

Thx so much but I get a syntax error when I try to use this SQL, Im going to attach the screen pic to save time. Tell me if you would prefer for me to write out the error
error-msg.JPG

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Oscar9ertango

ASKER

I'm sorry to ask but the query itself seems to work but now when I run the report that was based on the query before (which worked before but was pulling too many records, namely those duplicate LicenseID's it asks me for the parameter value for [NewExpirationDate]. I know this is a little beyond the original question which I will accept as a solution but any idea why it now needs that value entered?

Patrick Matthews

Review your report design. It appears that you have a report field for NewExpirationDate, but there is no corresponding field in the query.

There is a query field for FirstOfNewExpirationDate, though. You may have to update your report to use that instead.

Oscar9ertango

ASKER

Thanks so much, yes I figured that out once I looked at the report and saw that error.

Im accepting the above as a solution. Tell me if I need to do anything else. Thx again...

Chuck

Oscar9ertango

ASKER

A great Help!

Patrick Matthews

Glad to help :)