SELECT top 1000
ds.AcctCode,
ds.TxnDate,
SUM(isnull(ds.FuelFee,0)) + SUM(isnull(ds.CashFee,0)) + SUM(isnull(ds.ScFee,0)) AS TotalDailyRevenue,
--"MTD" = ?,
--"YDT" = ?,
ps.TC,
CASE
WHEN ps.Proj = 100 THEN 'New Account'
WHEN ps.Proj = 200 THEN 'Current Account'
END AS ProjStatus,
ps.FSR,
ps.SubmitRep1
FROM
TxnRptg.dbo.tbl_DailySummary ds
INNER JOIN SalesData.dbo.tbl_CYProcessedSales ps
ON ds.AcctCode = ps.Acct
WHERE
MONTH(ds.TxnDate) = 1
AND
Proj IN (100,200)
AND TC = 'HV'
GROUP BY
ds.AcctCode, ds.TxnDate, ps.TC, ps.Proj, ps.FSR, ps.SubmitRep1
ORDER BY
ds.AcctCode, ds.TxnDate
try this.
SELECT
ds.AcctCode,
sum( case when ds.TxDate >= DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
then (isnull(ds.FuelFee,0)) + SUM(isnull(ds.CashFee,0)) + SUM(isnull(ds.ScFee,0))
else 0 ) AS MTD,
SUM(isnull(ds.FuelFee,0) + isnull(ds.CashFee,0) + isnull(ds.ScFee,0)) AS YTD,
ps.TC,
CASE
WHEN ps.Proj = 100 THEN 'New Account'
WHEN ps.Proj = 200 THEN 'Current Account'
END AS ProjStatus,
ps.FSR,
ps.SubmitRep1
FROM
TxnRptg.dbo.tbl_DailySummary ds
INNER JOIN SalesData.dbo.tbl_CYProcessedSales ps
ON ds.AcctCode = ps.Acct
WHERE
Proj IN (100,200)
AND TC = 'HV'
AND ds.TxnDate >= DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
GROUP BY
ds.AcctCode, ps.TC, ps.Proj, ps.FSR, ps.SubmitRep1
ORDER BY
ds.AcctCode