asked on

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

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

HainKurt

please give some sample data and the result that you are looking for...

dqmq

Add these fields to the group by clause:

,x.ls_Code
,x.ls_actual_completion_date
,x.LS_SCHEDULED_COMPLETION_DATE

For a given loan_numer, they are always the same, right?

Leogal

ASKER

Hi HainKurt:, I have attached a file with sample data.

I do see one typo on my part in the initial question, the last statement in update should read:
[ACTUAL_COMPL_DATE] = Case When (ls_step_code = 'Q28') AND (ls_actual_completion_date <> '01/01/1900') Then ls_actual_completion_date else NULL End
test-data-for-sql-select---combi.xls

Leogal

ASKER

dgmg> that is correct any given loan could have all of the fields or only one.

Leogal

ASKER

I have added the following to my group by and I am still getting multiple entries per loan...

Leogal

ASKER

hit enter to fast. The group by statement is now : group by x.loan_number, x.code,
,x.ls_actual_completion_date , ,x.LS_SCHEDULED_COMPLETION_DATE- still getting multiple entries when I want only one per loan with fields populated that meet the criteria...

Leogal

ASKER

I thought I had figured this out but had not done so. I am removing the request to delete the comment.

dqmq

the reason you are getting multiple rows is because you have more than one ls_code for the same loan number. To see what I mean, remove ls_code from the select list and the group by.

Now the question is back at you: if there are multiple ls_codes for your loan_number, which one do you want to see in the results?

Lowfatspread

shouldn't it just be this?

select *

