1 (edited by eyeman303 2020-10-23 10:22:09)

Topic: How to get date-wise count?

Hello,
I want to know the number of patients seen every day and also the ability to search the number. in a date range. The Patient_Count button in form D4_Day_Activity does not give the desired result. How to best solve this issue?
Sincerely,
Eyeman

Post's attachments

Attachment icon clinic_count_01.zip 469.76 kb, 18 downloads since 2020-10-23 

2 (edited by derek 2020-10-23 12:45:20)

Re: How to get date-wise count?

Hello Eyeman,
Hope you're well.
What you'd done was actually working but because the opd visit date is defined as date/time, that is what you were grouping on (both date AND time) - so you would only ever get counts of 1.
To fix it, use strftime in your sqlquery to use just the 'date' part of the opd visit date in the 'group by'.
You will probably also want to use strftime to change the format of the date/time field so that it only shows the 'date' part in your output.
Please look at the attached and shout out if something's not clear.
Derek.

Post's attachments

Attachment icon clinic count.zip 488.94 kb, 26 downloads since 2020-10-23 

Re: How to get date-wise count?

Hello Derek,
I am fine. Hope you're fine too.
Once again you have provided me with the perfect solution and explained so clearly! Now I have learnt how to extract date from date/time and how to use the WHERE clause to get data for a date range. Thank you so much.
Regards,
Eyeman

4 (edited by eyeman303 2020-10-23 15:09:44)

Re: How to get date-wise count?

Hello Derek,
Is it possible to "SUM" the Patient_Counts in the selected date range?
Regards,
Eyeman

Post's attachments

Attachment icon Count_01.JPG 31.92 kb, 4 downloads since 2020-10-23 

5 (edited by derek 2020-10-24 00:16:54)

Re: How to get date-wise count?

Hi,
To have footer calculations when using sqlquery, you need to add a small script (please see attachment) which
1.  tells the program which column you want to perform a footer calculation on
2.  tells the program what kind of footer calculation you want to perform (in this example it is fksum)
3.  tells the program to perform the calculation using the 'calculatefooter' function
4.  You also need to make sure that the 'gofooter' option in the tablegrid properties is set to true (see screenshot in the attachment).
All of the above could also be done with calculated fields and adding footer options in the 'standard' way.
I have also amended the sqlquery calculation which contained an error (my bad!) - it wasn't selecting records where the opd visit date was the same as the 'to date' parameter in the search.  This happens because the opd visit date is defined as a date/time' type so the program needs to (in effect) add a day to the 'to date' parameter.
Derek.

Post's attachments

Attachment icon clinic count 2.zip 719.21 kb, 23 downloads since 2020-10-24 

Re: How to get date-wise count?

Hello Derek,
Thanks again. The SQL query is working perfectly. I have learnt how to add 1 day when there is a date/time format, to get the correct result. I have learnt from your different programs on how to get data from linked tables using a calculated field. I would love to learn how to use the same for this query. Please!
Regards,
Eyeman

Re: How to get date-wise count?

Hi,
I probably didn't make myself clear - you still need to use the sqlquery to get the actually data and group it by date.
Using a calculated field is just another way to specify the footer calculation (sum) and on what column you want it (see the attached screenshot), rather than having to do it by script.
Derek.

Post's attachments

Attachment icon clinic count 3.zip 497.15 kb, 29 downloads since 2020-10-24 

Re: How to get date-wise count?

Hello Derek,
Got the point about the calculated field. Just like to know the working of "cfdummy" in the database aa_002_opd_visits.
Regards,
Eyeman

Re: How to get date-wise count?

Hi,
It's just a calculated field left deliberately empty but it enables you to specify a footer column calculation, ready for when the sqlquery runs.
The empty calculated field columns are then filled with data from the sqlquery and the footer calculation then works on that data.
Derek.