Query that Returns Month to Date and YTD in Same Record

  • 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

  • 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

  • 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

  • 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.

  • 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,

  • It looks like this might work.

    Thanks again for your help!

  • Very good thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply