Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- größte Dateien mit Download-History
- select
- x.*, y.ZeitpunktLastDownload, y.AnzahlDownload
- from (
- select top 1000
- b.Name as Bereich, f.Name + '.' + t.Name as Name,
- f.Size / 1024.0 / 1024.0 as SizeInMb, f.DatumErstell, f.IdFile
- from TFile f
- join TFileTypFile t
- on t.TypFile = f.TypFile
- join TFileTypBereich b
- on b.TypBereich = f.TypBereich
- order by
- f.Size desc
- ) x
- left join (
- select
- IdFile, max(Zeitpunkt) as ZeitpunktLastDownload, count(*) as AnzahlDownload
- from TFileLog
- where
- Info = 'Download OK'
- group by
- IdFile
- ) y
- on y.IdFile = x.IdFile
- order by
- x.SizeInMb desc
- -- größte Dateien mit Download-History
- select
- x.*, y.ZeitpunktLastDownload, y.AnzahlDownload
- from (
- select top 1000
- b.Name as Bereich, f.Name + '.' + t.Name as Name,
- f.Size / 1024.0 / 1024.0 as SizeInMb, f.DatumErstell, f.IdFile
- from TFile f
- join TFileTypFile t
- on t.TypFile = f.TypFile
- join TFileTypBereich b
- on b.TypBereich = f.TypBereich
- where
- f.Size / 1024.0 / 1024.0 > 0.5
- order by
- f.IdFile desc
- ) x
- left join (
- select
- IdFile, max(Zeitpunkt) as ZeitpunktLastDownload, count(*) as AnzahlDownload
- from TFileLog
- where
- Info = 'Download OK'
- group by
- IdFile
- ) y
- on y.IdFile = x.IdFile
- order by
- x.IdFile desc
- -- hinzukommende Dateien pro Tag:
- select top 300
- cast(DatumErstell as date) as Datum, sum(Size / 1024.0 / 1024.0) as SizeInMb
- from TFile
- group by
- cast(DatumErstell as date)
- order by
- 1 desc
- -- hinzukommende Dateien pro Monat: seit 2011 mind. 2 GB pro Monat
- select
- year(DatumErstell) as Jahr, month(DatumErstell) as Monat, sum(Size / 1024.0 / 1024.0) as SizeInMb
- from TFile
- group by
- year(DatumErstell), month(DatumErstell)
- order by
- 1 desc, 2 desc
Add Comment
Please, Sign In to add comment