Skip to main content

Return to Question

formatted sql code
Source Link
Radu Gheorghiu
Radu Gheorghiu
  • 20.6k
  • 16
  • 78
  • 113
(select *  
from (
    select ROW_NUMBER() over (
            partition by Dep
            , tariffkode
            , LinkTariffType
            , poliata
            , poliatavia
            , podiata
            , podiatavia
            , PreCarriage
            , PortTerminalId
            , Product,RoutingOrder,
            , RoutingOrder
            , PrepaidCollect
  PrepaidCollect          , isnull(description, '')
            , ScaleCalcCode
            , isnull(scalefrom, 0)
            , isnull(scaleto, 0), 
 CurrencyCode,Base order by LinkTariffType desc) 
       , CurrencyCode
            , Base order by LinkTariffType desc
    Record,* 
        ) Record
        , *
    from (
       from (select tn.LinkTariffType
            , tn.Dep
            , tn.POLIata
            , tn.POLIatavia
            , tn.PODIata
            , tn.PODIatavia
            , tn.CurrencyCode
            , tn.LegalEntityID
            , tn.Rate
            , tn.Base, 
               , tn.Minimum
            tn.Minimum, tn.NrDescription
            , tn.Description
            , tn.DateFrom
            , tn.DateUntil
            , tn.DateCreate
            , tn.DateMod
            , tn.ModName
            , tn.Tariffkode
            , tn.ExpiryDate, 
               , tn.PClass
            tn.PClass, tn.Maximum
            , tn.RoutingOrder
            , tn.TariffCompType
            , tn.PrePaidCollect
            , tn.Product
            , tn.IsDeleted,
            , (
                (select distinct Location_IATA
                from Company
                where Called ='KARL= 'KARL KING' 
 and LegalEntityID=1                  and IsDeleted=0)LegalEntityID as= PreCarriage,1
                    and IsDeleted = 0
                ) as PreCarriage
            , tn.PortTerminalID
            , tn.ScaleFrom
            , tn.ScaleTo,
               , tn.ScaleCalcCode
            tn.ScaleCalcCode, tn.Mandatory
            , (
                select CompanyID
                from PlaceOfReceipt
                where warehouse='KARLwarehouse = 'KARL KING'
                    and LegalEntityID=1LegalEntityID = 1
                    and OfficeID = 13
     and OfficeID=13              and IsDeleted=0)IsDeleted as= WarehouseID,0
                ) as WarehouseID
            , tn.TariffRelID
            , tn.FreeDescription
            , 0
            , tn.ShipCode
            , tn.AgentID
            , tn.ContainerTypeID
            , tn.CommodityID, 0 as 
 TempTable from TariffNew tn 
        , 0 as TempTable
        from TariffNew tn
        inner join hhInvoiceLines inv
            on tn.Tariffkode = inv.NrInvoiceLine
            where (
               where (tn.PreCarriage is not null
                and tn.PreCarriage !=''= ''
                )
            and (
                tn.POLIata is not null
                and tn.POLIata!='' ) and!= ''
                )
            and inv.OfficeID = 13 
 and inv.IsDeleted= 0         and inv.LegalEntityID=1IsDeleted and= 0
            and inv.LegalEntityID = 1
            and tn.LegalEntityID=1LegalEntityID = 1
            and tn.Dep ='E'= 'E'
            and tn.IsDeleted=0IsDeleted = 0
            and tn.DateUntil = '2078-12-31 00:00:00'
            and tn.Description='kgl'Description = 'kgl'
        )
    ) as b  
where  b.Record =1)= 1

