我很难在powerbi中运行这个查询,因为它使用了一个临时表。没有临时表我怎么写这个?
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
1条答案
按热度按时间lh80um4z1#
您需要在主sql中将#tempg1查询作为内联视图引用。试试这个-