See the changes from new records, same records and old records

Transact-SQL
1

Hi,

I am looking at changes in data for two months and would like to see the changes include old records(in last month but not in this month), same records (in both months) and new records (in this month and not in last month).

I am doing calculations on these so if it returns a blank or null it would be good if it returned 0.

My current query picks up the mutual and new records but not the old records (i.e. the ones that have dropped off). Is there a way I can set my where the function to pick up the old records as well?

where reportDate in (@currentmonthend) OR (in(@previous monthEnd) AND NOT in (@currentmonth end).

My query looks like this:
declare @currentMonthend as date
declare @previousMonthEnd as date set @currentMonthend = '12/31/2022'
set @previousMonthEnd = eomonth(@currentMonthend, -1) select
cha.ID059Code
,cha.portfolio
,cha.securityDescription

,cha.accruedInterest,
(select chr.accruedInterest from tblPortCharacteristics chr
where chr.reportDate = @previousMonthEnd
and chr.ID059Code = cha.ID059Code
and chr.portfolio = cha.portfolio
and chr.securityDescription = cha.securityDescription) AS "Previous month Accrued"
,cha.accruedInterest -
(select chr.accruedInterest from tblPortCharacteristics chr
where chr.reportDate = @previousMonthEnd
and chr.ID059Code = cha.ID059Code
and chr.portfolio = cha.portfolio
and chr.securityDescription = cha.securityDescription) AS "change in accrued"
from dbo.tblPortCharacteristics cha
where cha.reportDate in(@currentMonthend) OR (in(@previousMonthEnd) AND not (@currentMonthend))

2

Hi

2 ways

Way1

data for last month
EXCEPT
data for this month

Way2

Data for last month
Left join
Data for this month
Where join column for this month is NULL

Same concept
For this month last month

3

hi

not sure what you mean by
t changes in data for two months and would like to see the changes include old records(in last month but not in this month), same records (in both months) and new records (in this month and not in last month).

please be specific
which column which table etc

DECLARE @currentMonthend as date

DECLARE @previousMonthEnd as date 
SET @currentMonthend = '12/31/2022'

SET @previousMonthEnd = eomonth(@currentMonthend, -1) 

SELECT
     cha.ID059Code
   , cha.portfolio
   , cha.securityDescription
   , sum(case when reportDate = @currentMonthend then cha.accruedInterest  end)
       - 
	 sum(case when reportDate = @previousMonthEnd then cha.accruedInterest  end)
	    as change_in_accrued 
FROM
   dbo.tblPortCharacteristics cha
GROUP BY
     cha.ID059Code
   , cha.portfolio
   , cha.securityDescription