query to put order dates in date categorises (1 Viewer)

Number11

Member
Hi all,

So i am trying to find a way to build a report that shows all open orders in date ranges

0-30 days
30-60 days
60-90 days
120+

i guess i would do this via a query and then build the report from that also ?

arnelgp

there are may ways to do this.
on a query, you will need the function Partition() (you can google it), it will group your date Field, on your Query.
on the report, you can Group your data and have Group heading on them (ie. 0-30 days group, etc.)

CJ_London

Super Moderator
Staff member
you have
0-30 days
30-60 days
so if it is exactly 30 days, which one do you want it to be classified as? sam applies to 60 days

60-90 days
120+
what if the order date is 95 days ago?

yes you can do by query - use the switch function to calculate which group an order should be in

grp:switch(date-orderdate<=30,"0-30",date-orderdate<=60,"30-60",etc

or you can use the partition function - which I see arnel has just advised

MajP

You've got your good things, and you've got mine.
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)

Number11

Member
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)
Thanks for your comments, so i need to produce the report likes this ...

1 Week
1 Month
1-3 Months
3-6 Months
6 Months +

and trying to use this format

Oustanding: Switch(Date()-[Order Date]<=7,"1 week",Date()-[Order Date]<=30,"1 Month",Date()-[Order Date]<=91,"1-3 Months",Date()-[Order Date]<=182,"3-6 Months",Date()-[Order Date]<=121,"6 Months Plus")

MajP

You've got your good things, and you've got mine.
If you are asking me, I would never use a switch statement personally. And that mess shows you why.

Number11

Member
If you are asking me, I would never use a switch statement personally. And that mess shows you why.
LOL ok so you think building a table is better, whats the best way to do that ?

MajP

You've got your good things, and you've got mine.
LOL ok so you think building a table is better, whats the best way to do that ?
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.

MajP

You've got your good things, and you've got mine.
SQl needed
Code:
``````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;``````

Attachments

• 207.3 KB Views: 2

MajP

You've got your good things, and you've got mine.
FYI, If you want to be more exact then I would personally role a UDF.
If you really need 3 elapsed months vice 91 days where 27 Feb to 27 May = 3. You would build a function using datediff and check months. This give the real expired periods. May be important if this is calculating delinquent payments where you pay on a set date regardless of the elapsed time.

Staff member

MajP

You've got your good things, and you've got mine.
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``````

Attachments

• 183.5 KB Views: 1

Number11

Member
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``````
ok like this idea, but how do i get it to show in this order

'1 Week
'1 Month
'1-3 Months
'3-6 Months
'6 Months

as shows like this:

DateRange2CountOfAppointment Date
>3-6 Months
205​
1-3 Months
633​
<1 Month
170​
1 Week
12​

Last edited:

MajP

You've got your good things, and you've got mine.
You could build a small table

TblInterval
label
Sort

then
'1 Week 1
'1 Month 2
'1-3 Months 3
'3-6 Months 4
'6 Months 5
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.

arnelgp

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.

Attachments

• 25.4 KB Views: 3

MajP

You've got your good things, and you've got mine.
Disregard my post in 15. If you want to sort the intervals the easiest thing is to sort the query, by start date. Then by default the intervals are sorted.

Number11

Member
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.
so i do like this option, however the week code doent seem to be working as today it is not counting anm order that was entered on 04.04.2020 its counted this within <month? The code looks like it is says if less than 8 days count as 1 week?

(DateDiff("d", p1, Date()) <= 8)

Number11

Member
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.
cant seem to get this to work?

MajP

You've got your good things, and you've got mine.
cant seem to get this to work?
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.