Subtract column data from two unrelated tables

this is my database diagram i want to determine blood remaining in each bank i am having a problem because the tables donation and transfusion are not related how i can subtract?

i have created two selections of blood drawn and blood withdrawn from each bank now i need to subtract but i don’t know how

Please, next time when adding a question, add the table create scripts, and some sample data. This saves a lot of time for both the person asking the question, and the person answering it.

One way to do this can do this with CTE’s:

Table Structure and sample data

CREATE TABLE [DBO].[Donation Record]([Donation ID] INT ,  [Blood bank name] NVARCHAR(255) ,[Donation] DATETIME)
CREATE TABLE [DBO].[Donation]([Donation ID] INT, [Donor ID] INT, [Amount Donated] INT)

CREATE TABLE [DBO].[Transfusion Record] ([Transfusion ID] INT ,  [Blood bank name] NVARCHAR(255) ,[Transfusion] DATETIME)
CREATE TABLE [DBO].[Transfusion]([Transfusion ID] INT, [Patient ID] INT, [Amount Accepted] INT)


INSERT INTO  [DBO].[Donation Record]([Donation ID],[Blood bank name])
VALUES(1,'California Blood Center'), 
      (2,'Community Blood bank'),
      (3,'Delta Blood bank'),
      (4,'Houchin Valley Blood bank')

INSERT INTO [DBO].[Donation]([Donation ID],[Donor ID],[Amount Donated])
VALUES(1,1,510),(2,2,501),(3,3,353),(3,4,500),(3,5,500),(4,6,323),(4,6,700)



INSERT INTO  [DBO].[Transfusion Record]([Transfusion ID],[Blood bank name])
VALUES(1,'California Blood Center'), 
      (2,'Community Blood bank'),
      (3,'Delta Blood bank'),
      (4,'Houchin Valley Blood bank')

INSERT INTO [DBO].[Transfusion]([Transfusion ID],[Patient ID],[Amount Accepted])
VALUES(1,1,488),(2,2,212),(3,3,300),(3,4,519),(4,6,300),(4,6,444)

Original Query’s

SELECT dr.[Blood bank name], COUNT(dr.[Donation ID]) as 'number of donations', sum(d.[Amount Donated]) as 'blood drawn'

FROM [DBO].[Donation Record] as dr JOIN  [Donation] as d on dr.[Donation ID] = d.[Donation ID]
group by dr.[Blood bank name]


SELECT tr.[Blood bank name], COUNT(t.[Transfusion ID]) as 'number of donations', sum(t.[Amount Accepted]) as 'blood drawn'

FROM [DBO].[Transfusion Record] as tr JOIN  [Transfusion] as t on tr.[Transfusion ID] = t.[Transfusion ID]
group by tr.[Blood bank name]

Solution with CTE’s to subtract:

;WITH CTE AS
(
SELECT dr.[Blood bank name], COUNT(dr.[Donation ID]) as 'number of donations', sum(d.[Amount Donated]) as 'blood drawn'

FROM [DBO].[Donation Record] as dr JOIN  [Donation] as d on dr.[Donation ID] = d.[Donation ID]
group by dr.[Blood bank name]
),
CTE2 AS(
SELECT tr.[Blood bank name], COUNT(t.[Transfusion ID]) as 'number of donations', sum(t.[Amount Accepted]) as 'blood drawn'

FROM [DBO].[Transfusion Record] as tr JOIN  [Transfusion] as t on tr.[Transfusion ID] = t.[Transfusion ID]
group by tr.[Blood bank name]
)

SELECT CTE.[Blood bank name],  CTE.[blood drawn] - ISNULL(CTE2.[blood drawn],0) as  'blood left'
FROM CTE LEFT JOIN CTE2 on CTE.[Blood bank name] = CTE2.[Blood bank name]

I don’t think you need to subtract the number of donations.

Result

Blood bank name                                     blood left
California Blood Center                             22
Community Blood bank                                289
Delta Blood bank                                    534
Houchin Valley Blood bank                           279