akretschmer
Datenbank-Guru
- Beiträge
- 10.337
Worin soll der konzeptionelle Fehler den bestehen?
Das macht man in der Abfrage und nicht durch tägliches Ändern der Tabellenstruktur. Das skaliert nicht.
Follow along with the video below to see how to install our site as a web app on your home screen.
Anmerkung: This feature may not be available in some browsers.
Worin soll der konzeptionelle Fehler den bestehen?
Drop Table S_92620
DECLARE @kopf varchar(100),
@query VARCHAR(2000)
SET @kopf = (Select Case When DateName(dw
, GetDate()) = 'Montag' Then 'Saldo vom ' + Convert([nvarchar](10),DateAdd(day
, -3
, GetDate()),104) Else 'Saldo vom ' + Convert([nvarchar](10),DateAdd(day
, -1
, GetDate()),104) End
);
SET @query =
'Select
[S_92591_1].[Kontenart]
, [S_92591_1].[Saldo] as ''' + @kopf+'''
into S_92620
From [dbo].[S_92591] As [S_92591_1]
Where [S_92591_1].[wBuch_Buchung_am] = Case When DateName(dw
, GetDate()) = ''Montag'' Then Convert([nvarchar](10),DateAdd(day
, -3
, GetDate()),104) Else Convert([nvarchar](10),DateAdd(day
, -1
, GetDate()),104) End
And [S_92591_1].[wBuch_jahr] = Year(GetDate())
Order by [Kontenart] ASC'
EXEC(@query)
SALDODATUM SALDO
1.2.2017 104,67
2.2.2017 7575,6
3.2.2017 7743,98
to be continued...
Select
[S_92591_1].[wBuch_Buchung_am]
, Sum([S_92591_1].[Saldo]) As [Saldo]
From [dbo].[S_92591] As [S_92591_1]
Where [S_92591_1].[wBuch_jahr] = Year(GetDate())
Group By [S_92591_1].[wBuch_Buchung_am]
Order By [S_92591_1].[wBuch_Buchung_am] ASC
Select
Top 100 Percent [S_92591_1].[wBuch_Buchung_am]
, Sum(Case When [S_92591_1].[Kontenart] = '400' Then [S_92591_1].[Saldo] Else 0 End) As [400]
, Sum(Case When [S_92591_1].[Kontenart] = '403' Then [S_92591_1].[Saldo] Else 0 End) As [403]
, Sum(Case When [S_92591_1].[Kontenart] = '405' Then [S_92591_1].[Saldo] Else 0 End) As [405]
, Sum(Case When [S_92591_1].[Kontenart] = '406' Then [S_92591_1].[Saldo] Else 0 End) As [406]
, Sum(Case When [S_92591_1].[Kontenart] = '410' Then [S_92591_1].[Saldo] Else 0 End) As [410]
, Sum(Case When [S_92591_1].[Kontenart] = '412' Then [S_92591_1].[Saldo] Else 0 End) As [412]
, Sum(Case When [S_92591_1].[Kontenart] = '413' Then [S_92591_1].[Saldo] Else 0 End) As [413]
, Sum(Case When [S_92591_1].[Kontenart] = '414' Then [S_92591_1].[Saldo] Else 0 End) As [414]
, Sum(Case When [S_92591_1].[Kontenart] = '419' Then [S_92591_1].[Saldo] Else 0 End) As [419]
, Sum(Case When [S_92591_1].[Kontenart] = '426' Then [S_92591_1].[Saldo] Else 0 End) As [426]
, Sum(Case When [S_92591_1].[Kontenart] = '427' Then [S_92591_1].[Saldo] Else 0 End) As [427]
, Sum(Case When [S_92591_1].[Kontenart] = '430' Then [S_92591_1].[Saldo] Else 0 End) As [430]
, Sum(Case When [S_92591_1].[Kontenart] = '441' Then [S_92591_1].[Saldo] Else 0 End) As [441]
, Sum(Case When [S_92591_1].[Kontenart] = '444' Then [S_92591_1].[Saldo] Else 0 End) As [444]
, Sum(Case When [S_92591_1].[Kontenart] = '449' Then [S_92591_1].[Saldo] Else 0 End) As [449]
, Sum(Case When [S_92591_1].[Kontenart] = '450' Then [S_92591_1].[Saldo] Else 0 End) As [450]
, Sum(Case When [S_92591_1].[Kontenart] = '460' Then [S_92591_1].[Saldo] Else 0 End) As [460]
, Sum(Case When [S_92591_1].[Kontenart] = '461' Then [S_92591_1].[Saldo] Else 0 End) As [461]
, Sum(Case When [S_92591_1].[Kontenart] = '462' Then [S_92591_1].[Saldo] Else 0 End) As [462]
, Sum(Case When [S_92591_1].[Kontenart] = '464' Then [S_92591_1].[Saldo] Else 0 End) As [464]
, Sum(Case When [S_92591_1].[Kontenart] = '470' Then [S_92591_1].[Saldo] Else 0 End) As [470]
, Sum(Case When [S_92591_1].[Kontenart] = '471' Then [S_92591_1].[Saldo] Else 0 End) As [471]
, Sum(Case When [S_92591_1].[Kontenart] = '478' Then [S_92591_1].[Saldo] Else 0 End) As [478]
, Sum(Case When [S_92591_1].[Kontenart] = '480' Then [S_92591_1].[Saldo] Else 0 End) As [480]
, Sum(Case When [S_92591_1].[Kontenart] = '481' Then [S_92591_1].[Saldo] Else 0 End) As [481]
, Sum(Case When [S_92591_1].[Kontenart] = '494' Then [S_92591_1].[Saldo] Else 0 End) As [494]
, Sum(Case When [S_92591_1].[Kontenart] = '496' Then [S_92591_1].[Saldo] Else 0 End) As [496]
, Sum(Case When [S_92591_1].[Kontenart] = '499' Then [S_92591_1].[Saldo] Else 0 End) As [499]
, Sum(Case When [S_92591_1].[Kontenart] = '505' Then [S_92591_1].[Saldo] Else 0 End) As [505]
, Sum(Case When [S_92591_1].[Kontenart] = '506' Then [S_92591_1].[Saldo] Else 0 End) As [506]
, Sum(Case When [S_92591_1].[Kontenart] = '511' Then [S_92591_1].[Saldo] Else 0 End) As [511]
, Sum(Case When [S_92591_1].[Kontenart] = '518' Then [S_92591_1].[Saldo] Else 0 End) As [518]
, Sum(Case When [S_92591_1].[Kontenart] = '542' Then [S_92591_1].[Saldo] Else 0 End) As [542]
, Sum(Case When [S_92591_1].[Kontenart] = '543' Then [S_92591_1].[Saldo] Else 0 End) As [543]
, Sum(Case When [S_92591_1].[Kontenart] = '544' Then [S_92591_1].[Saldo] Else 0 End) As [544]
, Sum(Case When [S_92591_1].[Kontenart] = '552' Then [S_92591_1].[Saldo] Else 0 End) As [552]
, Sum(Case When [S_92591_1].[Kontenart] = '554' Then [S_92591_1].[Saldo] Else 0 End) As [554]
, Sum(Case When [S_92591_1].[Kontenart] = '556' Then [S_92591_1].[Saldo] Else 0 End) As [556]
, Sum(Case When [S_92591_1].[Kontenart] = '564' Then [S_92591_1].[Saldo] Else 0 End) As [564]
, Sum(Case When [S_92591_1].[Kontenart] = '565' Then [S_92591_1].[Saldo] Else 0 End) As [565]
, Sum(Case When [S_92591_1].[Kontenart] = '576' Then [S_92591_1].[Saldo] Else 0 End) As [576]
, Sum(Case When [S_92591_1].[Kontenart] = '591' Then [S_92591_1].[Saldo] Else 0 End) As [591]
, Sum(Case When [S_92591_1].[Kontenart] = '598' Then [S_92591_1].[Saldo] Else 0 End) As [598]
, Sum(Case When [S_92591_1].[Kontenart] = '630' Then [S_92591_1].[Saldo] Else 0 End) As [630]
From [dbo].[S_92591] As [S_92591_1]
Where [S_92591_1].[wBuch_jahr] = Year(GetDate())
Group By [S_92591_1].[wBuch_Buchung_am]
Order By [S_92591_1].[wBuch_Buchung_am]
DECLARE @kopf DATE,
@intAlter Varchar (50)
Set @intAlter = 'Saldo vom ' + cast(@kopf as Varchar(10))
ALTER TABLE [S_92620] ADD @intAlter VARCHAR(100)