USP_BBDW_ATTRIBUTE_GETFACTVALUES

create procedure dbo.[USP_BBDW_ATTRIBUTE_GETFACTVALUES](
  @ATTRIBUTEID uniqueidentifier,
  @FULLLOAD bit, 
  @OPENWINDOW datetime, 
  @CLOSEWINDOW datetime
) 
as 

  --This NOP is required for the SSIS development environment to be able to detect the result set column metadata.

  if 1=null
    select 
      newid() [RECORDATTRIBUTESYSTEMID],
      newid() [ATTRIBUTESYSTEMID],
      newid() [RECORDSYSTEMID], 
      newid() [ATTRIBUTEVALUESYSTEMID], 
      current_timestamp [STARTDATE],      
      current_timestamp [ENDDATE],
      cast('No Value' as nvarchar(255)) [VALUE],
      cast('No Comment' as nvarchar(255)) [COMMENT],
      cast('1/1/2000' as date) as [VALUEDATE],
      cast(1 as bit) as [VALUEYESNO],
      cast(0 as money) as [VALUECURRENCY],
      newid() as [VALUECONSTITUENTSYSTEMID],
      cast('0000' as char(4)) as [VALUEHOURMINUTE],
      cast('20000101' as char(8)) as [VALUEFUZZYDATE],
      cast(0 as int) as [VALUENUMBER]


  declare @SQL nvarchar(max);
  declare @TABLENAME nvarchar(100);
  declare @RECORDIDCOLUMNNAME nvarchar(130);
  declare @VALUECOLUMNNAME nvarchar(128);
  declare @CODETABLENAME nvarchar(100);
  declare @ONLYALLOWONEPERRECORD bit;
  declare @DATATYPE tinyint;

  select 
    @TABLENAME = t.[TABLENAME],
    @RECORDIDCOLUMNNAME = r.[BASETABLENAME] + 'ID',
    @VALUECOLUMNNAME = a.[VALUECOLUMNNAME],
    @CODETABLENAME = ct.[DBTABLENAME],
    @DATATYPE = a.[DATATYPECODE],
    @ONLYALLOWONEPERRECORD = a.[ONLYALLOWONEPERRECORD]
  from dbo.[ATTRIBUTECATEGORY] a
  inner join dbo.[TABLECATALOG] t on a.[TABLECATALOGID] = t.[ID]
  inner join dbo.[ATTRIBUTERECORDTYPE] ar on a.[ATTRIBUTERECORDTYPEID] = ar.[ID]
  inner join dbo.[RECORDTYPE] r on ar.[RECORDTYPEID] = r.[ID]
  left join dbo.[CODETABLECATALOG] ct on a.[CODETABLECATALOGID] = ct.[ID]
  where a.[ID] = @ATTRIBUTEID;

  set @sql =  'select ' + char(13) +
              '  a.[ID] [RECORDATTRIBUTESYSTEMID],' + char(13) +
              '  @ATTRIBUTEID [ATTRIBUTESYSTEMID],' + char(13);

  --Parent is foreign key on one per record attributes

  if @ONLYALLOWONEPERRECORD = 1
    set @sql += '  a.[ID] [RECORDSYSTEMID],' + char(13);
  else                
    set @sql += '  a.[' + @RECORDIDCOLUMNNAME + '] [RECORDSYSTEMID],' + char(13);


  -- Time logic

  if @DATATYPE = 8
  begin
    set @sql += '  cast(''00000000-0000-0000-0000-000000000000'' as uniqueidentifier) [ATTRIBUTEVALUESYSTEMID],' + char(13) + 
                '  a.[STARTDATE] [STARTDATE],' + char(13) + 
                '  a.[ENDDATE] [ENDDATE],' + char(13) + 
                '  cast(a.[' + @VALUECOLUMNNAME + '] as nvarchar(255)) [VALUE],' + char(13) +
                '  a.[COMMENT] [COMMENT],' + char(13) +
                '  null as [VALUEDATE], ' + char(13) +
                '  null as [VALUEYESNO], ' + char(13) +
                '  null as [VALUECURRENCY], ' + char(13) +
                '  cast(''00000000-0000-0000-0000-000000000000'' as uniqueidentifier) as [VALUECONSTITUENTSYSTEMID], ' + char(13) +
                '  a.[' + @VALUECOLUMNNAME + '] as [VALUEHOURMINUTE], ' + char(13) +
                '  null as [VALUEFUZZYDATE], ' + char(13) +
                '  null as [VALUENUMBER] ' + char(13) +
                'from dbo.[' + @TABLENAME + '] a' + char(13);

    if @FULLLOAD = 0
    begin
      set @sql += 'where' + char(13) +
                  '  (a.[DATEADDED] > @OPENWINDOW and a.[DATEADDED] <= @CLOSEWINDOW) or' + char(13) +
                  '  (a.[DATECHANGED] > @OPENWINDOW and a.[DATECHANGED] <= @CLOSEWINDOW);';
    end         
  end   
  -- Fuzzy date logic

  else if @DATATYPE = 7
  begin
    set @sql += '  cast(''00000000-0000-0000-0000-000000000000'' as uniqueidentifier) [ATTRIBUTEVALUESYSTEMID],' + char(13) + 
                '  a.[STARTDATE] [STARTDATE],' + char(13) + 
                '  a.[ENDDATE] [ENDDATE],' + char(13) + 
                '  cast(a.[' + @VALUECOLUMNNAME + '] as nvarchar(255)) [VALUE],' + char(13) +
                '  a.[COMMENT] [COMMENT],' + char(13) +
                '  null as [VALUEDATE], ' + char(13) +
                '  null as [VALUEYESNO], ' + char(13) +
                '  null as [VALUECURRENCY], ' + char(13) +
                '  cast(''00000000-0000-0000-0000-000000000000'' as uniqueidentifier) as [VALUECONSTITUENTSYSTEMID], ' + char(13) +
                '  null as [VALUEHOURMINUTE], ' + char(13) +
                '  a.[' + @VALUECOLUMNNAME + '] as [VALUEFUZZYDATE], ' + char(13) +
                '  null as [VALUENUMBER] ' + char(13) +
                'from dbo.[' + @TABLENAME + '] a' + char(13);

    if @FULLLOAD = 0
    begin
      set @sql += 'where' + char(13) +
                  '  (a.[DATEADDED] > @OPENWINDOW and a.[DATEADDED] <= @CLOSEWINDOW) or' + char(13) +
                  '  (a.[DATECHANGED] > @OPENWINDOW and a.[DATECHANGED] <= @CLOSEWINDOW);';
    end         
  end    
  -- Constituent record logic

  else if @DATATYPE = 6
  begin
    set @sql += '  cast(''00000000-0000-0000-0000-000000000000'' as uniqueidentifier) [ATTRIBUTEVALUESYSTEMID],' + char(13) + 
                '  a.[STARTDATE] [STARTDATE],' + char(13) + 
                '  a.[ENDDATE] [ENDDATE],' + char(13) + 
                '  cast(a.[' + @VALUECOLUMNNAME + '] as nvarchar(255)) [VALUE],' + char(13) +
                '  a.[COMMENT] [COMMENT],' + char(13) +
                '  null as [VALUEDATE], ' + char(13) +
                '  null as [VALUEYESNO], ' + char(13) +
                '  null as [VALUECURRENCY], ' + char(13) +
                '  a.[' + @VALUECOLUMNNAME + ']  as [VALUECONSTITUENTSYSTEMID], ' + char(13) +
                '  null as [VALUEHOURMINUTE], ' + char(13) +
                '  null as [VALUEFUZZYDATE], ' + char(13) +
                '  null as [VALUENUMBER] ' + char(13) +
                'from dbo.[' + @TABLENAME + '] a' + char(13);

    if @FULLLOAD = 0
    begin
      set @sql += 'where' + char(13) +
                  '  (a.[DATEADDED] > @OPENWINDOW and a.[DATEADDED] <= @CLOSEWINDOW) or' + char(13) +
                  '  (a.[DATECHANGED] > @OPENWINDOW and a.[DATECHANGED] <= @CLOSEWINDOW);';
    end         
  end
  --Code table logic

  else if @DATATYPE = 5
  begin
    set @sql += '  ct.[ID] [ATTRIBUTEVALUESYSTEMID],' + char(13) + 
                '  a.[STARTDATE] [STARTDATE],' + char(13) + 
                '  a.[ENDDATE] [ENDDATE],' + char(13) + 
                '  ct.[DESCRIPTION] [VALUE],' + char(13) +
                '  a.[COMMENT] [COMMENT],' + char(13) +
                '  null as [VALUEDATE], ' + char(13) +
                '  null as [VALUEYESNO], ' + char(13) +
                '  null as [VALUECURRENCY], ' + char(13) +
                '  cast(''00000000-0000-0000-0000-000000000000'' as uniqueidentifier) as [VALUECONSTITUENTSYSTEMID], ' + char(13) +
                '  null as [VALUEHOURMINUTE], ' + char(13) +
                '  null as [VALUEFUZZYDATE], ' + char(13) +
                '  null as [VALUENUMBER] ' + char(13) +
                'from dbo.[' + @TABLENAME + '] a' + char(13) + 
                'inner join dbo.[' + @CODETABLENAME + '] ct on a.[' + @VALUECOLUMNNAME + '] = ct.[ID]' + char(13);

    if @FULLLOAD = 0
    begin
      set @sql += 'where' + char(13) +                
                  '  (a.[DATEADDED] > @OPENWINDOW and a.[DATEADDED] <= @CLOSEWINDOW) or' + char(13) +
                  '  (a.[DATECHANGED] > @OPENWINDOW and a.[DATECHANGED] <= @CLOSEWINDOW) or' + char(13) +
                  '  (ct.[DATECHANGED] > @OPENWINDOW and ct.[DATECHANGED] <= @CLOSEWINDOW);';
    end
  end
  --Yes/No logic

  else if @DATATYPE = 4
  begin
    set @sql += '  cast(case when a.[' + @VALUECOLUMNNAME + '] = 0 then ''00000000-0000-0000-0000-000000000001'' when a.[' + @VALUECOLUMNNAME + '] = 1 then ''00000000-0000-0000-0000-000000000002'' end as uniqueidentifier) [ATTRIBUTEVALUESYSTEMID],' + char(13) + 
                '  a.[STARTDATE] [STARTDATE],' + char(13) + 
                '  a.[ENDDATE] [ENDDATE],' + char(13) + 
                '  cast(a.[' + @VALUECOLUMNNAME + '] as nvarchar(255)) [VALUE],' + char(13) +
                '  a.[COMMENT] [COMMENT],' + char(13) +
                '  null as [VALUEDATE], ' + char(13) +
                '  a.[' + @VALUECOLUMNNAME + '] as [VALUEYESNO], ' + char(13) +
                '  null as [VALUECURRENCY], ' + char(13) +
                '  cast(''00000000-0000-0000-0000-000000000000'' as uniqueidentifier) as [VALUECONSTITUENTSYSTEMID], ' + char(13) +
                '  null as [VALUEHOURMINUTE], ' + char(13) +
                '  null as [VALUEFUZZYDATE], ' + char(13) +
                '  null as [VALUENUMBER] ' + char(13) +
                'from dbo.[' + @TABLENAME + '] a' + char(13);                

    if @FULLLOAD = 0
    begin
      set @sql += 'where' + char(13) +
                  '  (a.[DATEADDED] > @OPENWINDOW and a.[DATEADDED] <= @CLOSEWINDOW) or' + char(13) +
                  '  (a.[DATECHANGED] > @OPENWINDOW and a.[DATECHANGED] <= @CLOSEWINDOW);';
    end                
  end
  --Currency

  else if @DATATYPE = 3
  begin
    set @sql += '  cast(''00000000-0000-0000-0000-000000000000'' as uniqueidentifier) [ATTRIBUTEVALUESYSTEMID],' + char(13) + 
                '  a.[STARTDATE] [STARTDATE],' + char(13) + 
                '  a.[ENDDATE] [ENDDATE],' + char(13) + 
                '  cast(a.[ORGANIZATIONVALUE] as nvarchar(255)) [VALUE],' + char(13) +
                '  a.[COMMENT] [COMMENT],' + char(13) +
                '  null as [VALUEDATE], ' + char(13) +
                '  null as [VALUEYESNO], ' + char(13) +
                '  a.[ORGANIZATIONVALUE] as [VALUECURRENCY], ' + char(13) +
                '  cast(''00000000-0000-0000-0000-000000000000'' as uniqueidentifier) as [VALUECONSTITUENTSYSTEMID], ' + char(13) +
                '  null as [VALUEHOURMINUTE], ' + char(13) +
                '  null as [VALUEFUZZYDATE], ' + char(13) +
                '  null as [VALUENUMBER] ' + char(13) +
                'from dbo.[' + @TABLENAME + '] a' + char(13);

    if @FULLLOAD = 0
    begin
      set @sql += 'where' + char(13) +
                  '  (a.[DATEADDED] > @OPENWINDOW and a.[DATEADDED] <= @CLOSEWINDOW) or' + char(13) +
                  '  (a.[DATECHANGED] > @OPENWINDOW and a.[DATECHANGED] <= @CLOSEWINDOW);';
    end                 
  end   
  -- Date logic

  else if @DATATYPE = 2
  begin
    set @sql += '  cast(''00000000-0000-0000-0000-000000000000'' as uniqueidentifier) [ATTRIBUTEVALUESYSTEMID],' + char(13) + 
                '  a.[STARTDATE] [STARTDATE],' + char(13) + 
                '  a.[ENDDATE] [ENDDATE],' + char(13) + 
                '  cast(a.[' + @VALUECOLUMNNAME + '] as nvarchar(255)) [VALUE],' + char(13) +
                '  a.[COMMENT] [COMMENT],' + char(13) +
                '  a.[' + @VALUECOLUMNNAME + '] as [VALUEDATE], ' + char(13) +
                '  null as [VALUEYESNO], ' + char(13) +
                '  null as [VALUECURRENCY], ' + char(13) +
                '  cast(''00000000-0000-0000-0000-000000000000'' as uniqueidentifier) as [VALUECONSTITUENTSYSTEMID], ' + char(13) +
                '  null as [VALUEHOURMINUTE], ' + char(13) +
                '  null as [VALUEFUZZYDATE], ' + char(13) +
                '  null as [VALUENUMBER] ' + char(13) +
                'from dbo.[' + @TABLENAME + '] a' + char(13);

    if @FULLLOAD = 0
    begin
      set @sql += 'where' + char(13) +
                  '  (a.[DATEADDED] > @OPENWINDOW and a.[DATEADDED] <= @CLOSEWINDOW) or' + char(13) +
                  '  (a.[DATECHANGED] > @OPENWINDOW and a.[DATECHANGED] <= @CLOSEWINDOW);';
    end         
  end
  -- Number logic

  else if @DATATYPE = 1
  begin
    set @sql += '  cast(''00000000-0000-0000-0000-000000000000'' as uniqueidentifier) [ATTRIBUTEVALUESYSTEMID],' + char(13) + 
                '  a.[STARTDATE] [STARTDATE],' + char(13) + 
                '  a.[ENDDATE] [ENDDATE],' + char(13) + 
                '  cast(a.[' + @VALUECOLUMNNAME + '] as nvarchar(255)) [VALUE],' + char(13) +
                '  a.[COMMENT] [COMMENT],' + char(13) +
                '  null as [VALUEDATE], ' + char(13) +
                '  null as [VALUEYESNO], ' + char(13) +
                '  null as [VALUECURRENCY], ' + char(13) +
                '  cast(''00000000-0000-0000-0000-000000000000'' as uniqueidentifier) as [VALUECONSTITUENTSYSTEMID], ' + char(13) +
                '  null as [VALUEHOURMINUTE], ' + char(13) +
                '  null as [VALUEFUZZYDATE], ' + char(13) +
                '  a.[' + @VALUECOLUMNNAME + '] as [VALUENUMBER] ' + char(13) +
                'from dbo.[' + @TABLENAME + '] a' + char(13);

    if @FULLLOAD = 0
    begin
      set @sql += 'where' + char(13) +
                  '  (a.[DATEADDED] > @OPENWINDOW and a.[DATEADDED] <= @CLOSEWINDOW) or' + char(13) +
                  '  (a.[DATECHANGED] > @OPENWINDOW and a.[DATECHANGED] <= @CLOSEWINDOW);';
    end         
  end
  --Everything else

  else
  begin
    set @sql += '  cast(''00000000-0000-0000-0000-000000000000'' as uniqueidentifier) [ATTRIBUTEVALUESYSTEMID],' + char(13) + 
                '  a.[STARTDATE] [STARTDATE],' + char(13) + 
                '  a.[ENDDATE] [ENDDATE],' + char(13) + 
                '  cast(a.[' + @VALUECOLUMNNAME + '] as nvarchar(255)) [VALUE],' + char(13) +
                '  a.[COMMENT] [COMMENT],' + char(13) +
                '  null as [VALUEDATE], ' + char(13) +
                '  null as [VALUEYESNO], ' + char(13) +
                '  null as [VALUECURRENCY], ' + char(13) +
                '  cast(''00000000-0000-0000-0000-000000000000'' as uniqueidentifier) as [VALUECONSTITUENTSYSTEMID], ' + char(13) +
                '  null as [VALUEHOURMINUTE], ' + char(13) +
                '  null as [VALUEFUZZYDATE], ' + char(13) +
                '  null as [VALUENUMBER] ' + char(13) +
                'from dbo.[' + @TABLENAME + '] a' + char(13);

    if @FULLLOAD = 0
    begin
      set @sql += 'where' + char(13) +
                  '  (a.[DATEADDED] > @OPENWINDOW and a.[DATEADDED] <= @CLOSEWINDOW) or' + char(13) +
                  '  (a.[DATECHANGED] > @OPENWINDOW and a.[DATECHANGED] <= @CLOSEWINDOW);';
    end                 
  end

  --select @SQL


  exec sp_executesql @SQL, N'@ATTRIBUTEID uniqueidentifier, @OPENWINDOW datetime, @CLOSEWINDOW datetime', @ATTRIBUTEID=@ATTRIBUTEID, @OPENWINDOW=@OPENWINDOW, @CLOSEWINDOW=@CLOSEWINDOW;