Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

TSQL 2008 - write one row with multiple datea on the fly & combine updating new fields

Avatar of Leogal
LeogalFlag for United States of America asked on
Microsoft SQL Server
21 Comments1 Solution452 ViewsLast Modified:
I am struggling with fniding the right syntax & Code to ouput one row per loan number. I am still learning SQL coding & syntax so please forgive it this is a simple question.  I thought group by would resolve the issue,  but I am wrong.  Can someone help me understand what I am doing wrong please?  I can only get the data from the server master_file one time per the coding rules of my shop.

The second half of the Code is to update the fields that I have created on the select statement If there is a way to combine the two that would be very diserable.

IF OBJECT_ID('TEMPDB..#Temp1) IS NOT NULL
begin drop table #Temp1  end

select *

into      #Temp1
from openquery (aurfbd,
'
 SELECT distinct
  x.loan_number
 ,x.ls_Code
 ,x.ls_actual_completion_date  
 ,x.LS_SCHEDULED_COMPLETION_DATE
 ,cast(null as date) as  Code924_COMPL_DATE
 ,cast(null as date) as  Code926_COMPL_DATE
 ,cast(null as date) as  Code927_COMPL_DATE
 ,cast(null as date) as  Code928_COMPL_DATE
 ,cast(null as date) as  actual_compl_date
 ,cast(null as date) as  approval_date
 
     from  master_file x
      inner join  (select loan_number, MAX(ls_scheduled_completion_date)AS COMPL_DATE, MAX(ls_actual_completion_date)AS APPROVAL_DATE
                from master_file
                where  ls_Code in (''924'',''926'',''927'',''928'',''D08'',''H38'',''H79'',''H42'',  ''M40'',''M29'',''M10'',''M40'',''P31'',''P49'',''S17'')      
                               group by loan_number order by loan_number) y
                            on x.loan_number = y.loan_number
                                 and x.ls_scheduled_completion_date = y.compl_date
                                 and x.ls_actual_completion_date = y.approval_date

                                                                                      
for fetch only with ur ')
 
 UPDATE #LOSS_MIT_CODE
 set
   [CODE924_COMPL_DATE] = Case When (ls_Code  = '924') AND (ls_scheduled_completion_date <> '01/01/1900') Then ls_scheduled_completion_date else NULL End,
   [CODE926_COMPL_DATE] = Case When (ls_Code  = '926') AND (ls_scheduled_completion_date <> '01/01/1900') Then ls_scheduled_completion_date else NULL End,
   [CODE927_COMPL_DATE] = Case When (ls_Code  = '927') AND (ls_scheduled_completion_date <> '01/01/1900') Then ls_scheduled_completion_date else NULL End,
   [CODE928_COMPL_DATE] = Case When (ls_Code  = '928') AND (ls_scheduled_completion_date <> '01/01/1900') Then ls_scheduled_completion_date else NULL End,
   [ACTUAL_COMPL_DATE]  = Case When (ls_Code  = 'Q28') AND (ls_scheduled_completion_date <> '01/01/1900') Then ls_actual_completion_date else NULL End


ASKER CERTIFIED SOLUTION
Avatar of Lowfatspread
Commented:
This problem has been solved!
Unlock 1 Answer and 21 Comments.
See Answers