Hi All,
I am trying to convert following Oracle query to SQL Server and having issues in defining calculation in the BETWEEN of the PARTITION.
ORACLE Query:
SELECT
head.acct_unit, head.company,head.role_id,
AVG(head.HEADCOUNT) over
(partition by head.acct_unit, head.company,head.role_id
order by head.posting_month
range between head.posting_month - trunc(head.posting_month,'YYYY') PRECEDING and current row) YTD_HEADCOUNT
FROM
TABLE_A head
SQL Server:
SELECT
HEAD.ACCT_UNIT, HEAD.COMPANY,HEAD.ROLE_ID,
AVG(HEAD.HEADCOUNT) OVER
(PARTITION BY HEAD.ACCT_UNIT, HEAD.COMPANY,HEAD.ROLE_ID
ORDER BY HEAD.POSTING_MONTH
-- RANGE BETWEEN DATEDIFF(dd,DATEADD(yy, DATEDIFF(yy,0,HEAD.POSTING_MONTH), 0),HEAD.POSTING_MONTH)
-- PRECEDING AND CURRENT ROW
)
FROM
TABLE_A head
Any help in this regard is highly appreciated.
Regards,
Shijith