P
Sorry war leider kurzfristig unterwegs, zurück zum Thema.
Ja das alles in temporäre Tabellen zu machen ist durchaus sinnvoll hatte ich auch geplant, nur die ersten Versuche funktionierten noch nicht mit temp Tabellen ich muss noch ein wenig versuchen, denn ich erstelle ja die Splaten anhand eines Selects, Sprich ich muss Exec auf nen Text machen, dass ich meine Spalten die ich in ner varchar Variable hab hinzufügen kann. Wenn ich das hinbekomme dann kann ich es so machen. (Da sind halt noch paar nicht relevante Sachen drin.
declare @CostCentre varchar(4)
set @CostCentre = '5040'
-- Projects as Columns
declare @Name varchar(250), @SQL varchar(4000), @Columns varchar(4000), @ColumnNames varchar(4000)
Set @Columns = ''
Set @ColumnNames = ''
declare UserCursor CURSOR
for Select Name from dbo.Projects
open UserCursor
fetch UserCursor into @Name
while (@@fetch_status =0) Begin
set @Columns = @Columns + ', [' + @Name + '] varchar(250)'
set @ColumnNames = @ColumnNames + ', [' + @Name + ']'
fetch UserCursor into @Name
end
close UserCursor
deallocate UserCursor
Set @Columns = right(@Columns,len(@Columns) -1)
Set @ColumnNames = right(@ColumnNames,len(@ColumnNames) -1)
set @SQL = 'create table #temp(dynID int Identity(1,1), [Employee] varchar(50) NOT NULL, ' + @Columns + ' ,[Sum] int, [WorkLoad] float)'
exec (@SQL)
declare @Employee varchar(50)
declare EmployeeCursor CURSOR
for Select ANSID from vwEmployees
open EmployeeCursor
fetch EmployeeCursor into @Employee
while (@@fetch_status =0) Begin
insert into #temp (Employee) Values (@Employee)
declare @Project varchar(250), @pBudget int
declare ProjectCursor CURSOR
for Select Projects.Name, PlanedPersonnelProjectBudget from EmployeesInProjects eIp join Projects on Projects.ID = eIp.Project where Employee = (Select ID from Employees where ANSID = @Employee)
open ProjectCursor
declare @BudgetSum int, @PlanedBudget float, @Workload float
Set @PlanedBudget = isNull((Select PlanedPersonnelBudget from vwEmployees where ANSID = @Employee),240000)
set @BudgetSum = 0
fetch ProjectCursor into @Project, @pBudget
while (@@fetch_status =0) Begin
declare @update varchar(250)
set @update = 'update #temp Set ['+ @Project + '] = ' + Convert(varchar(50),@pBudget) + ' where Employee = ''' + @Employee + ''''
print @update
Exec(@update)
set @update = 'update #temp Set ['+ @Project + '] = ' + Convert(varchar(50),(Select PlanedProjectBudget from Projects where Name = @Project)) + ' where Employee = ''Total Budget'''
print @update
Exec(@update)
-- set @update = 'update #temp Set ['+ @Project + '] = ' + Convert(varchar(50),@pBudget) + ' where Employee = Total Budget'
-- print @update
-- Exec(@update)
Set @BudgetSum = isNull(@BudgetSum,0) + isNull(@pBudget,0)
fetch ProjectCursor into @Project, @pBudget
end
close ProjectCursor
deallocate ProjectCursor
Set @Workload = round((@BudgetSum / (isNull(@PlanedBudget,240000))) * 100,2)
declare @test varchar(1000)
Set @test = 'update #temp Set [Sum] = '+ Convert(varchar(50),@BudgetSum) +', [WorkLoad] = ' + Convert(varchar(50),@Workload) + ' where Employee = ''' + @Employee + ''''
Exec(@test)
fetch EmployeeCursor into @Employee
end
close EmployeeCursor
deallocate EmployeeCursor
declare @ColumnList nvarchar(4000)
Set @ColumnList = ''
Set @SQL = 'Select ANSID, ' + @ColumnNames + ' from SQLKappa._dynProjects proj right join vwEmployees on vwEmployees.ANSID = Employee where vwEmployees.CostCentre = ' + Convert(varchar(50),@CostCentre) + ' or CostCentre = 0 order by ID,Nachname'
Print @SQL
Exec(@SQL)
go
Achja und ich finde es deutlich es direkt auf dem SQL Server zu machen wie in meiner Webapplikation.