sql-server - 根据范围填充行

Question

我正在为会计部门查询。我有一个报告模板，它为我提供了标题、详细信息、总计和其他行类型。在行类型为“D”的情况下，我有一系列帐户（Start_Account/End_Account）。我正在尝试编写一个查询，该查询将与总帐数据发生冲突。我希望能够用适当的标题描述填充每一行。诚然，我很困惑，没有手动完成。

+-----------+--------------+----------+---------------+-------------+-----------+-------------------+---------------------------+
| Report_ID | Row_Sequence | Row_Type | Start_Account | End_Account | Level_Num | Reverse_Sign_Flag |    Heading_Description    |
+-----------+--------------+----------+---------------+-------------+-----------+-------------------+---------------------------+
|        06 |          001 | H        |               |             |         1 |                   | CONSTRUCTION INCOME       |
|        06 |          002 | D        |          3000 |        3099 |        10 | Y                 |                           |
|        06 |          003 | D        |          3801 |        3801 |        10 | Y                 |                           |
|        06 |          004 | T        |          3000 |        3099 |        11 | Y                 | TOTAL INCOME              |
|        06 |          005 | I        |          3801 |        3801 |         0 |                   |                           |
|        06 |          011 | H        |               |             |         1 |                   | DIRECT CONSTRUCTION COSTS |
|        06 |          012 | D        |          4000 |        4001 |        10 |                   |                           |
|        06 |          013 | D        |          4011 |        4031 |        10 |                   |                           |
|        06 |          014 | D        |          4041 |        4041 |        10 |                   |                           |
|        06 |          015 | T        |          4000 |        4099 |        11 |                   | TOTAL DIRECT EXPENSES     |
|        06 |          016 | E        |          4002 |        4002 |         0 |                   |                           |
|        06 |          017 | E        |          4032 |        4032 |         0 |                   |                           |
|        06 |          018 | E        |          4051 |        4051 |         0 |                   |                           |
|        06 |          019 | T        |          3000 |        3099 |        12 | Y                 | GROSS PROFIT FROM JOBS    |
|        06 |          020 | I        |          3801 |        3801 |         0 |                   |                           |
|        06 |          021 | I        |          4000 |        4099 |         0 |                   |                           |
|        06 |          022 | H        |               |             |         1 |                   | OVERHEAD APPLIED TO JOBS  |
|        06 |          023 | D        |          3402 |        3402 |        10 | Y                 |                           |
|        06 |          024 | D        |          3404 |        3404 |        10 | Y                 |                           |
|        06 |          025 | D        |          3417 |        3417 |        10 | Y                 |                           |
|        06 |          026 | D        |          5432 |        5432 |        10 | Y                 |                           |
|        06 |          027 | D        |          5471 |        5471 |        10 | Y                 |                           |
|        06 |          028 | D        |          5494 |        5494 |        10 | Y                 |                           |
|        06 |          029 | D        |          5495 |        5495 |        10 | Y                 |                           |
|        06 |          035 | T        |          3402 |        3402 |        12 | Y                 | ADJUST BURDEN TO ACTUAL   |
+-----------+--------------+----------+---------------+-------------+-----------+-------------------+---------------------------+

作为输出，我将拥有：

• Start_Account
• End_Account
• 标题_1
• 标题_2
• 标题_3

使用 JPG：

• Level_Num 1对应于Heading_1
• Level_Num 11对应于Heading_2
• Levl_Num 12将是Heading_3

因此，例如，对于 Start_Account = 3000，End_Account = 3099：

• Heading_1将是“建筑收入”
• Heading_2将是“总收入”
• Heading_3将是“工作的毛利润”。

现在，当我加入总帐详细信息时，我希望能够根据开始和结束帐户查找正确的标题标题。

标签： sql-servertsql

Answer 1

真的很难理解你想要达到的目标。无论如何，这是一个动态创建标题的脚本。您可能必须调整逻辑以满足您的要求，但这应该是一个很好的起点：

declare @startAccount int = 3000
declare @endAccount   int = 3099

declare @tmp table(Report_ID varchar(10), 
                   Row_Sequence varchar(10), 
                   Row_Type varchar(1), 
                   Start_Account int, 
                   End_Account int, 
                   Level_Num int, 
                   Reverse_Sign_Flag varchar(1), 
                   Heading_Description varchar(max))

insert into @tmp values      ('06','001','H',null,null,1 ,'','CONSTRUCTION INCOME')     ,('06','002','D',3000,3099,10,'Y','')   ,('06','003','D',3801,3801,10,'Y','')   ,('06','004','T',3000,3099,11,'Y','TOTAL INCOME')   ,('06','005','I',3801,3801,0 ,'','')    ,('06','011','H',null,null,1 ,'','DIRECT CONSTRUCTION COSTS')   ,('06','012','D',4000,4001,10 ,'','')   ,('06','013','D',4011,4031,10 ,'','')   ,('06','014','D',4041,4041,10 ,'','')   ,('06','015','T',4000,4099,11 ,'','TOTAL DIRECT EXPENSES')  ,('06','016','E',4002,4002,0 ,'','')    ,('06','017','E',4032,4032,0 ,'','')    ,('06','018','E',4051,4051,0 ,'','')    ,('06','019','T',3000,3099,12,'Y','GROSS PROFIT FROM JOBS')     ,('06','020','I',3801,3801,0 ,'','')    ,('06','021','I',4000,4099,0 ,'','')    ,('06','022','H',NULL,NULL,1 ,'','OVERHEAD APPLIED TO JOBS')    ,('06','023','D',3402,3402,10 ,'Y','')  ,('06','024','D',3404,3404,10 ,'Y','')  ,('06','025','D',3417,3417,10 ,'Y','')  ,('06','026','D',5432,5432,10 ,'Y','')  ,('06','027','D',5471,5471,10 ,'Y','')  ,('06','028','D',5494,5494,10 ,'Y','')  ,('06','029','D',5495,5495,10 ,'Y','')  ,('06','035','T',3402,3402,12 ,'Y','ADJUST BURDEN TO ACTUAL')

DECLARE @Heading_1 varchar(100)
      , @Heading_2 varchar(100)
      , @Heading_3 varchar(100)
      , @minRowSequence varchar(10)

select @minRowSequence = min(Row_Sequence) 
from @tmp 
where Start_Account=@startAccount and End_Account=@endAccount 

select @Heading_1 = Heading_Description 
from @tmp 
where isnull(Start_Account,'')='' 
    and isnull(End_Account,'')='' 
    and Level_Num=1 
    and Row_Sequence < @minRowSequence

select @Heading_2 = Heading_Description 
from @tmp 
where Start_Account=@startAccount 
    and End_Account=@endAccount 
    and Level_Num=11

select @Heading_3 = Heading_Description 
from @tmp 
where Start_Account=@startAccount 
    and End_Account=@endAccount 
    and Level_Num=12

DECLARE @sql nvarchar(max) = ''
set @sql = @sql + 'select ' + cast(@startAccount as varchar(max)) + ' AS Start_Account, '
set @sql = @sql + cast(@endAccount as varchar(max)) +' AS End_Account, '  
set @sql = @sql + '''' +  @Heading_1 + ''' AS Heading_1,  '
set @sql = @sql + '''' +  @Heading_2 + ''' AS Heading_2,  '
set @sql = @sql + '''' +  @Heading_3 + ''' AS Heading_3 '

exec(@sql)

输出：