Moin,
kann mir jemand sagen ob ich dieses Script einfacher hinbekomme?
declare @Liste as nvarchar(max)
declare @aListe as nvarchar(150)
declare @bListe as nvarchar(150)
declare @value as nvarchar(150)
declare @SQL_Liste as nvarchar(max)
declare @SQL_Mitte as nvarchar(max)
declare @SQL_Anfang as nvarchar(max)
declare @SQL_Ende as nvarchar(max)
declare @date as nvarchar(150)
select @date = '12.08.2015'
drop table ##test
SET @Liste = ''
SET @aListe = ' Select @Liste = @Liste + '
SET @bListe = ' from (select distinct value from dbo.test where [test1] = ''muster'') v'
SET @value = ' + [value] + '
SET @SQL_Anfang = 'WITH WeekDays AS (SELECT DATEADD(ww, DATEDIFF(ww, 0, '''+ @date +''') - (DATEPART(wk, '''+ @date +''') - DATEPART(isowk, '''+ @date +''')), 0) DayValue UNION ALL SELECT DayValue + 1 FROM WeekDays WHERE DayValue + 1 <= DATEADD(ww, DATEDIFF(ww, 0, '''+ @date +''') - (DATEPART(wk, '''+ @date +''' ) - DATEPART(isowk, '''+ @date +''')), 6)) SELECT convert (nvarchar ,DayValue,104) as Datum, Datename(weekday,DayValue)as Wochentag ,'
SET @SQL_liste = @aListe + '''Case '''''''+ @value + '''''''when '''''''+ @value + ''''''' then (select count([Test3]) FROM [dbo].[test] where [test1] = '''''''+ @value + '''''''and [CreatedOn] >= DayValue and [CreatedOn] <= DayValue + 1) else 0 end as '''''''+ @value + ''''''',''' + @bListe
SET @SQL_Mitte = ''
EXEC sp_executesql @SQL_Liste, N'@Liste nvarchar(max) OUTPUT', @Liste = @SQL_Mitte OUTPUT
SET @SQL_Ende = 'DATEPART(isowk, '''+ @date +''') AS KW into ##Ausgabe FROM WeekDays'
EXEC (@SQL_Anfang + @SQL_Mitte + @SQL_Ende)
select * from ##Ausgabe;
