svenw

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;