[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

SQL Where Clause Help with Later of Date

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
Michael Franz
Asked:
Michael Franz
  • 3
  • 2
  • 2
  • +4
1 Solution
 
SharathData EngineerCommented:
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
 
Michael FranzCFOAuthor Commented:
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
 
ste5anSenior DeveloperCommented:
Your description is not clear. Where does the LATER DATE comes from?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
Dale FyeCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
Michael FranzCFOAuthor Commented:
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
 
Dale FyeCommented:
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
 
awking00Commented:
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
 
ste5anSenior DeveloperCommented:
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
 
Scott PletcherSenior DBACommented:
No.  I coded this earlier:

WHERE
    _date_ >= '20140301' AND
    _date_ < '20140401'
0
 
PatHartmanCommented:
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
 
Michael FranzCFOAuthor Commented:
Good job
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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