Solved

SQL Where Clause Help with Later of Date

Posted on 2014-04-24
12
222 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
[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: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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

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

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

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ms/access get folder and file list 20 30
get and set file atrributes 5 13
Balance After Payment 12 21
SQL Query logic question 14 32
I have a large data set and a SSIS package. How can I load this file in multi threading?
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

696 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