USP_BBDW_ATTRIBUTE_GETDIMVALUES

create procedure dbo.[USP_BBDW_ATTRIBUTE_GETDIMVALUES](
  @OLAPDATASOURCEID uniqueidentifier, 
  @OPENWINDOW datetime, 
  @CLOSEWINDOW datetime
) as 
  set nocount on;

  declare @ATTRIBUTEVALUES table(
    [ATTRIBUTESYSTEMID] uniqueidentifier,
    [ATTRIBUTEVALUESYSTEMID] uniqueidentifier,
    [ATTRIBUTEVALUE] nvarchar(100),    
    [ACTIVE] bit,
    [DATECHANGED] datetime
  )

  declare @SQL nvarchar(500);
  declare @ATTRIBUTEID uniqueidentifier;
  declare @CODETABLENAME nvarchar(100);  

  --Get all code table values

  declare ATTRIBUTECURSOR cursor local fast_forward for 
    select a.[ID], c.[DBTABLENAME]
    from dbo.[ATTRIBUTECATEGORY] a    
    left join dbo.[CODETABLECATALOG] c on a.[CODETABLECATALOGID] = c.[ID]
    where a.[DATATYPECODE] = 5;

  open ATTRIBUTECURSOR;
  fetch next from ATTRIBUTECURSOR into @ATTRIBUTEID, @CODETABLENAME;
  while (@@FETCH_STATUS = 0)
  begin

    set @sql = 'select @ATTRIBUTEID, [ID], [DESCRIPTION], [ACTIVE], [DATECHANGED]' + char(13) + 
               'from  dbo.[' + @CODETABLENAME + '];';

    insert into @ATTRIBUTEVALUES([ATTRIBUTESYSTEMID],[ATTRIBUTEVALUESYSTEMID], [ATTRIBUTEVALUE], [ACTIVE],[DATECHANGED])
      exec sp_executesql @SQL, N'@ATTRIBUTEID uniqueidentifier', @ATTRIBUTEID=@ATTRIBUTEID;

    fetch next from ATTRIBUTECURSOR into @ATTRIBUTEID, @CODETABLENAME;
  end
  close ATTRIBUTECURSOR;
  deallocate ATTRIBUTECURSOR;

  ------


  --Default row for all attributes

  select 
    ac.[ID] [ATTRIBUTESYSTEMID],
    av.[ATTRIBUTEVALUESYSTEMID] [ATTRIBUTEVALUESYSTEMID],
    isnull(agc.[DESCRIPTION],'No Attribute Group') [ATTRIBUTEGROUP],
    rt.[NAME] [ATTRIBUTETYPE],
    ac.[NAME] [ATTRIBUTENAME], 
    ac.[DATATYPE] [ATTRIBUTEDATATYPE], 
    av.[ATTRIBUTEVALUE] [ATTRIBUTEVALUE], 
    cast(0 as bit) [ATTRIBUTEVALUEACTIVE],
    ac.[ONLYALLOWONEPERRECORD] [ATTRIBUTEONLYALLOWONEPERRECORD],
    cast(case when ac.[DATATYPECODE] in (5, 4) then 1 else 0 end as bit) [ATTRIBUTEHASCODETABLE],
    cast(case when o.[ID] is not null then 1 else 0 end as bit) [ATTRIBUTEFORREPORTING]
  from dbo.[ATTRIBUTECATEGORY] ac  
  inner join dbo.[ATTRIBUTERECORDTYPE] art on ac.[ATTRIBUTERECORDTYPEID] = art.[ID]
  inner join dbo.[RECORDTYPE] rt on art.[RECORDTYPEID] = rt.[ID]
  left join dbo.[ATTRIBUTEGROUPCODE] agc on ac.[ATTRIBUTEGROUPCODEID] = agc.[ID]
  left join dbo.[OLAPDATASOURCEATTRIBUTECATEGORY] o on ac.[ID] = o.[ATTRIBUTECATEGORYID] and o.[OLAPDATASOURCEID] = @OLAPDATASOURCEID
  cross join (
    select cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier) [ATTRIBUTEVALUESYSTEMID], 'No Value' [ATTRIBUTEVALUE]
  ) as [av]
  where
    (ac.[DATECHANGED] > @OPENWINDOW and ac.[DATECHANGED] <= @CLOSEWINDOW) or
    (agc.[DATECHANGED] > @OPENWINDOW and agc.[DATECHANGED] <= @CLOSEWINDOW) or
    (rt.[DATECHANGED] > @OPENWINDOW and rt.[DATECHANGED] <= @CLOSEWINDOW) or
    (o.[DATECHANGED] > @OPENWINDOW and o.[DATECHANGED] <= @CLOSEWINDOW)
  union all
  --All attribute value rows

  select 
    ac.[ID] [ATTRIBUTESYSTEMID],
    av.[ATTRIBUTEVALUESYSTEMID] [ATTRIBUTEVALUESYSTEMID],
    isnull(agc.[DESCRIPTION],'No Attribute Group') [ATTRIBUTEGROUP],
    rt.[NAME] [ATTRIBUTETYPE],
    ac.[NAME] [ATTRIBUTENAME], 
    ac.[DATATYPE] [ATTRIBUTEDATATYPE], 
    av.[ATTRIBUTEVALUE] [ATTRIBUTEVALUE], 
    av.[ACTIVE] [ATTRIBUTEVALUE],
    ac.[ONLYALLOWONEPERRECORD] [ATTRIBUTEONLYALLOWONEPERRECORD],
    cast(1 as bit) [ATTRIBUTEHASCODETABLE],
    cast(case when o.[ID] is not null then 1 else 0 end as bit) [ATTRIBUTEFORREPORTING]
  from dbo.[ATTRIBUTECATEGORY] ac
  inner join @ATTRIBUTEVALUES av on ac.[ID] = av.[ATTRIBUTESYSTEMID]
  inner join dbo.[ATTRIBUTERECORDTYPE] art on ac.[ATTRIBUTERECORDTYPEID] = art.[ID]
  inner join dbo.[RECORDTYPE] rt on art.[RECORDTYPEID] = rt.[ID]
  left join dbo.[ATTRIBUTEGROUPCODE] agc on ac.[ATTRIBUTEGROUPCODEID] = agc.[ID]
  left join dbo.[OLAPDATASOURCEATTRIBUTECATEGORY] o on ac.[ID] = o.[ATTRIBUTECATEGORYID] and o.[OLAPDATASOURCEID] = @OLAPDATASOURCEID
  where
    (ac.[DATECHANGED] > @OPENWINDOW and ac.[DATECHANGED] <= @CLOSEWINDOW) or
    (av.[DATECHANGED] > @OPENWINDOW and av.[DATECHANGED] <= @CLOSEWINDOW) or
    (agc.[DATECHANGED] > @OPENWINDOW and agc.[DATECHANGED] <= @CLOSEWINDOW) or
    (rt.[DATECHANGED] > @OPENWINDOW and rt.[DATECHANGED] <= @CLOSEWINDOW) or
    (o.[DATECHANGED] > @OPENWINDOW and o.[DATECHANGED] <= @CLOSEWINDOW)
  union all
  --All boolean rows (Yes/No)

  select 
    ac.[ID] [ATTRIBUTESYSTEMID],
    av.[ATTRIBUTEVALUESYSTEMID] [ATTRIBUTEVALUESYSTEMID],
    isnull(agc.[DESCRIPTION],'No Attribute Group') [ATTRIBUTEGROUP],
    rt.[NAME] [ATTRIBUTETYPE],
    ac.[NAME] [ATTRIBUTENAME], 
    ac.[DATATYPE] [ATTRIBUTEDATATYPE], 
    av.[ATTRIBUTEVALUE] [ATTRIBUTEVALUE], 
    cast(1 as bit) [ATTRIBUTEVALUEACTIVE],
    ac.[ONLYALLOWONEPERRECORD] [ATTRIBUTEONLYALLOWONEPERRECORD],
    cast(1 as bit) [ATTRIBUTEHASCODETABLE],
    cast(case when o.[ID] is not null then 1 else 0 end as bit) [ATTRIBUTEFORREPORTING]
  from dbo.[ATTRIBUTECATEGORY] ac
  inner join dbo.[ATTRIBUTERECORDTYPE] art on ac.[ATTRIBUTERECORDTYPEID] = art.[ID]
  inner join dbo.[RECORDTYPE] rt on art.[RECORDTYPEID] = rt.[ID]
  left join dbo.[ATTRIBUTEGROUPCODE] agc on ac.[ATTRIBUTEGROUPCODEID] = agc.[ID]
  left join dbo.[OLAPDATASOURCEATTRIBUTECATEGORY] o on ac.[ID] = o.[ATTRIBUTECATEGORYID] and o.[OLAPDATASOURCEID] = @OLAPDATASOURCEID
  cross join (
    select cast('00000000-0000-0000-0000-000000000001' as uniqueidentifier) [ATTRIBUTEVALUESYSTEMID], cast('No' as nvarchar(100)) [ATTRIBUTEVALUE] union all
    select cast('00000000-0000-0000-0000-000000000002' as uniqueidentifier), cast('Yes' as nvarchar(100))
  ) as [av]
  where 
    ac.[DATATYPECODE] = 4 and
    (
      (ac.[DATECHANGED] > @OPENWINDOW and ac.[DATECHANGED] <= @CLOSEWINDOW) or
      (agc.[DATECHANGED] > @OPENWINDOW and agc.[DATECHANGED] <= @CLOSEWINDOW) or
      (rt.[DATECHANGED] > @OPENWINDOW and rt.[DATECHANGED] <= @CLOSEWINDOW) or
      (o.[DATECHANGED] > @OPENWINDOW and o.[DATECHANGED] <= @CLOSEWINDOW)
    )