SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [dbo].[MarginTemp](
	[Amount] [float] NULL,
	[ServiceCategoryName] [nvarchar](255) NULL,
	[Bill_ID] [float] NULL,
	[Type] [nvarchar](255) NULL,
	[BillDate] [datetime] NULL,
	[BillNo] [nvarchar](255) NULL,
	[PatientType] [nvarchar](255) NULL,
	[BillingClassDesc] [nvarchar](255) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[BSCTemp](
	[Billno] [nvarchar](255) NULL,
	[Service_Name] [nvarchar](255) NULL,
	[ServiceCategory_Name] [nvarchar](255) NULL
) ON [PRIMARY]


INSERT [dbo].[MarginTemp] ([Amount], [ServiceCategoryName], [Bill_ID], [Type], [BillDate], [BillNo], [PatientType], [BillingClassDesc]) VALUES (9000, N'BED CHARGES', 1111, N'Revenue', CAST(N'2019-09-01T00:52:05.000' AS DateTime), N'A202019339291', N'SELF', N'NOT APPLICABLE')

INSERT [dbo].[MarginTemp] ([Amount], [ServiceCategoryName], [Bill_ID], [Type], [BillDate], [BillNo], [PatientType], [BillingClassDesc]) VALUES (950, N'BED SIDE PROCEDURE', 1111, N'Revenue', CAST(N'2019-09-01T00:52:05.000' AS DateTime), N'A202019339291', N'SELF', N'NOT APPLICABLE')

INSERT [dbo].[MarginTemp] ([Amount], [ServiceCategoryName], [Bill_ID], [Type], [BillDate], [BillNo], [PatientType], [BillingClassDesc]) VALUES (3000, N'External Dr Visit Charges', 1111, N'Revenue', CAST(N'2019-09-01T00:52:05.000' AS DateTime), N'A202019339291', N'SELF', N'NOT APPLICABLE')

INSERT [dbo].[MarginTemp] ([Amount], [ServiceCategoryName], [Bill_ID], [Type], [BillDate], [BillNo], [PatientType], [BillingClassDesc]) VALUES (2000, N'Internal Dr Visit Charges', 1111, N'Revenue', CAST(N'2019-09-01T00:52:05.000' AS DateTime), N'A202019339291', N'SELF', N'NOT APPLICABLE')

INSERT [dbo].[MarginTemp] ([Amount], [ServiceCategoryName], [Bill_ID], [Type], [BillDate], [BillNo], [PatientType], [BillingClassDesc]) VALUES (5940, N'LABORATORY', 1111, N'Revenue', CAST(N'2019-09-01T00:52:05.000' AS DateTime), N'A202019339291', N'SELF', N'NOT APPLICABLE')

INSERT [dbo].[MarginTemp] ([Amount], [ServiceCategoryName], [Bill_ID], [Type], [BillDate], [BillNo], [PatientType], [BillingClassDesc]) VALUES (500, N'MEDICAL', 1111, N'Revenue', CAST(N'2019-09-01T00:52:05.000' AS DateTime), N'A202019339291', N'SELF', N'NOT APPLICABLE')

INSERT [dbo].[MarginTemp] ([Amount], [ServiceCategoryName], [Bill_ID], [Type], [BillDate], [BillNo], [PatientType], [BillingClassDesc]) VALUES (30097.93, N'X-Gross Revenue', 1111, N'Gross-Revenue', CAST(N'2019-09-01T00:52:05.000' AS DateTime), N'A202019339291', N'SELF', N'NOT APPLICABLE')

INSERT [dbo].[MarginTemp] ([Amount], [ServiceCategoryName], [Bill_ID], [Type], [BillDate], [BillNo], [PatientType], [BillingClassDesc]) VALUES (0, N'Y-Discount', 1111, N'Ydiscount', CAST(N'2019-09-01T00:52:05.000' AS DateTime), N'A202019339291', N'SELF', N'NOT APPLICABLE')

INSERT [dbo].[MarginTemp] ([Amount], [ServiceCategoryName], [Bill_ID], [Type], [BillDate], [BillNo], [PatientType], [BillingClassDesc]) VALUES (0, N'TPADiscount', 1111, N'TPADiscount', CAST(N'2019-09-01T00:52:05.000' AS DateTime), N'A202019339291', N'SELF', N'NOT APPLICABLE')

INSERT [dbo].[MarginTemp] ([Amount], [ServiceCategoryName], [Bill_ID], [Type], [BillDate], [BillNo], [PatientType], [BillingClassDesc]) VALUES (30098, N'Z-Net Revenue', 1111, N'M', CAST(N'2019-09-01T00:52:05.000' AS DateTime), N'A202019339291', N'SELF', N'NOT APPLICABLE')

INSERT [dbo].[MarginTemp] ([Amount], [ServiceCategoryName], [Bill_ID], [Type], [BillDate], [BillNo], [PatientType], [BillingClassDesc]) VALUES (NULL, N'OT Consumables', 1111, N'CostInccured', CAST(N'2019-09-01T00:52:05.000' AS DateTime), N'A202019339291', N'SELF', N'NOT APPLICABLE')

INSERT [dbo].[MarginTemp] ([Amount], [ServiceCategoryName], [Bill_ID], [Type], [BillDate], [BillNo], [PatientType], [BillingClassDesc]) VALUES (4603.44, N'Pharmacy', 1111, N'CostInccured', CAST(N'2019-09-01T00:52:05.000' AS DateTime), N'A202019339291', N'SELF', N'NOT APPLICABLE')

INSERT [dbo].[MarginTemp] ([Amount], [ServiceCategoryName], [Bill_ID], [Type], [BillDate], [BillNo], [PatientType], [BillingClassDesc]) VALUES (NULL, N'Implant', 1111, N'CostInccured', CAST(N'2019-09-01T00:52:05.000' AS DateTime), N'A202019339291', N'SELF', N'NOT APPLICABLE')

INSERT [dbo].[MarginTemp] ([Amount], [ServiceCategoryName], [Bill_ID], [Type], [BillDate], [BillNo], [PatientType], [BillingClassDesc]) VALUES (992, N'Investigations', 1111, N'CostInccured', CAST(N'2019-09-01T00:52:05.000' AS DateTime), N'A202019339291', N'SELF', N'NOT APPLICABLE')

INSERT [dbo].[MarginTemp] ([Amount], [ServiceCategoryName], [Bill_ID], [Type], [BillDate], [BillNo], [PatientType], [BillingClassDesc]) VALUES (8427.93, N'PHARMACY', 1111, N'Revenue', CAST(N'2019-09-01T00:52:05.000' AS DateTime), N'A202019339291', N'SELF', N'NOT APPLICABLE')

INSERT [dbo].[MarginTemp] ([Amount], [ServiceCategoryName], [Bill_ID], [Type], [BillDate], [BillNo], [PatientType], [BillingClassDesc]) VALUES (280, N'RADIOLOGY', 1111, N'Revenue', CAST(N'2019-09-01T00:52:05.000' AS DateTime), N'A202019339291', N'SELF', N'NOT APPLICABLE')

INSERT [dbo].[MarginTemp] ([Amount], [ServiceCategoryName], [Bill_ID], [Type], [BillDate], [BillNo], [PatientType], [BillingClassDesc]) VALUES (5595.44, N'TOTAL DIRECT EXPENSES ', 1111, N'B-TOTAL DIRECT EXPENSES', CAST(N'2019-09-01T00:52:05.000' AS DateTime), N'A202019339291', N'SELF', N'NOT APPLICABLE')

INSERT [dbo].[MarginTemp] ([Amount], [ServiceCategoryName], [Bill_ID], [Type], [BillDate], [BillNo], [PatientType], [BillingClassDesc]) VALUES (30098, N'GROSS MARGIN', 1111, N'A-GROSS MARGIN', CAST(N'2019-09-01T00:52:05.000' AS DateTime), N'A202019339291', N'SELF', N'NOT APPLICABLE')

INSERT [dbo].[MarginTemp] ([Amount], [ServiceCategoryName], [Bill_ID], [Type], [BillDate], [BillNo], [PatientType], [BillingClassDesc]) VALUES (-5595.44, N'GROSS MARGIN', 1111, N'A-GROSS MARGIN', CAST(N'2019-09-01T00:52:05.000' AS DateTime), N'A202019339291', N'SELF', N'NOT APPLICABLE')

INSERT [dbo].[MarginTemp] ([Amount], [ServiceCategoryName], [Bill_ID], [Type], [BillDate], [BillNo], [PatientType], [BillingClassDesc]) VALUES (1000.00, N'GROSS MARGIN', 1111, N'A-GROSS MARGIN', CAST(N'2019-09-01T00:52:05.000' AS DateTime), N'A202019339292', N'SELF', N'NOT APPLICABLE')
20 rows affected

INSERT [dbo].[BSCTemp] ([Billno], [Service_Name], [ServiceCategory_Name]) VALUES (N'A202019339291', N'Admission Charges', N'BILLING')

INSERT [dbo].[BSCTemp] ([Billno], [Service_Name], [ServiceCategory_Name]) VALUES (N'A202019339291', N'GIPSA Inguinal Hernioplasty - Unilateral (DELUXE)', N'IP PACKAGE')

INSERT [dbo].[BSCTemp] ([Billno], [Service_Name], [ServiceCategory_Name]) VALUES (N'A202019339291', N'Laproscopy Charges - 15%', N'PROCEDURE CHARGES')

INSERT [dbo].[BSCTemp] ([Billno], [Service_Name], [ServiceCategory_Name]) VALUES (N'A202019339291', N'HGT', N'BED SIDE PROCEDURE')

INSERT [dbo].[BSCTemp] ([Billno], [Service_Name], [ServiceCategory_Name]) VALUES (N'A202019339291', N'Diet Counselling fees', N'MEDICAL')

INSERT [dbo].[BSCTemp] ([Billno], [Service_Name], [ServiceCategory_Name]) VALUES (N'A202019339291', N'NEBULIZATION', N'BED SIDE PROCEDURE')



INSERT [dbo].[BSCTemp] ([Billno], [Service_Name], [ServiceCategory_Name]) VALUES (N'A202019339292', N'NEBULIZATION', N'BED SIDE PROCEDURE')

INSERT [dbo].[BSCTemp] ([Billno], [Service_Name], [ServiceCategory_Name]) VALUES (N'A202019339292', N'NEBULIZATION', N'MEDICAL')

INSERT [dbo].[BSCTemp] ([Billno], [Service_Name], [ServiceCategory_Name]) VALUES (N'A202019339292', N'NEBULIZATION', N'BILLING')
9 rows affected

WITH CTE as
(SELECT 
---MAT.ServiceCategoryName,
'Sep-19' AS MonthName
, SUM( case when MAT.ServiceCategoryName='Z-NET Revenue' then MAT.Amount else 0 end) as AMOUNT
, SUM( case when MAT.ServiceCategoryName='Internal Dr Visit Charges' then MAT.Amount else 0 end) as [Internal Dr Visit Charges]
,SUM( case when MAT.ServiceCategoryName='External Dr Visit Charges' then MAT.Amount else 0 end) as [External Dr Visit Charges],
BSC.Service_Name,
BSC.ServiceCategory_Name,
MAT.BillNo,
bsc.billno as BSCBillNo
FROM MarginTemp MAT
left join BSCTemp bsc on mat.BillNo = bsc.Billno and bsc.ServiceCategory_Name='IP Package'
GROUP BY
MAT.BillNo,
MAT.ServiceCategoryName,
BSC.Billno,
BSC.Service_Name,
BSC.ServiceCategory_Name
) 

select MonthName,max(AMOUNT) as amt,
max([Internal Dr Visit Charges]) as [Internal Dr Visit Charges]
,max([External Dr Visit Charges]) as [External Dr Visit Charges],
cte.BillNo,
cte.BSCBillNo,
cte.Service_Name,
cte.ServiceCategory_Name
from cte
group by MonthName, BillNo, BSCBillNo, Service_Name, ServiceCategory_Name




MonthName
amt
Internal Dr Visit Charges
External Dr Visit Charges
BillNo
BSCBillNo
Service_Name
ServiceCategory_Name


Sep-19
30098
2000
3000
A202019339291
A202019339291
GIPSA Inguinal Hernioplasty - Unilateral (DELUXE)
IP PACKAGE

Sep-19
0
0
0
A202019339292
null
null
null
MonthName	amt	Internal Dr Visit Charges	External Dr Visit Charges	BillNo	BSCBillNo	Service_Name	ServiceCategory_Name
Sep-19	30098	2000	3000	A202019339291	A202019339291	GIPSA Inguinal Hernioplasty - Unilateral (DELUXE)	IP PACKAGE
Sep-19	0	0	0	A202019339292	null	null	null