Trying To Arrange Sql Query To Properly Use Datediff()

I'm creating a query where I'm showing the different statuses a member has had, and how long they were in each status. This is what I've written so far: select memberid,

Solution 1:

Complete the pivot on each member by aggregating over memberid:

SELECT
    memberid,
    MAX(CASEWHEN statusname = 'Pending'THEN CAST(statusdate ASDATE) END) AS StatusDate1,
    MAX(CASEWHEN statusname = 'In Progress'THEN CAST(statusdate ASDATE) END) AS StatusDate2, 
    MAX(CASEWHEN statusname = 'Approved'THEN CAST(statusdate ASDATE) END) AS StatusDate3
INTO #test
FROM TableA
GROUPBY memberid

Solution 2:

use this query before applying your second query

select memberid, max(statusDate1) statusDate1, max(statusDate2) statusDate2, 
max(statusDate3) statusDate3
from
(
select memberid, 
    casewhen statusname = "Pending" thencast(statusdate asdate) endas StatusDate1,
    casewhen statusname ="In Progress" thencast(statusdate asdate) endas StatusDate2, 
    casewhen statusname = "Approved" thencast(statusdate asdate) endas StatusDate3
    into #test
    fromTable A
    groupby memberid, casewhen statusname = "Pending" thencast(statusdate asdate) end, 
    casewhen statusname ="In Progress" thencast(statusdate asdate) end,
    casewhen statusname = "Approved" thencast(statusdate asdate) end 
)
groupby memberid