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
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)
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".
November 12, 2024 at 8:05 pm
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