Get Sum and Last month sum

• GrassHopper

  SSCarpal Tunnel

  Points: 4473

  More actions

  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 
  

  • This topic was modified 6 months, 3 weeks ago by GrassHopper.

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  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)

  ---

• ScottPletcher

  SSC Guru

  Points: 101192

  More actions

  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 6 months, 3 weeks ago by ScottPletcher. Reason: Edit: Edited as requested
  • This reply was modified 6 months, 3 weeks 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".

• GrassHopper

  SSCarpal Tunnel

  Points: 4473

  More actions

  November 12, 2024 at 8:05 pm

  #4480777

  Scott, This is very helpful.  Thanks!