(select * from (select ROW_NUMBER() over (partition by Dep, tariffkode,LinkTariffType,poliata,poliatavia,podiata,podiatavia,PreCarriage,PortTerminalId,Product,RoutingOrder,
                            PrepaidCollect,isnull(description,''),ScaleCalcCode,isnull(scalefrom,0),isnull(scaleto,0), CurrencyCode,Base order by LinkTariffType desc) 
                             Record,* 
                             from (select tn.LinkTariffType, tn.Dep, tn.POLIata, tn.POLIatavia, tn.PODIata, tn.PODIatavia, tn.CurrencyCode, tn.LegalEntityID, tn.Rate, tn.Base, 
                            tn.Minimum, tn.NrDescription, tn.Description, tn.DateFrom, tn.DateUntil, tn.DateCreate, tn.DateMod, tn.ModName, tn.Tariffkode, tn.ExpiryDate, 
                            tn.PClass, tn.Maximum, tn.RoutingOrder, tn.TariffCompType, tn.PrePaidCollect, tn.Product, tn.IsDeleted,
                            (select distinct Location_IATA from Company where Called ='KARL KING' and LegalEntityID=1 and IsDeleted=0) as PreCarriage, 
                            tn.PortTerminalID, tn.ScaleFrom, tn.ScaleTo,
                            tn.ScaleCalcCode, tn.Mandatory, (select CompanyID from PlaceOfReceipt where warehouse='KARL KING' and LegalEntityID=1
                            and OfficeID=13 and IsDeleted=0) as WarehouseID,
                            tn.TariffRelID, tn.FreeDescription, 0 , tn.ShipCode, tn.AgentID, tn.ContainerTypeID, tn.CommodityID, 0 as TempTable from TariffNew tn 
                             inner join hhInvoiceLines inv on tn.Tariffkode = inv.NrInvoiceLine
                            where (tn.PreCarriage is not null and tn.PreCarriage !='') and (tn.POLIata is not null and tn.POLIata!='' ) and 
                            inv.OfficeID = 13 and inv.IsDeleted= 0 and inv.LegalEntityID=1 and 
                            tn.LegalEntityID=1  and tn.Dep ='E' and tn.IsDeleted=0 and tn.DateUntil = '2078-12-31 00:00:00' and tn.Description='kgl')) as b where  b.Record =1)

select * 
from (
    select ROW_NUMBER() over (
            partition by Dep
            , tariffkode
            , LinkTariffType
            , poliata
            , poliatavia
            , podiata
            , podiatavia
            , PreCarriage
            , PortTerminalId
            , Product
            , RoutingOrder
            , PrepaidCollect
            , isnull(description, '')
            , ScaleCalcCode
            , isnull(scalefrom, 0)
            , isnull(scaleto, 0) 
            , CurrencyCode
            , Base order by LinkTariffType desc
            ) Record
        , *
    from (
        select tn.LinkTariffType
            , tn.Dep
            , tn.POLIata
            , tn.POLIatavia
            , tn.PODIata
            , tn.PODIatavia
            , tn.CurrencyCode
            , tn.LegalEntityID
            , tn.Rate
            , tn.Base
            , tn.Minimum
            , tn.NrDescription
            , tn.Description
            , tn.DateFrom
            , tn.DateUntil
            , tn.DateCreate
            , tn.DateMod
            , tn.ModName
            , tn.Tariffkode
            , tn.ExpiryDate
            , tn.PClass
            , tn.Maximum
            , tn.RoutingOrder
            , tn.TariffCompType
            , tn.PrePaidCollect
            , tn.Product
            , tn.IsDeleted
            , (
                select distinct Location_IATA
                from Company
                where Called = 'KARL KING' 
                    and LegalEntityID = 1
                    and IsDeleted = 0
                ) as PreCarriage
            , tn.PortTerminalID
            , tn.ScaleFrom
            , tn.ScaleTo
            , tn.ScaleCalcCode
            , tn.Mandatory
            , (
                select CompanyID
                from PlaceOfReceipt
                where warehouse = 'KARL KING'
                    and LegalEntityID = 1
                    and OfficeID = 13
                    and IsDeleted = 0
                ) as WarehouseID
            , tn.TariffRelID
            , tn.FreeDescription
            , 0
            , tn.ShipCode
            , tn.AgentID
            , tn.ContainerTypeID
            , tn.CommodityID 
            , 0 as TempTable
        from TariffNew tn
        inner join hhInvoiceLines inv
            on tn.Tariffkode = inv.NrInvoiceLine
        where (
                tn.PreCarriage is not null
                and tn.PreCarriage != ''
                )
            and (
                tn.POLIata is not null
                and tn.POLIata != ''
                )
            and inv.OfficeID = 13 
            and inv.IsDeleted = 0
            and inv.LegalEntityID = 1
            and tn.LegalEntityID = 1
            and tn.Dep = 'E'
            and tn.IsDeleted = 0
            and tn.DateUntil = '2078-12-31 00:00:00'
            and tn.Description = 'kgl'
        )
    ) as b 
where b.Record = 1
added tag to redirect question to wider audience
Link
edit approved Feb 21, 2017 at 12:12
jjj
edit approved Feb 21, 2017 at 12:12
jjj
  • 1.2k
  • 3
  • 18
  • 28
added 60 characters in body
Source Link
Chanukya
Chanukya
  • 5.9k
  • 1
  • 24
  • 39

