/*
Базируется на идее Сергея Болдарева
*/
declare @AccountingCode int, @CC nvarchar(2), @TrYear nvarchar(2), @sql nvarchar(4000)
set @AccountingCode=0
set @CC=’01’
set @TrYear=’08’
declare @AAS TABLE (
AccountingCode int,
StockReceived varchar(12),
AccruedReceived varchar(12),
AccruedCosts varchar(12),
StockIssued nvarchar(12),
AccruedIssued nvarchar(12),
StockTakingShortage varchar(12),
StockTakingSurplus varchar(12),
PurchaseDifference varchar(12),
PurchaseCosts varchar(12),
Revaluation varchar(12),
IntermediateAccount varchar (12)
)
while @AccountingCode<=40
BEGIN
set @sql=’select ‘+
convert(nvarchar(2),@AccountingCode)+’ as AccountingCode,
isnull((select SC24003 from SC24’+@CC+’00 where SC24003<>»» and SC24001=»’+@TrYear+»’ and convert(int,SC24002)=’+convert(nvarchar,@AccountingCode*9+1)+’),»(’+convert(nvarchar,@AccountingCode*9+1)+’)») as StockReceived,
isnull((select SC24003 from SC24’+@CC+’00 where SC24003<>»» and SC24001=»’+@TrYear+»’ and convert(int,SC24002)=’+convert(nvarchar,@AccountingCode*9+2)+’),»(’+convert(nvarchar,@AccountingCode*9+2)+’)») as AccruedReceived,
isnull((select SC24003 from SC24’+@CC+’00 where SC24003<>»» and SC24001=»’+@TrYear+»’ and convert(int,SC24002)=’+convert(nvarchar,@AccountingCode*9+3)+’),»(’+convert(nvarchar,@AccountingCode*9+3)+’)») as AccruedCosts,
isnull((select SC24003 from SC24’+@CC+’00 where SC24003<>»» and SC24001=»’+@TrYear+»’ and convert(int,SC24002)=’+convert(nvarchar,@AccountingCode*9+4)+’),»(’+convert(nvarchar,@AccountingCode*9+4)+’)») as StockIssued,
isnull((select SC24003 from SC24’+@CC+’00 where SC24003<>»» and SC24001=»’+@TrYear+»’ and convert(int,SC24002)=’+convert(nvarchar,@AccountingCode*9+5)+’),»(’+convert(nvarchar,@AccountingCode*9+5)+’)») as AccruedIssued,
isnull((select SC24003 from SC24’+@CC+’00 where SC24003<>»» and SC24001=»’+@TrYear+»’ and convert(int,SC24002)=’+convert(nvarchar,@AccountingCode*9+6)+’),»(’+convert(nvarchar,@AccountingCode*9+6)+’)») as StockTakingShortage,
isnull((select SC24003 from SC24’+@CC+’00 where SC24003<>»» and SC24001=»’+@TrYear+»’ and convert(int,SC24002)=’+convert(nvarchar,@AccountingCode+371)+’),»(’+convert(nvarchar,@AccountingCode+371)+’)») as StockTakingSurplus,
isnull((select SC24003 from SC24’+@CC+’00 where SC24003<>»» and SC24001=»’+@TrYear+»’ and convert(int,SC24002)=’+convert(nvarchar,@AccountingCode*9+7)+’),»(’+convert(nvarchar,@AccountingCode*9+7)+’)») as PurchaseDifference,
isnull((select SC24003 from SC24’+@CC+’00 where SC24003<>»» and SC24001=»’+@TrYear+»’ and convert(int,SC24002)=’+convert(nvarchar,@AccountingCode*9+8)+’),»(’+convert(nvarchar,@AccountingCode*9+8)+’)») as PurchaseCosts,
isnull((select SC24003 from SC24’+@CC+’00 where SC24003<>»» and SC24001=»’+@TrYear+»’ and convert(int,SC24002)=’+convert(nvarchar,@AccountingCode*9+9)+’),»(’+convert(nvarchar,@AccountingCode*9+9)+’)») as Revaluation,
isnull((select SC24003 from SC24’+@CC+’00 where SC24003<>»» and SC24001=»’+@TrYear+»’ and convert(int,SC24002)=370),»(370)») as IntermediateAccount’
insert into @AAS
exec(@sql)
set @AccountingCode=@AccountingCode+1
END
select * from @AAS |
