CODEWITHSUNDEEP
oracle-databaseselect
Selvakumar Duraipandiyan
Selvakumar Duraipandiyan

Reputation: 5

Datewise count and date range count using Oracle SQL

I have a data as shown in the table (image). I want to find the no. of New and Review patients (head count) on a day and for a range of date.

Table data is (as in attached image)

RegNo VisitID VisitDate NewReview DocID Specialisation
301573 RE410429 10-07-2021 Review DOC00082 SCOM0002
259717 RE410452 10-07-2021 Review DOC00082 SCOM0002
305781 RE410474 10-07-2021 New DOC00001 SCOM0002
301573 RE410536 10-07-2021 Review DOC00007 SCOM0025
305287 RE410544 10-07-2021 Review DOC00004 SCOM0003
305781 RE410570 10-07-2021 Review DOC00004 SCOM0003
197248 RE410573 10-07-2021 Review DOC00179 SCOM0003
291838 RE410574 10-07-2021 Review DOC00179 SCOM0003
305464 RE410579 10-07-2021 Review DOC00152 SCOM0018
305787 RE410609 12-07-2021 New DOC00082 SCOM0002
305801 RE410643 12-07-2021 New DOC00082 SCOM0002
305626 RE410663 12-07-2021 Review DOC00082 SCOM0002
305287 RE410697 12-07-2021 Review DOC00004 SCOM0003
305802 RE410711 12-07-2021 Review DOC00004 SCOM0003
305464 RE410714 12-07-2021 Review DOC00004 SCOM0003
197248 RE410721 12-07-2021 Review DOC00179 SCOM0003
243161 RE410723 12-07-2021 Review DOC00015 SCOM0014
291838 RE410724 12-07-2021 Review DOC00179 SCOM0003

When I take a report for each date, I want the output to be

Date New Review
10-07-2021 1 6
12-07-2021 2 7

(The RegNo 305781 is considered only under 'New' (on 10th July), though on the same day the patient consulted another specialist. First consultation is 'New', other Specialisation consultation on the same day is 'Review'. But for head count, this RegNo (305781) should be considered only under New.)

When I take a report for a date range (from 10-07-2021 to 12-07-2021), the output should be

New Review
3 13

But what I am getting for a date range (from 10-07-2021 to 12-07-2021) is (wrong output)

New Review
3 10

(distinct RegNo in the given date range).

Kindly help me with appropriate query.

Oracle Table Data

Upvotes: 0

Views: 758

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

Use conditional aggregation:

SELECT
    VisitDate,
    COUNT(CASE WHEN NewReview = 'New' THEN 1 END)    AS New,
    COUNT(CASE WHEN NewReview = 'Review' THEN 1 END) AS Review
FROM yourTable
GROUP BY
    VisitDate
ORDER BY
    VisitDate;

Upvotes: 2

Related Questions