Getting the daywise report from two tables-CodePudding

I want to generate daywise report from two table

Table 1: opd

CREATE TABLE `opd` (
  `id` int(50) NOT NULL,
  `Date` date NOT NULL,
  `time` time NOT NULL,
  `opd_no` varchar(150) NOT NULL,
  `patientid` varchar(150) DEFAULT NULL,
  `name` varchar(150) NOT NULL,
);

Table structure

id	Date	Time	opd_no	Patient_id	name
1	2022-03-02	18:30:10	OPD/2122/1	PT01	Siba
2	2022-03-03	18:30:10	OPD/2122/2	PT02	Deba
3	2022-03-04	18:30:10	OPD/2122/3	PT03	Haris
4	2022-03-04	18:31:10	OPD/2122/4	PT04	ravish

Table 2: ipd_pn

CREATE TABLE `ipd_pn` (
  `id` int(11) NOT NULL,
  `ipd_no` varchar(40) DEFAULT NULL,
  `patientid` varchar(170) NOT NULL,
  `reg_date` date DEFAULT NULL,
  `time` time DEFAULT NULL,
  `opd` varchar(40) DEFAULT NULL,
  `date` date DEFAULT NULL,
)

Table structure

id	IPD_no	Patient_id	Reg_date	time	opd_no	date
1	IPD/2122/1	PT01	2022-03-02	15:40:10	OPD/2122/1	2022-03-02
2	IPD/2122/2	PT03	2022-03-04	16:35:10	OPD/2122/3	2022-03-03
3	IPD/2122/3	PT02	2022-03-03	15:45:10	OPD/2122/2	2022-03-03

T tried to generate daywise how much opdno's generated by the below query

SELECT DATE(Date) AS date, COUNT(opdno) AS total_opd
FROM opd 
WHERE (date BETWEEN '2022-02-1' AND '2022-03-28')
GROUP BY Date

Got output like below

date	total_opd
2022-03-02	1
2022-03-03	1
2022-03-04	2

But I can't generate how much ipd no's generated in this date period.

Please help.

I want a report like below

date	total_opd	total_ipd
2022-03-02	1	1
2022-03-03	1	1
2022-03-04	2	1

I tried with join query i.e

SELECT DATE(Date) AS date, COUNT(opdno) AS total_opd
FROM opd 0 
INNER JOIN ipd_pn i ON o.Date = i.date
WHERE (date BETWEEN '2022-02-1' AND '2022-02-28')
GROUP BY Date

But that gives date ambiguity error.

So please try to help me how can I generate reports from both the tables.

CodePudding user response：

You can try to use UNION ALL to combine two tables in a subquery then use a flag column to represent which row from opd or ipd_pn

SELECT date,
       SUM(flag = 1) total_opd,
       SUM(flag = 2) total_ipd
FROM (
    SELECT DATE(Date) AS date, 1 flag
    FROM opd 
    UNION ALL
    SELECT Reg_date,2
    FROM ipd_pn
) t1
WHERE date between '2022-02-1' and '2022-03-28'
GROUP BY Date

sqlfiddle