007 007 - 3 months ago 14
SQL Question

Unique Count - TSQL

CODE

CREATE TABLE #TEMP (ID INT, AVAIL BIT, FK INT, DT DATETIME);
INSERT INTO #TEMP (ID,AVAIL,FK,DT)
SELECT 1,1,1,GETDATE()
UNION ALL
SELECT 2,0,2,GETDATE()
UNION ALL
SELECT 3,1,3,GETDATE()
UNION ALL
SELECT 1,1,4,GETDATE()
UNION ALL
SELECT 4,0,5,GETDATE()
UNION ALL
SELECT 5,1,6,GETDATE();


CREATE TABLE #FK (FK INT, DT2 DATETIME)
INSERT INTO #FK (FK, DT2)
SELECT 1,NULL
UNION
SELECT 2,DATEADD(DAY,1,GETDATE())
UNION
SELECT 3,DATEADD(DAY,1,GETDATE())
UNION
SELECT 4,NULL
UNION
SELECT 5,NULL
UNION
SELECT 6,DATEADD(DAY,1,GETDATE())
UNION
SELECT 7,DATEADD(DAY,1,GETDATE())


SELECT
[TotalIds] = COUNT(DISTINCT ID)
,[TotalAvail] = SUM(CASE WHEN [AVAIL] = 1 THEN 1 ELSE 0 END)
,[DTDIFF] = SUM(DATEDIFF(DAY,T1.DT,F.DT2))
FROM #TEMP T1 INNER JOIN #FK F
ON T1.FK = F.FK;

DROP TABLE #TEMP;
DROP TABLE #FK;


OUTPUT

TotalIds TotalAvail DTDIFF
5 4 3


DESIRED OUTPUT

TotalIds TotalAvail DTDIFF
5 3 3


GOAL:

I want to get
sum/count of UNIQUE IDs where [AVAIL] = 1.


I can do that by
COUNT(DISTINCT ID) WHERE [AVAIL] = 1
BUT... I need to do that within this SUM since I'm querying other data within the same query.

Desired output = 3
(for ID 1, 3, and 5).


Updated with Current/Desired output.
Updated with more data.

Answer
CREATE TABLE #TEMP (ID INT, AVAIL BIT, FK INT, DT DATETIME);
INSERT INTO #TEMP (ID,AVAIL,FK,DT)
SELECT 1,1,1,GETDATE()
UNION ALL
SELECT 2,0,2,GETDATE()
UNION ALL
SELECT 3,1,3,GETDATE()
UNION ALL
SELECT 1,1,4,GETDATE()
UNION ALL
SELECT 4,0,5,GETDATE()
UNION ALL
SELECT 5,1,6,GETDATE();


CREATE TABLE #FK (FK INT, DT2 DATETIME)
INSERT INTO #FK (FK, DT2)
SELECT 1,NULL
UNION
SELECT 2,DATEADD(DAY,1,GETDATE())
UNION
SELECT 3,DATEADD(DAY,1,GETDATE())
UNION
SELECT 4,NULL
UNION
SELECT 5,NULL
UNION
SELECT 6,DATEADD(DAY,1,GETDATE())
UNION
SELECT 7,DATEADD(DAY,1,GETDATE())


SELECT 
[TotalIds] = COUNT(DISTINCT ID)
,[TotalAvail] = COUNT(DISTINCT CASE WHEN [AVAIL] = 1 THEN ID ELSE NULL END)
,[DTDIFF] = SUM(DATEDIFF(DAY,T1.DT,F.DT2))
FROM #TEMP T1 INNER JOIN #FK F
    ON T1.FK = F.FK;


DROP TABLE #TEMP;
DROP TABLE #FK;