troubleshooting Question

Create new description column based on differences in 2 views

Avatar of flscooter
flscooter asked on
Microsoft SQL ServerMicrosoft SQL Server 2005SQL
5 Comments1 Solution186 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros