SQL Server Get Records from 2 patient Tables in sql

siv3szwd  于 2023-04-19  发布在  其他
关注(0)|答案(3)|浏览(149)

I have Two tables OPPatient AND Pharmacy

select SUM(Amtpaid) 
from OPPatient 
where startdate >= '2024-11-01' AND enddate  < '2024-12-01' 
select Sum(Amtpaid) 
from Pharmacy 
where startdate >= '2024-11-01' AND enddate  < '2024-12-01'

Need output like

AmtPaidAmtPaid
1000040000

Tried above query which is returning AmtPaid with 2 record like

AmtPaid
10000
40000

but i need as

AmtPaidAmtPaid
10000040000
fwzugrvs

fwzugrvs1#

Just select both values:

select
    (
        select sum(amtpaid) 
        from oppatient 
        where startdate >= '2024-11-01' and enddate  < '2024-12-01' 
    ) as amtpaid_patient,
    (
        select sum(amtpaid) 
        from pharmacy 
        where startdate >= '2024-11-01' and enddate  < '2024-12-01'
    ) as amtpaid_pharmacy
laawzig2

laawzig22#

You can use cte to solve the problem

;with  OPPatientcte as (

                        SELECT SUM(Amtpaid)  as AmtpaidOPPatient
                        FROM OPPatient 
                        WHERE 
                                     startdate >= '2024-11-01' 
                                 AND enddate  < '2024-12-01'

) 
,Pharmacycte as (

                        SELECT Sum(Amtpaid) as AmtpaidPharmacy 
                        FROM Pharmacy 
                        WHERE        startdate >= '2024-11-01' 
                                 AND enddate  < '2024-12-01'
)
SELECT *
FROM OPPatientcte,Pharmacycte

You can create insert Base data with the following statements:

create table OPPatient(Amtpaid int,startdate date,enddate date)
 create table Pharmacy(Amtpaid int,startdate date,enddate date)

 insert into OPPatient
 (Amtpaid,startdate,enddate)
 select 5000,'2024-11-01','2024-11-01'
 union all select 5000,'2024-11-01','2024-10-01'
  
   insert into Pharmacy
 (Amtpaid,startdate,enddate)
 select 20000,'2024-11-01','2024-11-01'
 union all select 20000,'2024-11-01','2024-12-01'
kmbjn2e3

kmbjn2e33#

This is an other way to pivot your result using the aggregate function MAX() with CASE clause :

select MAX(case when source = 'OPPatient' then amtpaid end) as amtpaid_OPPatient,
       MAX(case when source = 'Pharmacy' then amtpaid end) as amtpaid_Pharmacy
from (
  select SUM(Amtpaid) as amtpaid, 'OPPatient' as source
  from OPPatient 
  where startdate >= '2024-11-01' AND enddate  < '2024-12-01' 
  union all
  select Sum(Amtpaid) as amtpaid, 'Pharmacy' as source
  from Pharmacy 
  where startdate >= '2024-11-01' AND enddate  < '2024-12-01'
) as s;

Result :

amtpaid_OPPatient   amtpaid_Pharmacy
100000              40000

Demo here

相关问题