How would you write this SQL query without the use of a temp table?

Question

I am having difficulty running this query in Power Bi because it uses a temporary table. How would I write this without a temp table?

SELECT  Convert(varchar,TT1.POST_DATE,110) as 'POST_DATE',
        COUNT (DISTINCT TT1.TRAN_DATE) as 'NO_DAYS_ENTERED',
        HP4.EMPLOYEE_CODE,
        HP4.EMPLOYEE_NAME,
        HP4.EMPLOYEE_NAME + ' (' + LEFT(HB6.OFFC_DESC,3) + ')' as 'EMPLOYEE_NAME_2',
        TT1.OFFC,
        HB6.OFFC_DESC,
        HP4.GRAD_YEAR,
        TB1.RANK_CODE,
        TR1.RANK_DESC

into #tempG1
            
FROM    TAT_TIME TT1
        LEFT JOIN HBM_PERSNL HP4 ON TT1.TK_EMPL_UNO = HP4.EMPL_UNO 
        INNER JOIN TBM_PERSNL TB1 on HP4.EMPL_UNO = TB1.EMPL_UNO
        INNER JOIN TBL_RANK TR1 on TB1.RANK_CODE = TR1.RANK_CODE
        INNER JOIN HBL_OFFICE HB6 on HP4.OFFC = HB6.OFFC_CODE

WHERE   TB1.RANK_CODE IN ('4')
    AND HP4.INACTIVE = 'N'
    AND TT1.POST_DATE >= DATEADD(month, -3, GETDATE())

GROUP BY TT1.POST_DATE, HP4.EMPLOYEE_CODE, HP4.GRAD_YEAR, HP4.EMPLOYEE_NAME, TT1.OFFC, TB1.RANK_CODE, TR1.RANK_DESC, HB6.OFFC_DESC

select G1.EMPLOYEE_NAME,
        G1.EMPLOYEE_NAME_2,
       datediff(day, min(G1.POST_DATE), max(G1.POST_DATE)) * 1.0 / nullif(count(*) - 1, 0) as 'AVG_FREQ'
from #tempG1 G1
group by G1.EMPLOYEE_NAME, G1.EMPLOYEE_NAME_2

drop table #tempG1

Answer 1

You need to refer query of #tempG1 as an inline view in your main SQL. Try this -

select G1.EMPLOYEE_NAME,
        G1.EMPLOYEE_NAME_2,
       datediff(day, min(G1.POST_DATE), max(G1.POST_DATE)) * 1.0 / nullif(count(*) - 1, 0) as 'AVG_FREQ'
from (SELECT  Convert(varchar,TT1.POST_DATE,110) as 'POST_DATE',
        COUNT (DISTINCT TT1.TRAN_DATE) as 'NO_DAYS_ENTERED',
        HP4.EMPLOYEE_CODE,
        HP4.EMPLOYEE_NAME,
        HP4.EMPLOYEE_NAME + ' (' + LEFT(HB6.OFFC_DESC,3) + ')' as 'EMPLOYEE_NAME_2',
        TT1.OFFC,
        HB6.OFFC_DESC,
        HP4.GRAD_YEAR,
        TB1.RANK_CODE,
        TR1.RANK_DESC

            
FROM    TAT_TIME TT1
        LEFT JOIN HBM_PERSNL HP4 ON TT1.TK_EMPL_UNO = HP4.EMPL_UNO 
        INNER JOIN TBM_PERSNL TB1 on HP4.EMPL_UNO = TB1.EMPL_UNO
        INNER JOIN TBL_RANK TR1 on TB1.RANK_CODE = TR1.RANK_CODE
        INNER JOIN HBL_OFFICE HB6 on HP4.OFFC = HB6.OFFC_CODE

WHERE   TB1.RANK_CODE IN ('4')
    AND HP4.INACTIVE = 'N'
    AND TT1.POST_DATE >= DATEADD(month, -3, GETDATE())

GROUP BY TT1.POST_DATE, HP4.EMPLOYEE_CODE, HP4.GRAD_YEAR, HP4.EMPLOYEE_NAME, TT1.OFFC, TB1.RANK_CODE, TR1.RANK_DESC, HB6.OFFC_DESC
) G1
group by G1.EMPLOYEE_NAME, G1.EMPLOYEE_NAME_2