SET DATEFIRST 6
SELECT
CUSTTRANS
.ACCOUNTNUM + REPLICATE(CUSTTRANS.ACCOUNTNUM, 10 - DATALENGTH(CUSTTRANS.ACCOUNTNUM)) + ’ - ’ + CUSTTABLE.NAME AS AccountTxt,
CAST(DATEPART(YY, CUSTTRANS.TRANSDATE) AS VARCHAR) + ’ - WEEK (’ + CAST(DATEPART(WK, CUSTTRANS.TRANSDATE) AS VARCHAR) + ‘)’ AS TrnDat,
DATEADD(dd, (DATEPART(dw,@TranDate)-1) * -1 , @TranDate) firstDay,
DATEADD(dd, 7-DATEPART(dw,@TranDate), @TranDate) lastDay,
SUM(CUSTTRANS.AMOUNTCUR) AS AmtCur
FROM
CUSTTRANS
INNER JOIN CUSTTABLE ON CUSTTRANS.ACCOUNTNUM = CUSTTABLE.ACCOUNTNUM
WHERE
(CUSTTRANS.AMOUNTMST < 0) AND (CUSTTRANS.TRANSDATE BETWEEN ‘2010-11-01’ AND ‘2010-11-30’)
GROUP
BY CUSTTRANS.ACCOUNTNUM + REPLICATE(CUSTTRANS.ACCOUNTNUM, 10 - DATALENGTH(CUSTTRANS.ACCOUNTNUM)) + ’ - ’ + CUSTTABLE.NAME,CUSTTRANS.TRANSDATE
ORDER BY TrnDat
I am new to Dynamics, please tell me how to use this query in an SSRS report.