• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

Comparing Dates that fall within a range

Hello Experts,

I have a date and I would like to know if it falls within the follow ranges in a query:

-Occurs within next 10 Days
-Ocurrs within next 11 thru 30 Days
-Occurs within next 31 thru 60 Days
-Occurs within next 61 thru 90 Days
-Occurs within 90+ Days

How do I accomplish this within an expression?

Thank you


 
0
ShadowIT
Asked:
ShadowIT
  • 3
  • 2
  • 2
1 Solution
 
Patrick MatthewsCommented:
Try this:

SELECT Date() AS Today, MyDate, 
    Switch(DateDiff("d", Date(), MyDate) <= 10, "Next 10",
    DateDiff("d", Date(), MyDate) <= 30, "11 - 30", 
    DateDiff("d", Date(), MyDate) <= 60, "31 - 60",
    DateDiff("d", Date(), MyDate) <= 90, "61 - 90", 
    True, "90+") AS Category
FROM SomeTable
WHERE MyDate >= Date()

Open in new window



For more about the Switch function, please see:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_3556-Using-the-Switch-Function-in-Microsoft-Access.html
0
 
Dale FyeCommented:
So your date is in a field (we will call it SuspenseDate since you didn't provide any specifices)?

If you want the query to output 5 separate columns that indicate whether the record falls within one of those date spans then you could do something like:

SELECT SuspenseDate,
             iif(DateDiff("d", date(), [SuspenseDate]) Between 0 and 10, True, False) as [Next 10],
             iif(DateDiff("d", date(), [SuspenseDate]) Between 11 and 30, True, False) as [11 to 30],
             iif(DateDiff("d", date(), [SuspenseDate]) Between 31and 60, True, False) as [31 to 60],
             iif(DateDiff("d", date(), [SuspenseDate]) Between 61 and 90, True, False) as [61 to 90],
             iif(DateDiff("d", date(), [SuspenseDate]) >90, True, False) as [greater than 90]
FROM yourTable


0
 
Dale FyeCommented:
On the other hand, if you want to know which group each record falls in, I prefer to create another table (tbl_Ranges) which contains fields GT (greater than or equal), LT (Less than), RangeName that would look like:

GTE    LT       RangeName
0        11       Next 10
11       31      11 to 30
31       60      31 to 60
61       90      61 to 90
90     NULL    Greater than 90

Then you would create a query that look like:

SELECT SuspenseDate, RangeName
FROM yourTable, tbl_Ranges
WHERE yourTable.SuspenseDate >= tbl_Ranges.GTE
AND yourTable.SuspenseDate < tbl_Ranges.LT
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
ShadowITAuthor Commented:
Savant,

I tried this but cannot seem to get the syntax right.

SELECT Import_T.[Key Field], Import_T.[Booking Region], Import_T.[Forecast Ind#], Import_T.[Account Name], Import_T.[Account Manager], Import_T.[Opportunity Name], Import_T.[Crystal ID], Import_T.[Pipeline Stg#], Import_T.[Proposal Status], Import_T.[Estimator Name], Import_T.[Proposal No#], Import_T.[Proposal Rev No], Import_T.[Estimated Book Date], Import_T.[Product Category], Import_T.[Product Detail], Import_T.[Product Estimated Sell Amt], Import_T.HwlMarkUp, Import_T.EstCost, Import_T.ThirdPartyPercent, Import_T.Est3rdPartyBudget, Import_T.CostReductionPercent, Import_T.EstCostReduction, Import_T.ActuaThirdPartyBudget, Import_T.ActualThirdPartyPercent, Import_T.ProjectedCostReduction, Import_T.BuyerName, IIf(IsNull([Import_T]![BuyerName]),"No","Yes") AS BuyerAssigned, IIf([Product Estimated Sell Amount Not Null]<500000,"<500K",IIf([Product Estimated Sell Amount Not Null]>=500000,"=500K")) AS [Product Est Sell Amount], IIf(IsNull([Product Estimated Sell Amt]),0,[Product Estimated Sell Amt]) AS [Product Estimated Sell Amount Not Null], IIf(IsNull([Import_T]![Probability Win Pct#]),"<50%",IIf([Import_T]![Probability Win Pct#]<0.5,"<50%",IIf([Import_T]![Probability Win Pct#]>=0.5,"=50%"))) AS [Probability Win Pct Groups], [Import_T]![Estimated Book Date], Switch(DateDiff("d", Date(),[Import_T]![Estimated Book Date]) <= 10, "Next 10",DateDiff("d", Date(),[Import_T]![Estimated Book Date]) <= 30, "11 - 30", DateDiff("d", Date(),[Import_T]![Estimated Book Date]) <= 60, "31 - 60",DateDiff("d", Date(),[Import_T]![Estimated Book Date]) <= 90, "61 - 90", True, "90+) AS Category FROM Import_T  WHERE [Import_T]![Estimated Book Date]>= Date()
FROM Import_T
ORDER BY IIf(IsNull([Import_T]![Probability Win Pct#]),"<50%",IIf([Import_T]![Probability Win Pct#]<0.5,"<50%",IIf([Import_T]![Probability Win Pct#]>=0.5,"=50%"))), Import_T.[Probability Win Pct#];
0
 
Patrick MatthewsCommented:
You're missing a doublequote at the end of the 90+ label.

So, this:
   
           "90+

should be:

           "90+"
0
 
ShadowITAuthor Commented:
Fyed,

I goit your solution to work; but I was looking this to be display is a single column within my exist query, which looks like this:

SELECT Import_T.[Key Field], Import_T.[Booking Region], Import_T.[Forecast Ind#], Import_T.[Account Name], Import_T.[Account Manager], Import_T.[Opportunity Name], Import_T.[Crystal ID], Import_T.[Pipeline Stg#], Import_T.[Proposal Status], Import_T.[Estimator Name], Import_T.[Proposal No#], Import_T.[Proposal Rev No], Import_T.[Estimated Book Date], Import_T.[Product Category], Import_T.[Product Detail], Import_T.[Product Estimated Sell Amt], Import_T.HwlMarkUp, Import_T.EstCost, Import_T.ThirdPartyPercent, Import_T.Est3rdPartyBudget, Import_T.CostReductionPercent, Import_T.EstCostReduction, Import_T.ActuaThirdPartyBudget, Import_T.ActualThirdPartyPercent, Import_T.ProjectedCostReduction, Import_T.BuyerName, IIf(IsNull([Import_T]![BuyerName]),"No","Yes") AS BuyerAssigned, IIf([Product Estimated Sell Amount Not Null]<500000,"<500K",IIf([Product Estimated Sell Amount Not Null]>=500000,"=500K")) AS [Product Est Sell Amount], IIf(IsNull([Product Estimated Sell Amt]),0,[Product Estimated Sell Amt]) AS [Product Estimated Sell Amount Not Null], IIf(IsNull([Import_T]![Probability Win Pct#]),"<50%",IIf([Import_T]![Probability Win Pct#]<0.5,"<50%",IIf([Import_T]![Probability Win Pct#]>=0.5,"=50%"))) AS [Probability Win Pct Groups], Import_T.[Probability Win Pct#], IIf([Import_T]![Estimated Book Date]<Date(),"Occured In Past",IIf([Import_T]![Estimated Book Date]=Date(),"Occurs Today")) AS [Estimated Book Date Group]
FROM Import_T
ORDER BY IIf(IsNull([Import_T]![Probability Win Pct#]),"<50%",IIf([Import_T]![Probability Win Pct#]<0.5,"<50%",IIf([Import_T]![Probability Win Pct#]>=0.5,"=50%"))), Import_T.[Probability Win Pct#];
0
 
ShadowITAuthor Commented:
That's the ticket!  Thank you
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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
Tackle projects and never again get stuck behind a technical roadblock.
Join Now