

SSCAddicted
Group: General Forum Members
Last Login: Friday, September 4, 2015 9:31 AM
Points: 481,
Visits: 291


Hello, I have a query that returns a daily revenue figure. The query is as follows:
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
*********************************
TxnDate represents a single day of the month. How can I include MTD so that the dates for the revenue total are from DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) (beginning of current month) to TxnDate, and YTD so that the revenue totals are from DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) (beginning of the current year) to TxnDate?
Thank you for your help!
CSDunn




SSCAddicted
Group: General Forum Members
Last Login: Tuesday, July 7, 2015 10:24 AM
Points: 440,
Visits: 921


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




SSChampion
Group: General Forum Members
Last Login: Wednesday, July 20, 2016 7:24 AM
Points: 13,998,
Visits: 9,721


You'll need to run them separately, then combine them in a final query.
Perhaps something like:
;with Daily (DAcctCode, TotalDailyRevenue, DDay, DMonth, DYear) as (select AcctCode, isnull(sum(FuelFee), 0) + isnull(sum(CashFee), 0) + isnull(sum(ScFee), 0), datepart(day, TxnDate), datepart(month, TxnDate) datepart(year, TxnDate) from TxnRptg.dbo.tbl_DailySummary group by AcctCode, datepart(day, TxnDate), datepart(month, TxnDate) datepart(year, TxnDate)), Monthly (MAcctCode, TotalMonthlyRevenue, MMonth, MYear) as (select AcctCode, isnull(sum(FuelFee), 0) + isnull(sum(CashFee), 0) + isnull(sum(ScFee), 0), datepart(month, TxnDate) datepart(year, TxnDate) from TxnRptg.dbo.tbl_DailySummary group by AcctCode, datepart(month, TxnDate) datepart(year, TxnDate)), Yearly (YAcctCode, TotalYearlyRevenue, YYear) as (select AcctCode, isnull(sum(FuelFee), 0) + isnull(sum(CashFee), 0) + isnull(sum(ScFee), 0), datepart(year, TxnDate) from TxnRptg.dbo.tbl_DailySummary group by AcctCode, datepart(year, TxnDate)) select DAcctCode as AcctCode, TotalDailyRevenue, TotalMonthlyRevenue, TotalYearlyRevenue from Daily inner join Monthly on DAcctCode = MAcctCode and DMonth = MMonth and DYear = MYear inner join Yearly on DAcctCode = YAcctCode and MYear = YYear and DYear = YYear
Add in other tables for other columns to the Join clause.
Does that give you what you need? It'll take at least three table scans, but it shouldn't be too bad on performance.
 Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better."  Anon




SSCAddicted
Group: General Forum Members
Last Login: Friday, September 4, 2015 9:31 AM
Points: 481,
Visits: 291


with the CASE nested in the SUM, I got this:
Msg 130, Level 15, State 1, Line 5 Cannot perform an aggregate function on an expression containing an aggregate or a subquery.




SSCAddicted
Group: General Forum Members
Last Login: Tuesday, July 7, 2015 10:24 AM
Points: 440,
Visits: 921


cdun2 (2/4/2008) with the CASE nested in the SUM, I got this:
Msg 130, Level 15, State 1, Line 5 Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
sorry, i was trying to simplify sum(a)+sum(b)+sum(c) to sum(a+b+c). this should work. sum( case when ds.TxDate >= DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) then isnull(ds.FuelFee,0) + isnull(ds.CashFee,0) + isnull(ds.ScFee,0) else 0 end ) AS MTD,




SSCAddicted
Group: General Forum Members
Last Login: Friday, September 4, 2015 9:31 AM
Points: 481,
Visits: 291


It looks like this might work.
Thanks again for your help!




Forum Newbie
Group: General Forum Members
Last Login: Thursday, November 7, 2013 9:10 AM
Points: 1,
Visits: 29




