USP_DATALIST_SEGMENTATIONREPORT

CREATE procedure dbo.[USP_DATALIST_SEGMENTATIONREPORT]
(
  @SEGMENTATIONID uniqueidentifier
)
as
  set nocount on;

  select 
    [MKTSEGMENTATION].[NAME],
    [MKTSEGMENTATION].[MAILINGTYPECODE],
    [MKTSEGMENTATION].[MAILINGTYPE],
    (select count([ID]) from dbo.[MKTSEGMENTATIONSEGMENT] where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]) as [SEGMENTCOUNT],
    (select count([ID]) from dbo.[MKTSEGMENTATIONPACKAGE] where [MKTSEGMENTATIONPACKAGE].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]) as [PACKAGECOUNT],
    [MKTSEGMENTATION].[CODE] as [CODE],
    dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MKTSEGMENTATION].[SITEID]) as [SITE],
    [MKTSEGMENTATIONACTIVATE].[APPEALID] as [APPEAL],
    [MKTSEGMENTATION].[MAILDATE],
    [MKTSEGMENTATION].[ACTIVATEDATE],
    [MKTSEGMENTATION].[ACTIVE],
    isnull([MKTSEGMENTATIONREFRESHPROCESS].[DATEREFRESHED], [MKTSEGMENTATION].[ACTIVATEDATE]) as [DATEREFRESHED],
    cast(
        case 
            when [MKTSEGMENTATION].[ISHISTORICAL] = 1 then 1
            when [MKTSEGMENTATION].[ACTIVE] = 0 then dbo.[UFN_MKTSEGMENTATION_ISRECORDCOUNTCACHECURRENT]([MKTSEGMENTATION].[ID], 1) 
            else 1 
        end 
    as bit) as [ISRECORDCOUNTCACHECURRENT],
    (select count([ID]) from dbo.[MKTSEGMENTATIONSEGMENT] where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 0) as [ACTIVESEGMENTCOUNT],
    (select count([ID]) from dbo.[MKTSEGMENTATIONSEGMENT] where [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID] and [MKTSEGMENTATIONSEGMENT].[EXCLUDE] = 1) as [SEGMENTEXCLUSIONSCOUNT]
  from dbo.[MKTSEGMENTATION]
  left join dbo.[MKTSEGMENTATIONREFRESHPROCESS] on [MKTSEGMENTATIONREFRESHPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  left join dbo.[MKTSEGMENTATIONACTIVATE] on [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  where [MKTSEGMENTATION].[ID] = @SEGMENTATIONID;

  return 0;