Hello Jonathan,
thanks for your help. As I cannot upload the SQL data I paste the script in here. Maybe you can identify why I cannot work with your Load script.
LIB CONNECT TO 'ELA-SQL4 (container_matthiasb)';
Qualify *;
Unqualify AB_ADM, Kundenname, Kundennummer, AB_Datum, Plz2, Monat, Tag;
Load *,
Month(BuchDatum) as Monat,
Year(BuchDatum) as Jahr,
Day(BuchDatum) as Tag,
'Q' & ceil(month(BuchDatum)/3) as Quartal,
Floor(BuchDatum) as DateNum1,
GeoMakePoint(Latitude, Longitude) as LocationGLE;
UmsatzGLE:
SELECT
Res.Auftraggeber as Kundennummer
,Res.[Firma] as Kundenname
,Res.[Firma] as Kundenname2
,Res.[Strasse]
,Res.[PLZ] as Plz
,substring(Res.[PLZ],0,3) as Plz2
,Res.[Ort]
,Res.[Land]
,Res.[GLKonto]
,Res.[DimKTR] as Vertragsnummer
,Res.[Baustellennummer] as BauCode
,Res.[Bauname]
,Res.[Longitude]
,Res.[Latitude]
,Res.[DimVertrGeb] as VertriebsGeb
,Res.[DimADM] as AB_ADM
,Res.[Beleg]
,Res.[Hauptknoten] as Hauptknoten
,Res.[Knoten] as Knoten
,CONVERT(date,Res.[BuchDatum]) as BuchDatum
,Res.[Betrag]
FROM (
Select
case when RH.[Rent-to Customer No_] is not null then RH.[Rent-to Customer No_]
when RHA.[Rent-to Customer No_] is not null then RHA.[Rent-to Customer No_]
when SH.[Sell-to Customer No_] is not null then SH.[Sell-to Customer No_]
when SHA.[Sell-to Customer No_] is not null then SHA.[Sell-to Customer No_]
when CU.[No_] is not null then CU.[No_] else 'NA' end as Auftraggeber
, case when RH.[Rent-to Contact No_] is not null then RH.[Rent-to Contact No_]
when RHA.[Rent-to Contact No_] is not null then RHA.[Rent-to Contact No_]
when SH.[Sell-to Contact No_] is not null then SH.[Sell-to Contact No_]
when SHA.[Sell-to Contact No_] is not null then SHA.[Sell-to Contact No_]
else 'NA' end as KontNr
, case when RH.[Rent-to Customer No_] is not null then RH.Name
when RHA.[Rent-to Customer No_] is not null then RHA.Name
when SH.[Sell-to Customer No_] is not null then SH.[Name]
when SHA.[Sell-to Customer No_] is not null then SHA.[Name]
when CU.[No_] is not null then CU.[Name] else 'NA' end as Firma
, case when RH.[Rent-to Customer No_] is not null then RH.[Address]
when RHA.[Rent-to Customer No_] is not null then RHA.[Address]
when SH.[Sell-to Customer No_] is not null then SH.[Address]
when SHA.[Sell-to Customer No_] is not null then SHA.[Address]
when CU.[No_] is not null then CU.[Address] else 'NA' end as Strasse
, case when RH.[Rent-to Customer No_] is not null then RH.[Post Code]
when RHA.[Rent-to Customer No_] is not null then RHA.[Post Code]
when SH.[Sell-to Customer No_] is not null then SH.[Post Code]
when SHA.[Sell-to Customer No_] is not null then SHA.[Post Code]
when CU.[No_] is not null then CU.[Post Code] else 'NA' end as PLZ
, case when RH.[Rent-to Customer No_] is not null then RH.[City]
when RHA.[Rent-to Customer No_] is not null then RHA.[City]
when SH.[Sell-to Customer No_] is not null then SH.[City]
when SHA.[Sell-to Customer No_] is not null then SHA.[City]
when CU.[No_] is not null then CU.[City] else 'NA' end as Ort
, case when RH.[Rent-to Customer No_] is not null then RH.[Country_Region Code]
when RHA.[Rent-to Customer No_] is not null then RHA.[Country_Region Code]
when SH.[Sell-to Customer No_] is not null then SH.[Country_Region Code]
when SHA.[Sell-to Customer No_] is not null then SHA.[Country_Region Code]
when CU.[No_] is not null then CU.[Country_Region Code] else 'NA' end as Land
, case when RH.[Rent-to Customer No_] is not null then 'Miete'
when RHA.[Rent-to Customer No_] is not null then 'Miete'
when SH.[Sell-to Customer No_] is not null then 'Verkauf'
when SHA.[Sell-to Customer No_] is not null then 'Verkauf'
when CU.[No_] is not null then 'NIX' else 'NA' end as AuftragBeleg
, GLE.[G_L Account No_] as GLKonto
, case when GLA.[Acc_ Schedule Line 2] Between 130000 and 140000 then 'Mieterlöse'
when GLA.[Acc_ Schedule Line 2] Between 190000 and 200000 then 'Verkaufserlöse'
when GLA.[Acc_ Schedule Line 2] Between 232500 and 242500 then 'Service Erlöse'
when GLA.[Acc_ Schedule Line 2] Between 282500 and 292500 then 'Sonstige Erlöse'
when GLA.[Acc_ Schedule Line 2] Between 312500 and 312500 then 'Anlagenverkäufe'
else 'NA' end as Hauptknoten
, convert(varchar(50),substring(ASL.[Description],4,50)) as Knoten
, GLA.Name
, GLE.[Source Code] as Herkunft, GLE.[Source No_] as HerkKo
, case when DimKTR.[Dimension Value Code] is null then 'NA' else DimKTR.[Dimension Value Code] end as DimKTR
, case when DimPRJ.[Dimension Value Code] is null then 'NA' else DimPRJ.[Dimension Value Code] end as DimPjNr
, case when RH.[Site Code] is not null then RH.[Site Code]
when RHA.[Site Code] is not null then RHA.[Site Code]
when SH.[Site Code] is not null then SH.[Site Code]
when SHA.[Site Code] is not null then SHA.[Site Code]
when CU.[No_] is not null then 'NA' else 'NA' end as Baustellennummer
,STS.[Name] as Bauname
, case when STS.[Longitude] = 0 then 9.3431 else STS.[Longitude] end as Longitude
, case when STS.[Latitude] = 0 then 51.3389 else STS.[Latitude] end as Latitude
, case when DimART.[Dimension Value Code] is null then 'NA' else DimART.[Dimension Value Code] end as DimArtTYP
, case when DimVTG.[Dimension Value Code] is null then 'NA' else DimVTG.[Dimension Value Code] end as DimVertrGeb
, case when DimADM.[Dimension Value Code] is null and DimVTG.[Dimension Value Code] = 'UNITS' then 'UNITS'
when DimADM.[Dimension Value Code] is null and DimVTG.[Dimension Value Code] is null then 'NA'
else DimADM.[Dimension Value Code] end as DimADM
, GLE.[Document No_] as Beleg
, case when RH.[Order Date] is not null then convert(varchar(10),RH.[Order Date],20)
when RHA.[Order Date] is not null then convert(varchar(10),RHA.[Order Date],20)
when SH.[Order Date] is not null then convert(varchar(10),SH.[Order Date],20)
when SHA.[Order Date] is not null then convert(varchar(10),SHA.[Order Date],20)
when CU.[No_] is not null then 'NIX' else 'NA' end as AuftragDatum
, convert(varchar(10),GLE.[Posting Date],20) as BuchDatum, GLE.[Amount]*(-1) as Betrag
, GLE.[Description] as BuchText
From [ELA Container GmbH$G_L Entry] GLE
outer apply (select top(1) TRH.[Rent-to Customer No_], TRH.[Rent-to Contact No_], TRH.[Order Date]
, CU_RH.Name, CU_RH.Address, CU_RH.[Post Code], CU_RH.City, CU_RH.[Country_Region Code], TRH.[Site Code]
from [ELA Container GmbH$Rental Header] TRH
inner join [ELA Container GmbH$Customer] CU_RH ON CU_RH.No_ = TRH.[Rent-to Customer No_]
where TRH.No_ = GLE.[Global Dimension 2 Code] and TRH.[Document Type] = 4 ) as RH
outer apply (select top(1) TRHA.[Rent-to Customer No_], TRHA.[Rent-to Contact No_], TRHA.[Order Date]
, CU_RHA.Name, CU_RHA.Address, CU_RHA.[Post Code], CU_RHA.City, CU_RHA.[Country_Region Code], TRHA.[Site Code]
from [ELA Container GmbH$Rental Header Archive] TRHA
inner join [ELA Container GmbH$Customer] CU_RHA ON CU_RHA.No_ = TRHA.[Rent-to Customer No_]
where TRHA.No_ = GLE.[Global Dimension 2 Code] and TRHA.[Document Type] = 4
order by [Version No_] desc) as RHA
outer apply (select top(1) TSH.[Sell-to Customer No_], TSH.[Sell-to Contact No_], TSH.[Order Date]
, CU_SH.Name, CU_SH.Address, CU_SH.[Post Code], CU_SH.City, CU_SH.[Country_Region Code], TSH.[Site Code]
from [ELA Container GmbH$Sales Header] TSH
inner join [ELA Container GmbH$Customer] CU_SH ON CU_SH.No_ = TSH.[Sell-to Customer No_]
where TSH.No_ = GLE.[Global Dimension 2 Code] and TSH.[Document Type] = 1 ) as SH
outer apply (select top(1) TSHA.[Sell-to Customer No_], TSHA.[Sell-to Contact No_],TSHA.[Order Date]
, CU_SHA.Name, CU_SHA.Address, CU_SHA.[Post Code], CU_SHA.City, CU_SHA.[Country_Region Code], TSHA.[Site Code]
from [ELA Container GmbH$Sales Header Archive] TSHA
inner join [ELA Container GmbH$Customer] CU_SHA ON CU_SHA.No_ = TSHA.[Sell-to Customer No_]
where TSHA.No_ = GLE.[Global Dimension 2 Code] and TSHA.[Document Type] = 1
order by [Version No_] desc) as SHA
left outer join [ELA Container GmbH$G_L Account] GLA on GLA.No_ = GLE.[G_L Account No_]
left outer join [ELA Container GmbH$Customer] CU ON CU.No_ = GLE.[Source No_]
left outer join [ELA Container GmbH$Chain Name] UK on UK.Code = CU.[Chain Name]
left outer join [ELA Container GmbH$Ship-to Site] STS on STS.[Code] = RH.[Site Code] or STS.[Code] = SH.[Site Code] or STS.[Code] = RHA.[Site Code] or STS.[Code] = SHA.[Site Code]
left outer join [ELA Container GmbH$Acc_ Schedule Line] ASL on GLA.[Acc_ Schedule Line 2] = ASL.[Line No_] and GLA.[Acc_ Schedule Name 2] = ASL.[Schedule Name]
left outer join [ELA Container GmbH$Ledger Entry Dimension] DimKTR ON DimKTR.[Entry No_]=GLE.[Entry No_] and DimKTR.[Table ID] = 17 and DimKTR.[Dimension Code] = 'KTR'
left outer join [ELA Container GmbH$Ledger Entry Dimension] DimPRJ ON DimPRJ.[Entry No_]=GLE.[Entry No_] and DimPRJ.[Table ID] = 17 and DimPRJ.[Dimension Code] = 'ELA-PROJEKT'
left outer join [ELA Container GmbH$Ledger Entry Dimension] DimART ON DimART.[Entry No_]=GLE.[Entry No_] and DimART.[Table ID] = 17 and DimART.[Dimension Code] = 'ARTIKEL-TYP'
left outer join [ELA Container GmbH$Ledger Entry Dimension] DimVTG ON DimVTG.[Entry No_]=GLE.[Entry No_] and DimVTG.[Table ID] = 17 and DimVTG.[Dimension Code] = 'VTA-GEBIET'
left outer join [ELA Container GmbH$Ledger Entry Dimension] DimADM ON DimADM.[Entry No_]=GLE.[Entry No_] and DimADM.[Table ID] = 17 and DimADM.[Dimension Code] = 'VTA-ADM'
Where ( (len(GLE.[G_L Account No_]) = 5 and (GLE.[G_L Account No_] Between '40000' And '42200'
Or GLE.[G_L Account No_] Between '42205' And '43999'
Or GLE.[G_L Account No_] Between '45000' And '45999'
Or GLE.[G_L Account No_] Between '46110' And '46189'
Or GLE.[G_L Account No_] Between '46260' And '46263'
Or GLE.[G_L Account No_] Between '48503' And '48503'
Or GLE.[G_L Account No_] Between '49000' And '49014'
Or GLE.[G_L Account No_] Between '49016' And '49999'))
or (len(GLE.[G_L Account No_]) = 8 and (GLE.[G_L Account No_] Between '41100000' And '43369999'
Or GLE.[G_L Account No_] Between '43370001' And '45051002'
Or GLE.[G_L Account No_] Between '45052003' And '45052003'
Or GLE.[G_L Account No_] Between '45053010' And '45053010'
Or GLE.[G_L Account No_] Between '45054015' And '45054015'
Or GLE.[G_L Account No_] Between '47300000' And '47999999'
Or GLE.[G_L Account No_] Between '47600100' And '47600100'
Or GLE.[G_L Account No_] Between '47694002' And '47694002'
Or GLE.[G_L Account No_] Between '48320102' And '48320102'
Or GLE.[G_L Account No_] Between '48450100' And '48450300'
Or GLE.[G_L Account No_] Between '48470202' And '48470303'
Or GLE.[G_L Account No_] Between '48470515' And '48470515'
Or GLE.[G_L Account No_] Between '49700000' And '49700102'
Or GLE.[G_L Account No_] Between '57410001' And '57410001'
Or GLE.[G_L Account No_] Between '68850100' And '68850200'
Or GLE.[G_L Account No_] Between '68870115' And '68870115'
Or GLE.[G_L Account No_] Between '68870202' And '68870202')) )
and GLE.[Source Code] <> 'JAHRABSCH'
and convert(varchar(10),GLE.[Posting Date],120) Between convert(date,DATEADD(YEAR, DATEDIFF(YEAR, 0,DATEADD(YEAR,-1,GETDATE())), 0)) and convert(date,DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, -1))
) Res;
