Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Selecting all from one table and merge with current query

Proposed Selecting all from one table and merge with current query

  • Monday, March 04, 2013 7:56 PM
     
      Has Code
    Sign In to Vote
    0

    I am needing to add to this query every asset No_ from the asset table and then merge with the data, for example if i had assets that did not have a Sales invoice the aqsset would not be brought back in the result set

    thanks

    -- get revenue
insert into #results select a.[Asset Serial No_], b.[ Size], b.[ Type], b.[Item No_], CONVERT(varchar, c.[Service Date], 111), 
 a.[ Transaction Type], '', a.[Line Amount], a.[Shortcut Dimension 2 Code], b.[Product Group], '', b.[Asset Aq_ Value] ,b.[Acq_ Date]  from 
[Chronus$Sales Invoice Line] as a join 
[Chronus$Asset] as b on b.[Serial No_] = a.[Asset Serial No_] join 
[Chronus$Sales Invoice Header] as c on c.[No_] = a.[Document No_]  
where c.[Service Date] between @begdate AND @enddate
and a.[Line Amount] <> 0 


update #results set TransType = '' where TransType = 0
update #results set TransType = 'Credit' where TransType = 1
update #results set TransType = 'DBR Charge' where TransType = 2
update #results set TransType = 'LIH Charge' where TransType = 3
update #results set TransType = 'Rental' where TransType = 4
update #results set TransType = 'Repair Charge' where TransType = 5
update #results set TransType = 'Sale' where TransType = 6



---- get expense
insert into #results select a.[Asset Serial No_], b.[ Size], b.[ Type], b.[Item No_], CONVERT(varchar, a.[Date], 111), 0, c.[TransType], 
a.[Amount] * -1, a.[District], b.[Product Group], '' , b.[Asset Aq_ Value] ,b.[Acq_ Date] from 
[Chronus$Asset Depreciation Entry] as a join 
[Chronus$Asset] as b on b.[Serial No_] = a.[Asset Serial No_] join 
 #transtypes as c on c.[TransTypeInt] = a.[Transaction Type] 
 where a.[Date] between @begdate AND @enddate






---- get repair costs
insert into #results select a.[Serial No_], b.[ Size], b.[ Type], b.[Item No_], CONVERT(varchar, a.[Completion Date], 111), 0, 
'Repair', (select sum([Total Cost (LCY)])
from [Chronus$Job Ledger Entry] 
where [Job No_] = a.[No_] and [Type] in (1, 2)  ) * -1, a.[Global Dimension 2 Code], b.[Product Group], a.[Reason Code],b.[Asset Aq_ Value] ,b.[Acq_ Date] from 
[Chronus$Job] as a right join 
[Chronus$Asset] as b on b.[Serial No_] = a.[Serial No_] 
where a.[Completion Date] between  @begdate AND @enddate 
and a.[Status] = 3  and a.[Job Type 2] = 2 and a.[Scrapped] = 0 



select Serial
     , [ Size]
     , [ Type]
     , ItemNo
     , TransDate
     , TransType
     , ISNULL(Amount, 0) as Amount
     , District
     , ProdGroup
     , ReasonCode
     ,[Asset Aq_ Value] 
     ,[Acq_ Date]
     
  from #results
  where 

[Acq_ Date] between @acqDateStart and @acqDateEnd
 order by Serial, TransDate


    • Edited by SBolton Monday, March 04, 2013 7:57 PM
    •  
     

All Replies

  • Monday, March 18, 2013 3:02 AM
     
     
    Sign In to Vote
    0

    Kindly elaborate more the Merge process needed in terms of :

    • Source data
    • Target data
    • Migration condition 

    Shehap (DB Consultant/DB Architect) Think More deeply of DB Stress Stabilities

     
  • Monday, March 18, 2013 5:24 AM
     
     Proposed Has Code
    Sign In to Vote
    0
     
    -- get revenue
insert into #results 

select a.[Asset Serial No_], b.[ Size], b.[ Type], b.[Item No_], CONVERT(varchar, c.[Service Date], 111), 
 , case when a.[ Transaction Type]=0 then ''
	when a.[ Transaction Type]=1 then 'Credit'
	when a.[ Transaction Type]=2 then 'DBR Charge'
	when a.[ Transaction Type]=3 then 'LIH Charge'
	when a.[ Transaction Type]=4 then 'Rental'
	when a.[ Transaction Type]=5 then 'Repair Charge'
	when a.[ Transaction Type]=6 then 'Sale'
 END as TransType 
    , '', a.[Line Amount], a.[Shortcut Dimension 2 Code], b.[Product Group], '', b.[Asset Aq_ Value] ,b.[Acq_ Date]  from 
[Chronus$Sales Invoice Line] as a join 
[Chronus$Asset] as b on b.[Serial No_] = a.[Asset Serial No_] join 
[Chronus$Sales Invoice Header] as c on c.[No_] = a.[Document No_]  
where c.[Service Date] between @begdate AND @enddate
and a.[Line Amount] <> 0 

Union All



---- get expense
select a.[Asset Serial No_], b.[ Size], b.[ Type], b.[Item No_], CONVERT(varchar, a.[Date], 111), 0, c.[TransType], 
a.[Amount] * -1, a.[District], b.[Product Group], '' , b.[Asset Aq_ Value] ,b.[Acq_ Date] from 
[Chronus$Asset Depreciation Entry] as a join 
[Chronus$Asset] as b on b.[Serial No_] = a.[Asset Serial No_] join 
 #transtypes as c on c.[TransTypeInt] = a.[Transaction Type] 
 where a.[Date] between @begdate AND @enddate


 Union All



---- get repair costs
select a.[Serial No_], b.[ Size], b.[ Type], b.[Item No_], CONVERT(varchar, a.[Completion Date], 111), 0, 
'Repair', (select sum([Total Cost (LCY)])
from [Chronus$Job Ledger Entry] 
where [Job No_] = a.[No_] and [Type] in (1, 2)  ) * -1, a.[Global Dimension 2 Code], b.[Product Group], a.[Reason Code],b.[Asset Aq_ Value] ,b.[Acq_ Date] from 
[Chronus$Job] as a right join 
[Chronus$Asset] as b on b.[Serial No_] = a.[Serial No_] 
where a.[Completion Date] between  @begdate AND @enddate 
and a.[Status] = 3  and a.[Job Type 2] = 2 and a.[Scrapped] = 0 



select Serial
     , [ Size]
     , [ Type]
     , ItemNo
     , TransDate
     , TransType
     , ISNULL(Amount, 0) as Amount
     , District
     , ProdGroup
     , ReasonCode
     ,[Asset Aq_ Value] 
     ,[Acq_ Date]
     
  from #results
  where 

[Acq_ Date] between @acqDateStart and @acqDateEnd
 order by Serial, TransDate





     
  • Monday, March 18, 2013 5:42 AM
     
     
    Sign In to Vote
    0

    Hi,

    Merge statement may help you,See below URL for more about merge

    http://msdn.microsoft.com/en-us/library/bb510625.aspx

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.