so if it is exactly 30 days, which one do you want it to be classified as? sam applies to 60 days0-30 days
30-60 days
what if the order date is 95 days ago?60-90 days
120+
0 30 0-30 days
30 60 30-60 days
60 90 60-90 days
90 120 90-120 days
120 999999 120+
Another way is to make a small table, which will give you more flexibility
StartRange
EndRange
GroupName
Code:0 30 0-30 days 30 60 30-60 days 60 90 60-90 days 90 120 90-120 days 120 999999 120+
Now you can use the this in a query where the elapsedDays between (StartRange AND EndRange)
LOL ok so you think building a table is better, whats the best way to do that ?If you are asking me, I would never use a switch statement personally. And that mess shows you why.
I am kind of kidding, but it is hard to get all the commas and quotes correct. I will demo with the table, you can decide what is easier.LOL ok so you think building a table is better, whats the best way to do that ?
SELECT tbldata.id,
tbldata.created_date,
tblranges.label,
tblranges.beginrange,
tblranges.endrange,
Datediff("d", [created_date], DATE()) AS Elapsed
FROM tbldata,
tblranges
WHERE (( ( Datediff("d", [created_date], DATE()) ) > [beginrange]
AND ( Datediff("d", [created_date], DATE()) ) <= [endrange] ))
ORDER BY tbldata.created_date DESC;
Public Function GetDateRange(TheDate As Date) As String
'1 Week
'1 Month
'1-3 Months
'3-6 Months
'6 Months
Dim ElapsedMonths As Long
If DateDiff("d", TheDate, Date) <= 7 Then
Debug.Print DateDiff("d", Date, TheDate)
GetDateRange = "1 Week"
Else
ElapsedMonths = DateDiff("M", TheDate, Date)
Debug.Print ElapsedMonths
Select Case ElapsedMonths
Case 0
GetDateRange = "< 1 Month"
Case 1 To 3
GetDateRange = "1-3 Months"
Case 4 To 6
GetDateRange = ">3-6 Months"
Case Else
GetDateRange = "6 Months +"
End Select
End If
End Function
The partition function takes a set interval. In this case the OP has vastly different intervals.Access has a Partition function that may work.
Here is a function that may be more accurate depending on what you want
Code:Public Function GetDateRange(TheDate As Date) As String '1 Week '1 Month '1-3 Months '3-6 Months '6 Months Dim ElapsedMonths As Long If DateDiff("d", TheDate, Date) <= 7 Then Debug.Print DateDiff("d", Date, TheDate) GetDateRange = "1 Week" Else ElapsedMonths = DateDiff("M", TheDate, Date) Debug.Print ElapsedMonths Select Case ElapsedMonths Case 0 GetDateRange = "< 1 Month" Case 1 To 3 GetDateRange = "1-3 Months" Case 4 To 6 GetDateRange = ">3-6 Months" Case Else GetDateRange = "6 Months +" End Select End If End Function
DateRange2 | CountOfAppointment Date |
---|---|
>3-6 Months | 205 |
1-3 Months | 633 |
<1 Month | 170 |
1 Week | 12 |
Then use this in a second query dropping this table into the query window with the first query. And and order by Sort'1 Week 1
'1 Month 2
'1-3 Months 3
'3-6 Months 4
'6 Months 5
this is somewhat similar to mr.majp.
on the sample db, i made a table, tblPartRule
with fields like:
Order
Caption
Rule
the Rule field is the actual rule of which to group
the date field from your order table.
there is also a sampleTable, sample order with
date field.
on Module1 i create a function udfPartition().
call this in your query (see query1).
to Group, i created another query (query2).
query2 will be a little slower since we are
using function in this query and at the same
time grouping it.
You could build a small table
TblInterval
label
Sort
then
Then use this in a second query dropping this table into the query window with the first query. And and order by Sort
Then you would need a where
select .... GetRange([WordkDate]) as Range WHERE GetRannge([WorkDate]) = TblInterval.label order by SORT
This is one reason using that first solution with the simple table is nice, but as I said it is not as accurate if you want "real" intervals.
Did you read post, 17. As I pointed out this is a dumb solution on my part. Simply sort the input. If the dates are in order the ranges are in order.cant seem to get this to work?