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 WalgreensCnlySapPaidHistory.[dbo].[CnlySapRawPaidHistory] T1
    Where DocTypeDesc Like '%Scan Base%' and T1.VenNum = '0001000005' ANd T1.FiYr = '2023' and T1.DocDte = '2023-02-20'
    Group by T1.FiYr, T1.DocDte, T1.VenNum
    Order by FiYr, T1.DocDte
  • 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
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Maybe?:

    Select count(*) as cnt, T1.FiYr, T1.DocDte, T1.VenNum,  
    Sum(T1.DocCurrAmt) as DocCurrAmt, Max(T2.DocMonthlySum) AS MonthlySum
    From WalgreensCnlySapPaidHistory.[dbo].[CnlySapRawPaidHistory] T1
    cross apply (
    Select sum(T2.DocCurrAmt) as DocMonthlySum
    From WalgreensCnlySapPaidHistory.[dbo].[CnlySapRawPaidHistory] 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 = '0001000005' ANd T1.FiYr = '2023' and T1.DocDte = '2023-02-20'
    Group by T1.FiYr, T1.DocDte, T1.VenNum
    Order by FiYr, T1.DocDte

    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