into      #Temp1
from openquery (aurfbd,
'
 SELECT 
  x.loan_number
 ,x.ls_actual_completion_date  
 ,x.LS_SCHEDULED_COMPLETION_DATE
 ,max(Case When ls_Code  = '924' AND ls_scheduled_completion_date <> '01/01/1900'
           Then ls_scheduled_completion_date
           else Date(NULLIF('01/01/1900','01/01/1900')) End) as  Code924_COMPL_DATE 
 ,max(Case When ls_Code  = '926' AND ls_scheduled_completion_date <> '01/01/1900'
           Then ls_scheduled_completion_date
           else Date(NULLIF('01/01/1900','01/01/1900')) End) as  Code926_COMPL_DATE 
 ,max(Case When ls_Code  = '927' AND ls_scheduled_completion_date <> '01/01/1900'
           Then ls_scheduled_completion_date
           else Date(NULLIF('01/01/1900','01/01/1900')) End) as  Code927_COMPL_DATE 
 ,max(Case When ls_Code  = '928' AND ls_scheduled_completion_date <> '01/01/1900'
           Then ls_scheduled_completion_date
           else Date(NULLIF('01/01/1900','01/01/1900')) End) as  Code928_COMPL_DATE 
 ,max(Case When ls_Code  = '924' AND ls_actual_completion_date <> '01/01/1900'
           Then ls_actual_completion_date
           else Date(NULLIF('01/01/1900','01/01/1900')) End) as  actual_compl_date
 ,max(date(nullif('01/01/1900','01/01/1900'))) 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
group by x.loan_number,x.ls_actual_completion_date,ls+scheduled+completion_date
                                                                                      
for fetch only with ur ')
  
select * from #temp1
order by loan_number

Open in new window

Lowfatspread

are you sure you want WITH UR?

Lowfatspread

it may also assist if you attach this to the DB2 zone since your base query is runniong against a db2 database...

Leogal

ASKER

lowfatspread: the repeat of these lines: ,max(Case When ls_Code = '924' AND ls_actual_completion_date <> '01/01/1900'
Then ls_actual_completion_date
should that be: ,max(Case When ls_Code not in( '924', '92'6','927','928') AND ls_actual_completion_date <> '01/01/1900'
Then ls_actual_completion_date else Date(NULLIF('01/01/1900','01/01/1900')) End) as actual_compl_date

Leogal

ASKER

Lowfatspread, yes I need to leave the state for fetch only with ur;') this is a shop standard.

Leogal

ASKER

dgmg all codes must be integrated with a single row for each loan as applicable.

Leogal

ASKER

Thanks to LowFatSpread I am glad to say I am now getting one row per loan with the appropriate date fields populated. I have made small changes and it working!

select *

into #Temp1
from openquery (aurfbd,
'
SELECT
x.loan_number
,x.ls_actual_completion_date
,x.LS_SCHEDULED_COMPLETION_DATE
,max(Case (When ls_Code = '924' AND ls_scheduled_completion_date <> '01/01/1900'
Then ls_scheduled_completion_date
else NULL) End) as Code924_COMPL_DATE
,max(Case When ls_Code = '926' AND ls_scheduled_completion_date <> '01/01/1900'
Then ls_scheduled_completion_date
else NULL) End) as Code926_COMPL_DATE
,max(Case When ls_Code = '927' AND ls_scheduled_completion_date <> '01/01/1900'
Then ls_scheduled_completion_date
else NULL) End) as Code927_COMPL_DATE
,max(Case When ls_Code = '928' AND ls_scheduled_completion_date <> '01/01/1900'
Then ls_scheduled_completion_date
else NULL) End) as Code928_COMPL_DATE
,max(Case When ls_Code = '924' AND ls_actual_completion_date <> '01/01/1900'
Then ls_actual_completion_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
group by x.loan_number,x.ls_actual_completion_date,ls+scheduled+completion_date

for fetch only with ur ')

The only issue I have left is the DB2 error I am receiving: I am getting this error: [OLE/DB provider returned message: [IBM][CLI Driver] [DB2 / linuxx8664] SQL0206N "LS_SCHEDULED_DATE" is not valid in the context where used. SQLSTATE=42703) OLE DB error trace [OLE /DB Provider 'MSDASQL' IColumnsInfo: GetColumnsInfo returned 0x80004005: Msg7339, Level 1, State 1, Line 83 OLE DB provider 'MSDASQL' reported an error.

I have added a DB2 tag to this question

Leogal

ASKER

Nope I am wrong, there is still multiple rows per loan...

Lowfatspread

can you confirm what sql you actually ran you last example is in error...

was it something like this?

how many duplicates do you get? (per loan)

(have you dropped the temp table between runs?)

select *

into      #Temp1
from openquery (aurfbd,
'
 SELECT 
  x.loan_number
 ,x.ls_actual_completion_date  
 ,x.LS_SCHEDULED_COMPLETION_DATE
 ,max(Case (When ls_Code  = '924' AND ls_scheduled_completion_date <> '01/01/1900'
           Then ls_scheduled_completion_date
           else NULL) End) as  Code924_COMPL_DATE 
 ,max(Case When ls_Code  = '926' AND ls_scheduled_completion_date <> '01/01/1900'
           Then ls_scheduled_completion_date
          else NULL) End) as  Code926_COMPL_DATE 
 ,max(Case When ls_Code  = '927' AND ls_scheduled_completion_date <> '01/01/1900'
           Then ls_scheduled_completion_date
            else NULL) End) as  Code927_COMPL_DATE 
 ,max(Case When ls_Code  = '928' AND ls_scheduled_completion_date <> '01/01/1900'
           Then ls_scheduled_completion_date
             else NULL) End) as  Code928_COMPL_DATE 
 ,max(Case When ls_Code  = 'Q28' AND ls_actual_completion_date <> '01/01/1900'
           Then ls_actual_completion_date else null end as actual_compl_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
group by x.loan_number,x.ls_actual_completion_date,ls_scheduled_completion_date
                                                                                      
for fetch only with ur ')

Open in new window

dqmq

That means for a given loan_number at least one of the other group by fields contains multiple values. So then, the question falls to you: what do you want to see in the x.code field when multiple values exist for the same loan. Same question for the other two fields.

For example, you might say:
return highest values. In that case, use the max(field_name) in the select list and remove from the group by.

Leogal

ASKER

LowFatSpread: Yes my code is that in your last post. The problem is that I am getting an error now:
SQL0104N An unexpected token "=" was found following "(When ls_code". Expected tokens may include : "<join_type_without_spec> JOIN <join_operand>". SQLSTATE=42601

Leogal

ASKER

dgmg the returned results should be like this: (where actual_completion_date = date 1 & ls_scheduled_completion_date = date2)

loan date1 date2 924date, 926date, 927date 928date, Actual_Compl_Date
001 9/12/2008 9/13/2008 9/132008 9/12/2008
002 05/12/2009 5/12/2009 5/12/2009 5/12/2009

ASKER CERTIFIED SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

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