Post reply

Get Sum and Last month sum

  • November 12, 2024 at 11:30 am

    Go to Answer

    #4480534

    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

  • November 12, 2024 at 11:36 am

    #4480545

    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

  • November 12, 2024 at 2:20 pm

    Answer

    #4480595

    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 1 month ago by  ScottPletcher. Reason: Edit: Edited as requested
    • This reply was modified 1 month 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".

  • November 12, 2024 at 8:05 pm

    #4480777

    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