March 5, 2018 at 2:31 pm
I have a result set that is a Difference Report for changes on a contract. I have some columns that are decimal(18,6) and some that are txt that I have had to make a Diff column based on a case statement where they are not equal. What I need help figuring out is how can I see if
All of the Difference columns are > 1 (meaning there is a change someone) and only show those.
I was thinking if all of the Current - Prior = 0 then add all of those type of columns and if they are > 1 then show that record but it could be any of The 7 columns in question that should show the column
If(OBJECT_ID('tempdb..#temp123') Is Not Null)
Begin
Drop table #Temp123
End
Create Table #temp123 (
PKEY nvarchar(13)
,cmBase decimal(18,6)
,pmBase decimal(18,6)
,cmFrt decimal(18,6)
,pmFrt decimal(18,6)
,cmZone nvarchar(3)
,pmZone nvarchar(3)
,cmMon nvarchar(6)
,pmMon nvarchar(6)
)
insert into #Temp123 values
('614-0204473-S', '0.800000', '0.830000', '0.000000', '0.000000', 'DTA', 'MNA', '201803', '201803'),
('614-0204474-S','0.715000', '0.800000', '0.000000', '0.000000', 'DTA', 'DTA', '201805', '201803'),
('614-0204677-S','-0.100000','-0.130000', '0.000000', '0.000000', 'CAP', 'CAP', '201803', '201803'),
('614-0204678-S','-0.100000','-0.160000', '0.000000', '0.000000', 'CAP', 'CAP', '201803', '201803'),
('614-0204697-S','-0.110000','-0.130000', '0.000000', '0.000000', 'CAP', 'CRP', '201803', '201803'),
('614-0204698-S','-0.110000','-0.110000', '0.000000', '0.000000', 'CAP', 'CAP', '201803', '201803'),
('614-0204699-S','-0.110000','-0.110000', '0.000000', '0.200000', 'CAP', 'CAP', '201803', '201803'),
('614-0204700-S','-0.110000','-0.110000', '0.000000', '0.000000', 'CAP', 'CAP', '201803', '201803'),
('614-0204701-S','-0.110000','-0.120000', '0.000000', '0.000000', 'CAP', 'CAP', '201803', '201803'),
('614-0204756-S','-0.110000','-0.110000', '0.000000', '0.200000', 'CAP', 'CPA', '201803', '201803'),
('614-0204757-S','-0.110000','-0.120000', '0.000000', '0.000000', 'CAP', 'CAP', '201803', '201803'),
('614-0204758-S','-0.110000','-0.110000', '0.000000', '0.000000', 'CAP', 'CAP', '201803', '201803'),
('614-0204759-S','-0.110000','-0.140000', '0.000000', '0.100000', 'CAP', 'CMC', '201803', '201803'),
('614-0204760-S','-0.110000','-0.110000', '0.000000', '0.000000', 'CAP', 'CAP', '201803', '201803')
Select Pkey, cmBase , pmBase, cmBase - pmBase as DiffBase,
CmFrt, PMFrt, cmFrt - pmFrt as DiffFrt,
cmZone, pmZone, case when cmZone <> pmZone then 1 else 0 end as DiffZone,
cmMon, pmMon, case when cmMon <> pmMon then 1 else 0 end as DiffMon
from #temp123
Desired Results. Only the records where all of the Diff columns 0 or 0.0000000
March 5, 2018 at 3:52 pm
Those requirements seem contradictory to me.
What I need help figuring out is how can I see if
All of the Difference columns are > 1 (meaning there is a change someone) and only show those.
Desired Results. Only the records where all of the Diff columns 0 or 0.0000000
Hmm, which is it? Or am I reading that wrong?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 5, 2018 at 6:00 pm
is the Diff columns in the select statement there is an easy to read column for 0 or 0.00000 for decimal. If there are no changes in the columns across the records they don't want to see the result, only want to see if there are changes in 1 or more of those diff column.
So if there was a difference only in the Base column for Diff Base then they want to see the entire record but for the record with the pkey as '614-0204700-S' there are no changes across the different groups of columns so all of the Diff columns are showing no changes. They would not want to see this record, thus I need to be able to filter out records with no changes across all of the Diff columns like the record mentioned previously
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply