How count the days and then segregate them out into Monthwise data?
Application Type
Mobile

I have a dataset in which i scan QR codes and later translated into series of database and tables to get records as required, respective to the QR codes. I get the corresponding ID (PRN) matching to the QR code, along with date and time columns. Am planning to make an application for my class in which i take the attendance through QR code and later map them to respective students. Further to this, make Reports available based on the Semester or Subject or Course. Students should be be able to see their attendance and related other activities as they happen.

After using Keywords filter through the search input - i get this filtered list of Date and PRN for a given Keyword (PRN) as shown below

Now for a report - i want to get the Count of UNIQUE Records for a given month based on Keyword (PRN).  

so in the above case i will need to see the answer as totals for each month as Apr'22 - 2 & May'22 - 5. I could get the Total Days, Count of days, Percentage --but My question is how do i get the count of these filtered data group together by dates segregated by Month.

Am open for any other option to do the same. Also, my OML is a mess currently. hence these screen shots.

mvp_badge
MVP
Solution

I've made a change to the SQL example by using COUNT(DISTINCT DATE). It should now only count unique dates for each PRN. Example here.

If you're willing to create an Advanced Query, it would be the most straightforward solution since you can just convert that SQL example into the OutSystems syntax. It would look a bit like this:

SELECT {DB1}.[PRN], Year({DB1}.[Date]), MONTHNAME({DB1}.[Date]), COUNT(DISTINCT {DB1}.[Date])
FROM {DB1}
GROUP BY {DB1}.[PRN], Year({DB1}.[Date]), MONTHNAME({DB1}.[Date])

However, you would have to create it in server-side logic if you're building a mobile app. With COUNT(DISTINCT DATE) I believe this can no longer be done in an Aggregate.

mvp_badge
MVP

Hello,

If I understood correctly, you want a query to return a total count of unique days grouped by PRN and month. You'd do something like this.

MONTHNAME is MySQL specific, you'd have to replace it with DATENAME or FORMAT.

Yes you are correct and the SQL you have provided is also correct which gives the desired results... But, to replicate this in the Outsystems SQL seems difficult for me. I tried but its very rigid - for my level.

any other documentation which can help me get there?


EDIT: the SQL is NOT taking in UNIQUE dates - its counting even if the date is repeated for the same PRN.

mvp_badge
MVP
Solution

I've made a change to the SQL example by using COUNT(DISTINCT DATE). It should now only count unique dates for each PRN. Example here.

If you're willing to create an Advanced Query, it would be the most straightforward solution since you can just convert that SQL example into the OutSystems syntax. It would look a bit like this:

SELECT {DB1}.[PRN], Year({DB1}.[Date]), MONTHNAME({DB1}.[Date]), COUNT(DISTINCT {DB1}.[Date])
FROM {DB1}
GROUP BY {DB1}.[PRN], Year({DB1}.[Date]), MONTHNAME({DB1}.[Date])

However, you would have to create it in server-side logic if you're building a mobile app. With COUNT(DISTINCT DATE) I believe this can no longer be done in an Aggregate.

Hi Vishill,


Please find the attached OML for the same and let me know if you have any doubt in understanding the aggregate.



SampleApp.oml

thank you Mayank for your reply and OML - but am looking to get a count based on the Month of the date.

Hi,


I have updated the module as per your requirement.

SampleApp.oml

Thanks for your help Mayank. However i wanted to derive this results...

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.