How Create a query Group by Month DOB and Sort by Date from DOB

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
cishpix
Posts: 183
Joined: Fri Nov 06, 2015 5:07 am
Location: Indonesia

How Create a query Group by Month DOB and Sort by Date from DOB

Post by cishpix »

Hi all...
I have a BO (call it as StudentData) with 2 attributes (Name and DOB). I want to create a query that can show all the data of the BO with the below criteria
  • all the data will be group by month from DOB
  • If there are some data that have same month (DOB) then the data will be sort by date from DOB
I have try set like below in the "Group Template Header" but it not works well

Code: Select all

Month of DOB: #= kendo.toString(value,'MMMM') #
Any idea??
Regards,

Suwandy
-----------------
Kisaran - Indonesia
himanshu
Posts: 722
Joined: Thu Jun 19, 2008 6:24 am
Location: India
Contact:

Re: How Create a query Group by Month DOB and Sort by Date from DOB

Post by himanshu »

Hi Suwandy,

create a new attribute Month and calculate month from the DOB and assign value in it.

create query and group by month order by DOB
From,
Himanshu Jain


AwareIM Consultant (since version 4.0)
OS: Windows 10.0, Mac
DB: MYSQL, MSSQL
cishpix
Posts: 183
Joined: Fri Nov 06, 2015 5:07 am
Location: Indonesia

Re: How Create a query Group by Month DOB and Sort by Date from DOB

Post by cishpix »

Hi Himanshu..
many thanks for your idea..
himanshu wrote: Thu Sep 09, 2021 11:13 am create a new attribute Month and calculate month from the DOB and assign value in it
I already create like your suggestion and I got it as number in Month attribute. So, my question how can I convert it as month name not as number in query when I set it in grouping?
Regards,

Suwandy
-----------------
Kisaran - Indonesia
himanshu
Posts: 722
Joined: Thu Jun 19, 2008 6:24 am
Location: India
Contact:

Re: How Create a query Group by Month DOB and Sort by Date from DOB

Post by himanshu »

instead of number you can save the Month Name too..

MONTH_TEXT(MONTH(CustomDate))
From,
Himanshu Jain


AwareIM Consultant (since version 4.0)
OS: Windows 10.0, Mac
DB: MYSQL, MSSQL
cishpix
Posts: 183
Joined: Fri Nov 06, 2015 5:07 am
Location: Indonesia

Re: How Create a query Group by Month DOB and Sort by Date from DOB

Post by cishpix »

Hi Himanshu..
thanks again for your help.. it works but..
himanshu wrote: Thu Sep 09, 2021 3:59 pm instead of number you can save the Month Name too..
how can I sort the month name when I set it as group? Currently it sort as ascending but I want the month name sort like in calendar..
Any idea again :D
Regards,

Suwandy
-----------------
Kisaran - Indonesia
himanshu
Posts: 722
Joined: Thu Jun 19, 2008 6:24 am
Location: India
Contact:

Re: How Create a query Group by Month DOB and Sort by Date from DOB

Post by himanshu »

could be couple of ways... a quick idea on this

save your Month text attribute value something like this

01-Jan..
02-Feb..
03-March

=PAD_LEFT(MONTH(CustomDate),'0',2)+'-'+MONTH_TEXT(MONTH(CustomDate))
From,
Himanshu Jain


AwareIM Consultant (since version 4.0)
OS: Windows 10.0, Mac
DB: MYSQL, MSSQL
cishpix
Posts: 183
Joined: Fri Nov 06, 2015 5:07 am
Location: Indonesia

Re: How Create a query Group by Month DOB and Sort by Date from DOB

Post by cishpix »

Hi Himanshu..
You're really perfect man! Many thanks for your help..
It's really works..
But I'm curious and want to know, is it possible only show the group by month name (not include with the month number) and sort like in Calendar?
Regards,

Suwandy
-----------------
Kisaran - Indonesia
himanshu
Posts: 722
Joined: Thu Jun 19, 2008 6:24 am
Location: India
Contact:

Re: How Create a query Group by Month DOB and Sort by Date from DOB

Post by himanshu »

a quick solution to achieve is keep both values in two columns MONTH_TEXT and MONTH

attributeMonth= MONTH(CustomDate)
= MONTH_TEXT(attributeMonth)

hide the attributeMonth col in grid query and sort order by attributeMonth.

I think this way you have what you looking.. :idea:
From,
Himanshu Jain


AwareIM Consultant (since version 4.0)
OS: Windows 10.0, Mac
DB: MYSQL, MSSQL
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: How Create a query Group by Month DOB and Sort by Date from DOB

Post by PointsWell »

This is covered here with an example

https://www.awareim.com/forum/viewtopic ... ort#p56022
Post Reply