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
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)
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
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
Scott, This is very helpful. Thanks!
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy