Solved

SQL Where Clause Help with Later of Date

Posted on 2014-04-24
12
227 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:Michael Franz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +4
12 Comments
 
LVL 41

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:Michael Franz
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 34

Expert Comment

by:ste5an
ID: 40020912
Your description is not clear. Where does the LATER DATE comes from?
0
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 
LVL 48

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:Scott Pletcher
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:Michael Franz
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
 
LVL 48

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 34

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:Scott Pletcher
ID: 40022884
No.  I coded this earlier:

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

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:Michael Franz
ID: 40047799
Good job
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

729 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