USP_DATALIST_MKTSEGMENTLISTHISTORICAL

CREATE procedure dbo.[USP_DATALIST_MKTSEGMENTLISTHISTORICAL]
(
  @SEGMENTID uniqueidentifier
)
as
  set nocount on;

  select
    [MKTSEGMENTLIST].[ID],
    (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTLIST].[NAME] else [MKTSEGMENTLISTHISTORICAL].[LISTNAME] end) as [LISTNAME],
    (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTLIST].[DESCRIPTION] else [MKTSEGMENTLISTHISTORICAL].[LISTDESCRIPTION] end) as [LISTDESCRIPTION],
    (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTLIST].[CODE] else [MKTSEGMENTLISTHISTORICAL].[LISTCODE] end) as [LISTCODE],
    (select [NAME] from dbo.[CONSTITUENT] where [ID] = (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTLIST].[VENDORID] else [MKTSEGMENTLISTHISTORICAL].[LISTVENDORID] end)) as [LISTVENDOR],
    (select [DESCRIPTION] from dbo.[MKTLISTCATEGORYCODE] where [ID] = (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTLIST].[LISTCATEGORYCODEID] else [MKTSEGMENTLISTHISTORICAL].[LISTCATEGORYCODEID] end)) as [LISTCATEGORY],
    (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTSEGMENT].[NAME] else [MKTSEGMENTLISTHISTORICAL].[SEGMENTNAME] end) as [SEGMENTNAME],
    (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTSEGMENT].[DESCRIPTION] else [MKTSEGMENTLISTHISTORICAL].[SEGMENTDESCRIPTION] end) as [SEGMENTDESCRIPTION],
    (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTSEGMENT].[CODE] else [MKTSEGMENTLISTHISTORICAL].[SEGMENTCODE] end) as [SEGMENTCODE],
    (select [DESCRIPTION] from dbo.[MKTSEGMENTCATEGORYCODE] where [ID] = (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTSEGMENT].[SEGMENTCATEGORYCODEID] else [MKTSEGMENTLISTHISTORICAL].[SEGMENTCATEGORYCODEID] end)) as [SEGMENTCATEGORY],
    [MKTSEGMENTLIST].[STATUSCODE],
    [MKTSEGMENTLIST].[STATUS],
    [MKTSEGMENTLIST].[TOTALRECORDCOUNT],
    [MKTSEGMENTLIST].[DUPLICATERECORDCOUNT],
    [MKTSEGMENTLIST].[ORDERDATE],
    [MKTSEGMENTLIST].[EXPIRATIONDATE],
    [MKTSEGMENTLIST].[NUMBEROFCONTACTS],
    [MKTSEGMENTLIST].[RENTALQUANTITY],
    (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTLIST].[BASERENTALCOST] else [MKTSEGMENTLISTHISTORICAL].[LISTBASERENTALCOST] end) as [LISTBASERENTALCOST],
    (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTLIST].[BASERENTALCOSTBASISCODE] else [MKTSEGMENTLISTHISTORICAL].[LISTBASERENTALCOSTBASISCODE] end) as [LISTBASERENTALCOSTBASISCODE],
    [MKTSEGMENTLIST].[RENTALCOSTADJUSTMENT],
    [MKTSEGMENTLIST].[RENTALCOSTBASISCODE],
    [MKTSEGMENTLIST].[EXCHANGEQUANTITY],
    (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTLIST].[BASEEXCHANGECOST] else [MKTSEGMENTLISTHISTORICAL].[LISTBASEEXCHANGECOST] end) as [LISTBASEEXCHANGECOST],
    (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTLIST].[BASEEXCHANGECOSTBASISCODE] else [MKTSEGMENTLISTHISTORICAL].[LISTBASEEXCHANGECOSTBASISCODE] end) as [LISTBASEEXCHANGECOSTBASISCODE],
    [MKTSEGMENTLIST].[EXCHANGECOSTADJUSTMENT],
    [MKTSEGMENTLIST].[EXCHANGECOSTBASISCODE],
    [MKTSEGMENTLIST].[RECEIVEDVIA],
    [MKTLISTLAYOUT].[NAME] as [LAYOUTNAME],
    [MKTSEGMENTLIST].[FILENAME],
    (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTSEGMENT].[DATECHANGED] else [MKTSEGMENTLISTHISTORICAL].[DATEADDED] end) as [DATEADDED],
    (case when [MKTSEGMENTLIST].[STATUSCODE] = 5 then [MKTSEGMENTLISTHISTORICAL].[DATECHANGED] else null end) as [DATEPURGED],
    (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then 'RES:check' else (case when [MKTSEGMENTLIST].[STATUSCODE] = 5 then 'RES:warning' else 'RES:history' end) end) as [IMAGEKEY]
  from dbo.[MKTSEGMENTLIST]
  inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTLIST].[SEGMENTID]
  left join dbo.[MKTSEGMENTLISTHISTORICAL] on [MKTSEGMENTLISTHISTORICAL].[ID] = [MKTSEGMENTLIST].[ID]
  inner join dbo.[MKTLIST] on [MKTLIST].[ID] = [MKTSEGMENTLIST].[LISTID]
  inner join dbo.[MKTLISTLAYOUT] on [MKTLISTLAYOUT].[ID] = [MKTSEGMENTLIST].[LISTLAYOUTID]
  where [MKTSEGMENTLIST].[SEGMENTID] = @SEGMENTID
  and [MKTSEGMENTLIST].[TYPECODE] = 0  --Imported only

  order by [MKTSEGMENTLIST].[DATEADDED] desc;

  return 0;