(select * from (select ROW_NUMBER() over (partition by Dep, tariffkode,LinkTariffType,poliata,poliatavia,podiata,podiatavia,PreCarriage,PortTerminalId,Product,RoutingOrder, PrepaidCollect,isnull(description,''),ScaleCalcCode,isnull(scalefrom,0),isnull(scaleto,0), CurrencyCode,Base order by LinkTariffType desc) Record,* from (select tn.LinkTariffType, tn.Dep, tn.POLIata, tn.POLIatavia, tn.PODIata, tn.PODIatavia, tn.CurrencyCode, tn.LegalEntityID, tn.Rate, tn.Base, tn.Minimum, tn.NrDescription, tn.Description, tn.DateFrom, tn.DateUntil, tn.DateCreate, tn.DateMod, tn.ModName, tn.Tariffkode, tn.ExpiryDate, tn.PClass, tn.Maximum, tn.RoutingOrder, tn.TariffCompType, tn.PrePaidCollect, tn.Product, tn.IsDeleted, (select distinct Location_IATA from Company where Called ='KARL KING' and LegalEntityID=1 and IsDeleted=0) as PreCarriage, tn.PortTerminalID, tn.ScaleFrom, tn.ScaleTo, tn.ScaleCalcCode, tn.Mandatory, (select CompanyID from PlaceOfReceipt where warehouse='KARL KING' and LegalEntityID=1 and OfficeID=13 and IsDeleted=0) as WarehouseID, tn.TariffRelID, tn.FreeDescription, 0 , tn.ShipCode, tn.AgentID, tn.ContainerTypeID, tn.CommodityID, 0 as TempTable from TariffNew tn inner join hhInvoiceLines inv on tn.Tariffkode = inv.NrInvoiceLine where (tn.PreCarriage is not null and tn.PreCarriage !='') and (tn.POLIata is not null and tn.POLIata!='' ) and inv.OfficeID = 13 and inv.IsDeleted= 0 and inv.LegalEntityID=1 and tn.LegalEntityID=1 and tn.Dep ='E' and tn.IsDeleted=0 and tn.DateUntil = '2078-12-31 00:00:00' and tn.Description='kgl')) as b where b.Record =1)

(select * from (select ROW_NUMBER() over (partition by Dep, tariffkode,LinkTariffType,poliata,poliatavia,podiata,podiatavia,PreCarriage,PortTerminalId,Product,RoutingOrder,
                            PrepaidCollect,isnull(description,''),ScaleCalcCode,isnull(scalefrom,0),isnull(scaleto,0), CurrencyCode,Base order by LinkTariffType desc) 
                            Record,* 
                            from (select tn.LinkTariffType, tn.Dep, tn.POLIata, tn.POLIatavia, tn.PODIata, tn.PODIatavia, tn.CurrencyCode, tn.LegalEntityID, tn.Rate, tn.Base, 
                            tn.Minimum, tn.NrDescription, tn.Description, tn.DateFrom, tn.DateUntil, tn.DateCreate, tn.DateMod, tn.ModName, tn.Tariffkode, tn.ExpiryDate, 
                            tn.PClass, tn.Maximum, tn.RoutingOrder, tn.TariffCompType, tn.PrePaidCollect, tn.Product, tn.IsDeleted,
                            (select distinct Location_IATA from Company where Called ='KARL KING' and LegalEntityID=1 and IsDeleted=0) as PreCarriage, 
                            tn.PortTerminalID, tn.ScaleFrom, tn.ScaleTo,
                            tn.ScaleCalcCode, tn.Mandatory, (select CompanyID from PlaceOfReceipt where warehouse='KARL KING' and LegalEntityID=1
                            and OfficeID=13 and IsDeleted=0) as WarehouseID,
                            tn.TariffRelID, tn.FreeDescription, 0 , tn.ShipCode, tn.AgentID, tn.ContainerTypeID, tn.CommodityID, 0 as TempTable from TariffNew tn 
                            inner join hhInvoiceLines inv on tn.Tariffkode = inv.NrInvoiceLine
                            where (tn.PreCarriage is not null and tn.PreCarriage !='') and (tn.POLIata is not null and tn.POLIata!='' ) and 
                            inv.OfficeID = 13 and inv.IsDeleted= 0 and inv.LegalEntityID=1 and 
                            tn.LegalEntityID=1  and tn.Dep ='E' and tn.IsDeleted=0 and tn.DateUntil = '2078-12-31 00:00:00' and tn.Description='kgl')) as b where  b.Record =1)

