premise_v

--CREATE OR ALTER VIEW URS_STAGING_SCHEMA.PREMISE_V (DERIVEDID, SYSTEM, PREMISE_TYPE_DESC, ID, CT_VOL, CT_FOLIO, VGO_NO, PHONE_NO, ADDRLINE1, ADDRLINE2, ADDRLINE3, SURBURB, POSTCODE, STATE, COUNTRYCODE, APPLICATION_ID, FROM_DATE, TO_DATE, BAR_CODE_NO, BSP_NO, DPID, "TIMESTAMP", USERID, "NAME", NAME_EFFECTIVE_DATE, NAME_TIMESTAMP, NAME_USERID, PREMISEFORCOMPARISION) AS
select    /*first_value(Id) over  (partition by a.PremiseForComparision, a.PostCode, UPPER(a.AddrLine1)
                                 order by     case a.system  when 'RGLS' then  1  when 'RGS' then  2  when 'LLS' then  3 end, Id desc) derivedId
,*/         a."SYSTEM",a."PREMISE_TYPE_DESC",a."ID", a."CT_VOL",a."CT_FOLIO",a."VGO_NO",a."PHONE_NO",a."ADDRLINE1",a."ADDRLINE2",a."ADDRLINE3",a."SURBURB",a."POSTCODE",a."STATE",a."COUNTRYCODE",a."APPLICATION_ID",a."FROM_DATE",a."TO_DATE",a."BAR_CODE_NO",a."BSP_NO",a."DPID",a."TIMESTAMP",a."USERID",a."NAME",a."NAME_EFFECTIVE_DATE",a."NAME_TIMESTAMP",a."NAME_USERID",a."PREMISEFORCOMPARISION"
from
(
select    cast("SYSTEM" as varchar(4)) as "SYSTEM", "PREMISE_TYPE_DESC","ID",
          "CT_VOL", "CT_FOLIO", "VGO_NO", "PHONE_NO", "ADDRLINE1", "ADDRLINE2", "ADDRLINE3", "SURBURB", "POSTCODE", "STATE", "COUNTRYCODE", "APPLICATION_ID"
,         "FROM_DATE", "TO_DATE"
,         "BAR_CODE_NO", "BSP_NO", "DPID", "TIMESTAMP", "USERID", "NAME"
,         "NAME_EFFECTIVE_DATE"
,         "NAME_TIMESTAMP", "NAME_USERID", "PREMISEFORCOMPARISION"

from      "LIQUOR"."LIQUOR"."LLS_PREMISE_V"

union
select    cast("SYSTEM" as varchar(4)) as "SYSTEM", "PREMISE_TYPE_DESC","ID",
          cast("CT_VOL" as varchar), cast("CT_FOLIO" as varchar), cast("VGO_NO" as varchar), cast("PHONE_NO" as varchar), "ADDRLINE1", "ADDRLINE2", "ADDRLINE3", "SURBURB", "POSTCODE", "STATE", "COUNTRYCODE", "APPLICATION_ID"
--,       convert(convert(datetime,"FROM_DATE")), convert(datetime2,convert(datetime,"TO_DATE"))
,         cast(convert(datetime,"FROM_DATE") as datetime2), cast(convert(datetime,"TO_DATE") as datetime2)
--,       "FROM_DATE","TO_DATE"
,         "BAR_CODE_NO", "BSP_NO", "DPID", "TIMESTAMP", "USERID", "NAME"
,         "NAME_EFFECTIVE_DATE"
,         "NAME_TIMESTAMP", "NAME_USERID", "PREMISEFORCOMPARISION"

from      "GAMING"."GAMING"."RGS_PREMISE_V"

union
select    cast("SYSTEM" as varchar(4)) as "SYSTEM", "PREMISE_TYPE_DESC","ID",
          cast("CT_VOL" as varchar), cast("CT_FOLIO" as varchar), cast("VGO_NO" as varchar), cast("PHONE_NO" as varchar), "ADDRLINE1", "ADDRLINE2", "ADDRLINE3", "SURBURB", "POSTCODE", "STATE", "COUNTRYCODE", "APPLICATION_ID"
,         "FROM_DATE", "TO_DATE"
,         "BAR_CODE_NO", "BSP_NO", "DPID", "TIMESTAMP", "USERID", "NAME"
,         convert(datetime2,convert(datetime,"NAME_EFFECTIVE_DATE"))
,         "NAME_TIMESTAMP", "NAME_USERID", "PREMISEFORCOMPARISION"

from      "RGL"."RGL"."RGLS_PREMISE_V"
 ) a