Get Sum and Last month sum

  • I want to get the sum and I want to get the sum of only last month for VenNum by FiYr and DocDte.  I am having trouble adding the sum of only the previous month.

    Select count(*) as cnt, T1.FiYr, T1.DocDte, T1.VenNum,  
    Sum(T1.DocCurrAmt) as DocCurrAmt
    From PH.[dbo].[PHTbl] T1
    Where DocTypeDesc Like '%Scan Base%' and T1.VenNum = '1234' ANd T1.FiYr = '2023' and T1.DocDte = '2023-02-20'
    Group by T1.FiYr, T1.DocDte, T1.VenNum
    Order by FiYr, T1.DocDte

    • This topic was modified 2 weeks, 6 days ago by  GrassHopper.
  • Without sample data and DDL it's not easy to provide working code.

    But in pseudo-code, something like this

    select sum(iif(date in last month), value, 0)

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Maybe?:

    Select count(*) as cnt, T1.FiYr, T1.DocDte, T1.VenNum,  
    Sum(T1.DocCurrAmt) as DocCurrAmt, Max(T2.DocMonthlySum) AS MonthlySum
    From dbo.table_name T1
    cross apply (
    Select sum(T2.DocCurrAmt) as DocMonthlySum
    From dbo.table_name T2
    Where T2.FiYr = T1.FiYr AND T2.VenNum = T1.VenNum AND
    T2.DocDte >= DATEADD(MONTH, DATEDIFF(MONTH, 0, '2023-02-20'), 0) AND T2.DocDte < DATEADD(MONTH, DATEDIFF(MONTH, 0, '2023-02-20') + 1, 0)
    ) as T2
    Where DocTypeDesc Like '%Scan Base%' And T1.VenNum = 1234 And T1.FiYr = '2023' And T1.DocDte = '2023-02-20'
    Group by T1.FiYr, T1.DocDte, T1.VenNum
    Order by FiYr, T1.DocDte

    • This reply was modified 2 weeks, 6 days ago by  ScottPletcher. Reason: Edit: Edited as requested
    • This reply was modified 2 weeks, 4 days ago by  ScottPletcher.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Scott, This is very helpful.  Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

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