(select * from (select ROW_NUMBER() over (partition by Dep, tariffkode,LinkTariffType,poliata,poliatavia,podiata,podiatavia,PreCarriage,PortTerminalId,Product,RoutingOrder, PrepaidCollect,isnull(description,''),ScaleCalcCode,isnull(scalefrom,0),isnull(scaleto,0), CurrencyCode,Base order by LinkTariffType desc) Record,* from (select tn.LinkTariffType, tn.Dep, tn.POLIata, tn.POLIatavia, tn.PODIata, tn.PODIatavia, tn.CurrencyCode, tn.LegalEntityID, tn.Rate, tn.Base, tn.Minimum, tn.NrDescription, tn.Description, tn.DateFrom, tn.DateUntil, tn.DateCreate, tn.DateMod, tn.ModName, tn.Tariffkode, tn.ExpiryDate, tn.PClass, tn.Maximum, tn.RoutingOrder, tn.TariffCompType, tn.PrePaidCollect, tn.Product, tn.IsDeleted, (select distinct Location_IATA from Company where Called ='KARL KING' and LegalEntityID=1 and IsDeleted=0) as PreCarriage, tn.PortTerminalID, tn.ScaleFrom, tn.ScaleTo, tn.ScaleCalcCode, tn.Mandatory, (select CompanyID from PlaceOfReceipt where warehouse='KARL KING' and LegalEntityID=1 and OfficeID=13 and IsDeleted=0) as WarehouseID, tn.TariffRelID, tn.FreeDescription, 0 , tn.ShipCode, tn.AgentID, tn.ContainerTypeID, tn.CommodityID, 0 as TempTable from TariffNew tn inner join hhInvoiceLines inv on tn.Tariffkode = inv.NrInvoiceLine where (tn.PreCarriage is not null and tn.PreCarriage !='') and (tn.POLIata is not null and tn.POLIata!='' ) and inv.OfficeID = 13 and inv.IsDeleted= 0 and inv.LegalEntityID=1 and tn.LegalEntityID=1 and tn.Dep ='E' and tn.IsDeleted=0 and tn.DateUntil = '2078-12-31 00:00:00' and tn.Description='kgl')) as b where b.Record =1)

(select * from (select ROW_NUMBER() over (partition by Dep, tariffkode,LinkTariffType,poliata,poliatavia,podiata,podiatavia,PreCarriage,PortTerminalId,Product,RoutingOrder,
                            PrepaidCollect,isnull(description,''),ScaleCalcCode,isnull(scalefrom,0),isnull(scaleto,0), CurrencyCode,Base order by LinkTariffType desc) 
                            Record,* 
                            from (select tn.LinkTariffType, tn.Dep, tn.POLIata, tn.POLIatavia, tn.PODIata, tn.PODIatavia, tn.CurrencyCode, tn.LegalEntityID, tn.Rate, tn.Base, 
                            tn.Minimum, tn.NrDescription, tn.Description, tn.DateFrom, tn.DateUntil, tn.DateCreate, tn.DateMod, tn.ModName, tn.Tariffkode, tn.ExpiryDate, 
                            tn.PClass, tn.Maximum, tn.RoutingOrder, tn.TariffCompType, tn.PrePaidCollect, tn.Product, tn.IsDeleted,
                            (select distinct Location_IATA from Company where Called ='KARL KING' and LegalEntityID=1 and IsDeleted=0) as PreCarriage, 
                            tn.PortTerminalID, tn.ScaleFrom, tn.ScaleTo,
                            tn.ScaleCalcCode, tn.Mandatory, (select CompanyID from PlaceOfReceipt where warehouse='KARL KING' and LegalEntityID=1
                            and OfficeID=13 and IsDeleted=0) as WarehouseID,
                            tn.TariffRelID, tn.FreeDescription, 0 , tn.ShipCode, tn.AgentID, tn.ContainerTypeID, tn.CommodityID, 0 as TempTable from TariffNew tn 
                            inner join hhInvoiceLines inv on tn.Tariffkode = inv.NrInvoiceLine
                            where (tn.PreCarriage is not null and tn.PreCarriage !='') and (tn.POLIata is not null and tn.POLIata!='' ) and 
                            inv.OfficeID = 13 and inv.IsDeleted= 0 and inv.LegalEntityID=1 and 
                            tn.LegalEntityID=1  and tn.Dep ='E' and tn.IsDeleted=0 and tn.DateUntil = '2078-12-31 00:00:00' and tn.Description='kgl')) as b where  b.Record =1)
Source Link
Che
Che
  • 87
  • 1
  • 1
  • 10