Awais Sharafat Awais Sharafat - 24 days ago 10
SQL Question

SQL Server 2 queries to get desired result

Query 1

SELECT
Admin_Services.S_Name, SUM(Patient_Services.Amount) AS Total
FROM
Patient_Services
INNER JOIN
Admin_Services ON Patient_Services.S_ID = Admin_Services.S_ID
WHERE
Admin_Services.S_ID = 1606 OR
Admin_Services.S_ID = 28451 OR
Admin_Services.S_ID = 28449
AND Patient_Services.sdatetime = getdate()
GROUP BY
Admin_Services.S_Name


Results of query 1:

S_Name | Total
Anesthesia Charges | 20000
Consultant Fee | 50200
Operation Charges | 31000


Query 2

SELECT
'Pathology' as S_Name, SUM(Patient_Services.Amount) AS Total
FROM
Patient_Services
INNER JOIN
Admin_Services ON Patient_Services.S_ID = Admin_Services.S_ID
WHERE
Patient_Services.TB_ID IS NOT NULL
AND Patient_Services.sdatetime = getdate()


Results of query 2

S_Name | Total
Pathology | 109576


My desired result looks like:

S_Name | Total
Anesthesia Charges | 20000
Consultant Fee | 50200
Operation Charges | 31000
Pathology | 109576


Please someone help me to get my required result.

Answer

Quick change to get the result you want is to use a UNION ALL to combine the result of both query

-- query 1
SELECT        Admin_Services.S_Name,Sum(Patient_Services.Amount) as Total
FROM            Patient_Services INNER JOIN
                         Admin_Services ON Patient_Services.S_ID = Admin_Services.S_ID
                         where Admin_Services.S_ID = 1606 OR Admin_Services.S_ID = 28451 OR Admin_Services.S_ID = 28449 And Patient_Services.sdatetime = getdate()  
Group By Admin_Services.S_Name

UNION ALL

-- query 2
SELECT        'Pathology' as S_Name, Sum(Patient_Services.Amount) as Total
FROM            Patient_Services INNER JOIN
                         Admin_Services ON Patient_Services.S_ID = Admin_Services.S_ID
                         where Patient_Services.TB_ID is not null And Patient_Services.sdatetime = getdate()