Solved

SQL Where Clause Help with Later of Date

Posted on 2014-04-24
12
219 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
ID: 40020846
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
ID: 40020871
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 33

Expert Comment

by:ste5an
ID: 40020912
Your description is not clear. Where does the LATER DATE comes from?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40020921
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
ID: 40020982
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
ID: 40020986
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40021028
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 32

Expert Comment

by:awking00
ID: 40022626
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 33

Accepted Solution

by:
ste5an earned 500 total points
ID: 40022866
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
ID: 40022884
No.  I coded this earlier:

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

Expert Comment

by:PatHartman
ID: 40024828
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
ID: 40047799
Good job
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

895 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

13 Experts available now in Live!

Get 1:1 Help Now