• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • Last Modified:

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
0
Oscar9ertango
Asked:
Oscar9ertango
  • 4
  • 4
1 Solution
 
Patrick MatthewsCommented:
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

0
 
Oscar9ertangoAuthor Commented:
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
0
 
Patrick MatthewsCommented:
Had some extraneous brackets there.  Try changing the HAVING clause to:

HAVING Count(*) = 1 OR 
    (Count(*) > 1 AND First(d.NewExpirationDate) >= d.NewScheduleDate AND
    First(d.NewExpirationDate) <= DateAdd("yyyy", 1, d.NewScheduleDate))

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Oscar9ertangoAuthor Commented:
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?
0
 
Patrick MatthewsCommented:
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.
0
 
Oscar9ertangoAuthor Commented:
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
0
 
Oscar9ertangoAuthor Commented:
A great Help!
0
 
Patrick MatthewsCommented:
Glad to help :)
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now