Calculate percentage of records that have an association in another table Table A                                           Table B
Columns                                           Columns
Performance Count                                  Key
Source                                             Record
Date
Key


I need the %age of Total Performance counts for a Source and Date that have a value in the record column of Table B

select sum(a.Performance Count) As TotalPerfCount, a.Source, a.Date,
(100 * b.Record / a.sum(a.PerformanceCount)) AS Percentage
from Table A a
JOIN Table B b ON a.key = b.key
where b.Record is not null
Group By a.TotalPerfCount, a.Source, a.Date


This isn't pulling all records that have a value in b.Record.  what am I doing wrong?
In order to calculate a percentage like that you need a count of rows in table b divided by a count of rows in table a.

Something like this:

USE tempdb;

DROP TABLE IF EXISTS dbo.b;
DROP TABLE IF EXISTS dbo.a;
GO

CREATE TABLE dbo.a
(
    a_id int NOT NULL
        CONSTRAINT a_pk
        PRIMARY KEY CLUSTERED
    , someData varchar(10) NOT NULL
    , rowDate datetime NOT NULL
);

CREATE TABLE dbo.b
(
    b_id int NOT NULL
        CONSTRAINT b_pk
        PRIMARY KEY CLUSTERED
    , a_id int NOT NULL
        CONSTRAINT b_a_id_fk
        FOREIGN KEY
        REFERENCES a(a_id)
    , someOtherData varchar(10) NOT NULL
);

INSERT INTO dbo.a (a_id, someData, rowDate)
VALUES (1, 'abcdefghij', GETDATE())
    , (2, 'abcdefghik', GETDATE());

INSERT INTO dbo.b (b_id, a_id, someOtherData)
VALUES (1, 1, 'blah');

;WITH a AS 
(
    SELECT cntA = COUNT(1)
    FROM dbo.a
)
, b AS 
(
    SELECT cntB = COUNT(1)
    FROM dbo.b
        INNER JOIN dbo.a ON b.a_id = a.a_id
)
SELECT a.cntA
    , b.cntB
    , [percentage] = (CONVERT(decimal(10, 2), b.cntB) / a.cntA) * 100
FROM a, b;


The output looks like:

╔══════╦══════╦══════════════════╗
║ cntA ║ cntB ║    percentage    ║
╠══════╬══════╬══════════════════╣
║    2 ║    1 ║ 50.0000000000000 ║
╚══════╩══════╩══════════════════╝

When you JOIN the rows, as in your example query, you're only going to see rows that match in both tables, since JOIN is really INNER JOIN.  If you use a LEFT JOIN instead, you'll get all rows from the table on the left side of the join, and only matching rows from the right side of the join.  Consider:

SELECT *
FROM dbo.a
    INNER JOIN dbo.b ON a.a_id = b.a_id;


Results:

╔══════╦════════════╦═════════════════════════╦══════╦══════╦═══════════════╗
║ a_id ║  someData  ║         rowDate         ║ b_id ║ a_id ║ someOtherData ║
╠══════╬════════════╬═════════════════════════╬══════╬══════╬═══════════════╣
║    1 ║ abcdefghij ║ 2019-12-06 07:07:33.340 ║    1 ║    1 ║ blah          ║
╚══════╩════════════╩═════════════════════════╩══════╩══════╩═══════════════╝

Note, we're only seeing a single row of results; rows that exist in both tables are returned by the query.

The left join version looks like:

SELECT *
FROM dbo.a
    LEFT JOIN dbo.b ON a.a_id = b.a_id;


Results:

╔══════╦════════════╦═════════════════════════╦══════╦══════╦═══════════════╗
║ a_id ║  someData  ║         rowDate         ║ b_id ║ a_id ║ someOtherData ║
╠══════╬════════════╬═════════════════════════╬══════╬══════╬═══════════════╣
║    1 ║ abcdefghij ║ 2019-12-06 07:07:33.340 ║ 1    ║ 1    ║ blah          ║
║    2 ║ abcdefghik ║ 2019-12-06 07:07:33.340 ║ NULL ║ NULL ║ NULL          ║
╚══════╩════════════╩═════════════════════════╩══════╩══════╩═══════════════╝

