Unable to perform join two tables based on JOINS

user_224636 0
Viewed 67

Please refer to https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=15bd35473ac0c18b2b3a21f3decd1a77

Here you will find two tables - Margin and BSC (Bill Service Category). They have common column which is BillNo. Using my CTE query I am getting result mentioned in URL (above)

The output query that I need should filter only records whose ServiceCategory_Name is "IP Package"

If it is IP Package I need to refer to column - ServiceName. Hence In the example, I am concerned with row 4 i.e. Service Name as "GIPSA Inguinal Hernioplasty - Unilateral (DELUXE) "

Rest all rows are not needed.

IF, the servicecategoryName is NOT IP Package, the ServiceName should display as NULL.

Ideally. there should be2 rows (for each bill number) that should show output like

enter image description here

I have written my CTE query (see dbfiddle URL). Here I tried putting "having clause" (see here) in Group by that should filter IPPackage but in doing so it will remove the 2nd row .

Suggestions and Help appreciated !

sql-server sql
2 Answers

based on the brief above, if you put

where ServiceCategory_Name  = 'IP PACKAGE'

after the 

from cte

this will filter for just the package. you count then UNION to add the line with no values

where ServiceCategory_Name  = 'IP PACKAGE'
group by MonthName, BillNo, BSCBillNo, Service_Name, ServiceCategory_Name
UNION
select top 1 MonthName,
0 as amt,
0 as [Internal Dr Visit Charges]
,0 as [External Dr Visit Charges],
cte.BillNo,
cte.BSCBillNo,
'' AS Service_Name,
'' AS ServiceCategory_Name
from cte
order by amt desc
user_13533240
user_107593810
Related