IF OBJECT_ID('tempdb.dbo.#temptable1') IS NOT NULL
    DROP TABLE #temptable1

IF OBJECT_ID('tempdb.dbo.#temptable2') IS NOT NULL
    DROP TABLE #temptable2

SELECT DISTINCT [Service_Date]
    ,[Member_Patient_ID]
    ,[Member_Name]
    ,[Pharmacy_Rx_Number]
    ,[Pharmacy_NPI]
    ,[Pharmacy_Name]
    ,[Pharmacy_Physical_State]
    ,[Prescriber_NPI]
    ,[Prescriber_Name]
    ,[Prescriber_Primary_Specialty]
    ,[Prescriber_Physical_State]
    ,[Dispensable_Name]
    ,[Quantity_Dispensed]
    ,[Days_Supply]
    ,[Patient_Paid]
    ,[Plan_Paid]
    ,[Claim_Number]
    ,[ICN]
    ,[LOB_ID]
    ,[Date_Written]
    ,ROW_NUMBER() OVER (
        PARTITION BY [Member_Patient_ID] ORDER BY [Pharmacy_Physical_State]
        ) row_num
INTO #TempTable1
FROM [AbcaMstrDDS].[data].[PBM_Utilization]
WHERE [Service_Date] BETWEEN '20190707'
        AND '20190807'
    AND Member_Name <> 'Unknown'
--GROUP BY [Member_Patient_ID]
--  ,[Member_Name]
--  ,[Pharmacy_Physical_State]
--  ,[Service_Date]
ORDER BY [Member_Patient_ID];

SELECT  DISTINCT [Member_Patient_ID], [Pharmacy_Physical_State]
INTO #TempTable2
FROM #TempTable1
WHERE Member_Patient_ID = '2536328'
--'2536328'
--AND row_num > 2
--GROUP BY Pharmacy_Physical_State

SELECT *
FROM #TempTable2 t2
JOIN #TempTable1 t1 on t2.[Member_Patient_ID] = t1.[Member_Patient_ID]
AND t2.[Pharmacy_Physical_State] = t1.[Pharmacy_Physical_State]
ORDER BY row_num