As you can see, this is return rows from both tables; where there is no row in b, we see NULL data.

You can capitalize on the left join to get you the results you want:

SELECT [Count_of_a] = COUNT(a.a_id)
    , [Count_of_b] = COUNT(b.a_id)
    , [Percentage] = CONVERT(decimal(10,2), COUNT(b.a_id)) / COUNT(a.a_id) * 100
FROM dbo.a
    LEFT JOIN dbo.b ON a.a_id = b.a_id;


Results:

╔════════════╦════════════╦═════════════════╗
║ Count_of_a ║ Count_of_b ║   Percentage    ║
╠════════════╬════════════╬═════════════════╣
║          2 ║          1 ║ 50.000000000000 ║
╚════════════╩════════════╩═════════════════╝

You can extend that to include a group-by on the date column.  But, first lets insert a couple of more rows in our sample tables:

INSERT INTO dbo.a (a_id, someData, rowDate)
VALUES (3, 'abcdefghil', GETDATE())
    , (4, 'abcdefghim', GETDATE());

INSERT INTO dbo.b (b_id, a_id, someOtherData)
VALUES (2, 1, 'blah')
    , (3, 4, 'blah');

SELECT *
FROM dbo.a;

SELECT *
FROM dbo.b;


Contents of both tables:

╔══════╦════════════╦═════════════════════════╗
║ a_id ║  someData  ║         rowDate         ║
╠══════╬════════════╬═════════════════════════╣
║    1 ║ abcdefghij ║ 2019-12-06 07:27:24.643 ║
║    2 ║ abcdefghik ║ 2019-12-06 07:27:24.643 ║
║    3 ║ abcdefghil ║ 2019-12-06 07:27:24.643 ║
║    4 ║ abcdefghim ║ 2019-12-06 07:27:24.643 ║
╚══════╩════════════╩═════════════════════════╝

╔══════╦══════╦═══════════════╗
║ b_id ║ a_id ║ someOtherData ║
╠══════╬══════╬═══════════════╣
║    1 ║    1 ║ blah          ║
║    2 ║    1 ║ blah          ║
║    3 ║    4 ║ blah          ║
╚══════╩══════╩═══════════════╝

The following query counts the distinct key values from each table, showing the percentage of rows that exist in table b compared to table a:

SELECT a.rowDate
    , a.someData
    , [Count_of_a] = COUNT(DISTINCT a.a_id)
    , [Count_of_b] = COUNT(DISTINCT b.b_id)
    , [Percentage] = CONVERT(decimal(10,2), COUNT(DISTINCT b.b_id)) / COUNT(DISTINCT a.a_id) * 100
FROM dbo.a
    LEFT JOIN dbo.b ON a.a_id = b.a_id
GROUP BY a.rowDate, a.someData;



Results:

╔═════════════════════════╦════════════╦════════════╦════════════╦═══════════════════╗
║         rowDate         ║  someData  ║ Count_of_a ║ Count_of_b ║    Percentage     ║
╠═════════════════════════╬════════════╬════════════╬════════════╬═══════════════════╣
║ 2019-12-06 07:27:24.643 ║ abcdefghij ║          1 ║          2 ║ 200.0000000000000 ║
║ 2019-12-06 07:27:24.643 ║ abcdefghik ║          1 ║          0 ║   0.0000000000000 ║
║ 2019-12-06 07:27:24.643 ║ abcdefghil ║          1 ║          0 ║   0.0000000000000 ║
║ 2019-12-06 07:27:24.643 ║ abcdefghim ║          1 ║          1 ║ 100.0000000000000 ║
╚═════════════════════════╩════════════╩════════════╩════════════╩═══════════════════╝

As you can see in the first row, there is a count of 2 rows in b, but only 1 row matching in a; hence we get a percentage value of 200%.  

The DISTINCT keywords inside each COUNT(...) aggregate ensure we only count unique values for the keys in a and b.  Without that keyword, SQL Server will count duplicates from a where more rows exist in b for the given a.a_id key value.