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

Back to Forums

vishill

Solved

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

Question

Mobile

Aggregate

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.

07 May

Afonso Carvalho

mvp_badge

MVP

Solution

Replying to vishill's comment on 07 May 2022 16:55:50

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.

See solution in context

07 May

Afonso Carvalho

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.

07 May

3 replies

Last reply 14 days ago

Show thread

Hide thread

vishill

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.

07 May

Afonso Carvalho

mvp_badge

MVP

Solution

Replying to vishill's comment on 07 May 2022 16:55:50

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.

07 May

vishill

Replying to Afonso Carvalho's comment on 07 May 2022 17:20:30

worked smoothly. Thank you.

14 days ago