Avatar of flscooter
flscooter asked on

Create new description column based on differences in 2 views

Experts,

I have 2 views I have created of the same table, one view contains last month's data and one view contains the current month's data ( Meaning the Statement Date contains different values for each view ).   The previous month's report to my user showed about 10 fields for each view, side by side, as such:
Last Month Acct #      This Month Acct #        Last Month Transaction Cd    This Month Transaction Cd

and so on, with 10 or more fields side by side with the Previous Month's data first.  In this way, the user could browse the report and see what changes, if any,  a particular account had.  It appears that the user now wants this report rerun, but with the result set displayed differently.  Now they want only this month's fields ( no display of last month's data ) and one added field which displays the field name(s) of columns which changed from last month.  Currently, I am comparing each field that we are reporting on
( for selected records ) where the values had changed from last month to this.  Of course, multiple fields can change in any particular account ( record ) from month to month, so a given account could be on the report multiple times, with a different entry in the new "What Changed" column.   I have tried this with the case statement and if statement, but it is not working as I would like.  Any help would be appreciated.  I am attaching the old code I need transformed.  Thanks.

Scott
select a.GRP_BANK_NO, 
       b.GRP_BANK_NO, 
       a.GRP_ACCT_NO,
       b.GRP_ACCT_NO, 
       a.Inst_Nbr,
       b.Inst_Nbr,
       a.Acct_No,  
       b.Acct_No,  
       a.ACCT_NAME, 
       b.ACCT_NAME, 
       a.BRANCH_NO, 
       b.BRANCH_NO, 
       a.PRICE_LIST_NBR,
       b.PRICE_LIST_NBR,
       a.AA_STMT_TYPE,
       b.AA_STMT_TYPE,
       a.ACCT_TYPE,
       b.ACCT_TYPE,
       a.User_Code_2, 
       b.User_Code_2, 
       a.SERCHG_CODE,
       b.SERCHG_CODE,
       a.RECORD_CODE,
       b.RECORD_CODE,
       a.ACCT_STATUS,
       b.ACCT_STATUS,
       a.DORM_INACT,
       b.DORM_INACT,
       a.Stmt_Date, 
       b.Stmt_Date
--       a.Acct_Desg,
--       b.Acct_Desg,    
from  dbo.vwIDDA_Last_Month a, dbo.vwIDDA_This_Month b
where  a.acct_no = b.acct_no
and    a.inst_nbr = b.inst_nbr
and    a.record_code = b.record_code
and    a.User_Code_2 in ('1', '2', '3', '4')
and    b.User_Code_2 in ('1', '2', '3', '4')  
and    (a.GRP_BANK_NO <> b.grp_bank_no
or      a.GRP_ACCT_NO <> b.GRP_ACCT_NO
or      a.ACCT_NAME   <> b.ACCT_NAME
or      a.BRANCH_NO   <> b.BRANCH_NO
or      a.PRICE_LIST_NBR <> b.PRICE_LIST_NBR
or      a.AA_STMT_TYPE <> b.AA_STMT_TYPE
or      a.ACCT_TYPE    <> b.ACCT_TYPE
or      a.User_Code_2  <> b.User_Code_2
or      a.SERCHG_CODE  <> b.SERCHG_CODE
or      a.RECORD_CODE  <> b.RECORD_CODE
or      a.ACCT_STATUS  <> b.ACCT_STATUS
or      a.DORM_INACT   <> b.DORM_INACT)
order by b.GRP_BANK_NO, b.GRP_ACCT_NO, a.INST_NBR, b.ACCT_NO

Open in new window

Microsoft SQL ServerMicrosoft SQL Server 2005SQL

Avatar of undefined
Last Comment
Mark Wills

8/22/2022 - Mon
Daniel Wilson

How's this?
select a.GRP_BANK_NO, 
       a.GRP_ACCT_NO,
       a.Inst_Nbr,
       a.Acct_No,  
       a.ACCT_NAME, 
       a.BRANCH_NO, 
       a.PRICE_LIST_NBR,
       a.AA_STMT_TYPE,
       a.ACCT_TYPE,
       a.User_Code_2, 
       a.SERCHG_CODE,
       a.RECORD_CODE,
       a.ACCT_STATUS,
       a.DORM_INACT,
       a.Stmt_Date, 
--       a.Acct_Desg,
--       b.Acct_Desg,   
WhatChanged = Case When a.GRP_BANK_NO <> b.grp_bank_no Then 'Grp_Bank_No'
   When a.GRP_ACCT_NO <> b.GRP_ACCT_NO then 'Grp_Acct_No'
   When a.ACCT_NAME   <> b.ACCT_NAME then 'Acct_Name'
   --etc.
   End
from  dbo.vwIDDA_Last_Month a, dbo.vwIDDA_This_Month b
where  a.acct_no = b.acct_no
and    a.inst_nbr = b.inst_nbr
and    a.record_code = b.record_code
and    a.User_Code_2 in ('1', '2', '3', '4')
and    b.User_Code_2 in ('1', '2', '3', '4')  
and    (a.GRP_BANK_NO <> b.grp_bank_no
or      a.GRP_ACCT_NO <> b.GRP_ACCT_NO
or      a.ACCT_NAME   <> b.ACCT_NAME
or      a.BRANCH_NO   <> b.BRANCH_NO
or      a.PRICE_LIST_NBR <> b.PRICE_LIST_NBR
or      a.AA_STMT_TYPE <> b.AA_STMT_TYPE
or      a.ACCT_TYPE    <> b.ACCT_TYPE
or      a.User_Code_2  <> b.User_Code_2
or      a.SERCHG_CODE  <> b.SERCHG_CODE
or      a.RECORD_CODE  <> b.RECORD_CODE
or      a.ACCT_STATUS  <> b.ACCT_STATUS
or      a.DORM_INACT   <> b.DORM_INACT)
order by b.GRP_BANK_NO, b.GRP_ACCT_NO, a.INST_NBR, b.ACCT_NO

Open in new window

ASKER
flscooter

DanielWilson -
I am sorry but my system is down for the afternoon.  I will check back with you tomorrow.  Thanks for responding.

Scott
ASKER CERTIFIED SOLUTION
Mark Wills

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
flscooter

Mark,
Your solution was spot on, exactly what I needed.  Thanks so much for your help.  Could you please explain a little bit more about the else statement after the case statements you added.  It appears you are saying to print certain verbage based on certain criteria.  If the criteria is not true, put nulls in the new Changes field for that condition.  Is that kind of what is going on here?  Thanks.

Scott
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Mark Wills

Thanks, and apologies for the poor typing - just noticed...

Yes, that is what is happening, it is basically concatenating the results from a series of case statements into a single output column. Each case statement is checking if a particular column has changed, and if so, put in a description else put in a zero length string (which is different to a null).

Should also be checking the individual columns for nulls. Null can cause undersirable results when checking and comparing values. So, for example, a more correct "case" statement would be:

       case when isnull(a.GRP_BANK_NO,0) <> isnull(b.grp_bank_no,0) then 'grp_bank_no ' else '' end +

but need to match the "type" of column so the above if not a number, should be: isnull(a.GRP_BANK_NO,'')   using that zero length string, and for dates, use '01/01/1900'. the isnull function basically says "if my value is NULL then use 'this' instead" - very handy feature.

Fixed the typing for you as well (which you probably have already done...)

       case when a.GRP_BANK_NO <> b.grp_bank_no then 'grp_bank_no ' else '' end +
        case when a.GRP_ACCT_NO <> b.GRP_ACCT_NO then 'grp_acct_no ' else '' end +
        case when a.ACCT_NAME   <> b.ACCT_NAME then 'acct_name ' else '' end +
        case when a.BRANCH_NO   <> b.BRANCH_NO then 'branch_no ' else '' end +
        case when a.PRICE_LIST_NBR <> b.PRICE_LIST_NBR then 'price_list_no ' else '' end +
        case when a.AA_STMT_TYPE <> b.AA_STMT_TYPE then 'AA_STMT_TYPE ' else '' end +
        case when a.ACCT_TYPE    <> b.ACCT_TYPE then 'ACCT_TYPE ' else '' end +
        case when a.User_Code_2  <> b.User_Code_2 then 'User_Code_2 ' else '' end +
        case when a.SERCHG_CODE  <> b.SERCHG_CODE then 'SERCHG_CODE ' else '' end +
        case when a.RECORD_CODE  <> b.RECORD_CODE then 'RECORD_CODE ' else '' end +
        case when a.ACCT_STATUS  <> b.ACCT_STATUS then 'ACCT_STATUS ' else '' end +
        case when a.DORM_INACT   <> b.DORM_INACT then 'DORM_INACT ' else '' end  as Changes