Solved

SQL Where Clause Help with Later of Date

Posted on 2014-04-24
12
218 Views
Last Modified: 2014-05-07
I have 3 dates that will appear in a report. They are: Inv Date, Inv Eff Date and Entered Date.

I need the report to only pull back the line item that has a later date of the three dates that fit the report parameters.



For example,.....

Inv No       Prem        Comm       Pol No      Inv Date        Inv Eff Date       Entered Date
1336496       (12.00)       (1.50)      BCV064      4/10/2014   3/1/2014          4/10/2014

Right now I run the report in another tool with the report date parameters as follows:

Inv Date >=3/1/2014 and Inv Date<= 3/31/2014

OR

Inv Eff Date >=3/1/2014 and Inv Eff Date<= 3/31/2014

OR

Entered Date >=3/1/2014 and Entered Date<= 3/31/2014


As a result of my dates, I get the above transaction. However, the LATER DATE is 4/10/2014 and I want it NOT to show.
0
Comment
Question by:Newbi22
  • 3
  • 2
  • 2
  • +4
12 Comments
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
Instead of OR, try AND
Inv Date >=3/1/2014 and Inv Date<= 3/31/2014

AND

Inv Eff Date >=3/1/2014 and Inv Eff Date<= 3/31/2014

AND

Entered Date >=3/1/2014 and Entered Date<= 3/31/2014

Open in new window

0
 

Author Comment

by:Newbi22
Comment Utility
Sharath,

Thanks  for the quick turn around, I comtemplated that but not sure that would work because it is all inclusive. The example, I have above would need to show in April and NOT March.

Would the AND clause omit it in April??? If, so, then I would not want that.
0
 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
Your description is not clear. Where does the LATER DATE comes from?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
I use a function to do this type of thing.

fnMax accepts an array of parameters, so you can pass any number of parameters and it will return the maximum value from among them.  So, in your query, you would use:

WHERE fnMax([Inv Date], [Inv Eff Date], [Entered Date])  Between #3/1/14# and #3/31/14#

Public Function fnMax(ParamArray ValList() As Variant) As Variant

   Dim intLoop As Integer
   Dim myVal As Variant
   
   For intLoop = LBound(ValList) To UBound(ValList)
      If Not IsNull(ValList(intLoop)) Then
         If IsEmpty(myVal) Then
            myVal = ValList(intLoop)
         ElseIf ValList(intLoop) > myVal Then
            myVal = ValList(intLoop)
         End If
      End If
   Next
   fnMax = myVal
   
End Function

Open in new window

BTW, you can use this with numbers, dates or strings.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Determine the lastest date -- for example, using a CROSS APPLY as below -- then compare that last value to the date range you want, like so:


FROM dbo.tablename
CROSS APPLY (
    SELECT CASE WHEN [Inv Date] > [Inv Eff Date] THEN [Inv Date] ELSE [Inv Eff Date] END AS last_date_prelim
) AS ca1
CROSS APPLY (
    SELECT CASE WHEN last_date_prelim > [Entered Date] THEN last_date_prelim ELSE [Entered Date] END AS last_date
) AS ca1
WHERE
    last_date >= '20140301' AND
    last_date < '20140401'
0
 

Author Comment

by:Newbi22
Comment Utility
Ste5fan,

LATER DATE is just the later of the 3 dates. The initial report results are

Inv Date        Inv Eff Date       Entered Date
 4/10/2014   3/1/2014          4/10/2014

The later date is 4/10/2014. So I want to show that in April and not March
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
As I mentioned, I use my fnMax( ) function for these instances, but you could also do somthing like:

LatestDate: SWITCH([Inv Date] > [Inv Eff Date] AND [Inv_Date] > [Entered Date], [Inv_Date],
                           [Inv Eff Date] > [Inv Date] AND [Inv Eff Date] > [Entered Date], [Inv Eff Date],
                           True, [Entered Date])
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
Are you saying that if the latest date of the three date fields is beyond your date range, you don't want that record? One observation, do any of your date fields contain a time element? If so, you should change <= 3/31/2014 to < 4/01/2014.
0
 
LVL 32

Accepted Solution

by:
Stefan Hoffmann earned 500 total points
Comment Utility
E.g.

SELECT	*
FROM	yourTable
WHERE	NOT ( [Inv Date] < '3/1/2014' 
		OR [Inv Date] > '3/31/2014' 
		OR [Inv Eff Date] < '3/1/2014' 
		OR [Inv Eff Date] > '3/31/2014'
		OR [Entered Date] < '3/1/2014' 
		OR [Entered Date] > '3/31/2014'
	);

Open in new window

0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
No.  I coded this earlier:

WHERE
    _date_ >= '20140301' AND
    _date_ < '20140401'
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Have you tried  Sharath's suggestion?  It looks like all the conditions have to be true not just any one of them.  That means that you need to use the AND operator to connect the conditions.  Using the AND operator, only records where ALL the dates are in the desired range will be selected.  

If you don't care about all the dates but just the one with the newest date, then either of the solutions posed by Dale would work.

If neither of those solutions satisfy the requirement then please clarify the requirement.
0
 

Author Closing Comment

by:Newbi22
Comment Utility
Good job
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now