SQL - query multiple archive-databases
Hi all
I'm a bloody beginner with sql-code.
What I want to know is to the amount of archived data for last night (22:00-06:00) and the night before. Both for two different archives.
My code is the following:
(SELECT "date" = '2018-02-17', "MB mailarch1" = sum (originalsize)/1024/1024,"MB mailarch2"=''
from EVVSVSArchiveMail1.dbo.Saveset with (nolock) inner join EVVSVSArchiveMail1.dbo.savesetproperty with (nolock) ON saveset.savesetidentity = savesetproperty.savesetidentity
WHERE archiveddate > '2018-02-17 20:00' and archiveddate < '2018-02-18 06:00'
union
SELECT "date" = '2018-02-18',"MB (orig) mailarch1" = sum (originalsize)/1024/1024,"MB (orig) mailarch2"=''
from EVVSVSArchiveMail1.dbo.Saveset with (nolock) inner join EVVSVSArchiveMail1.dbo.savesetproperty with (nolock) ON saveset.savesetidentity = savesetproperty.savesetidentity
WHERE archiveddate > '2018-02-18 20:00' and archiveddate < '2018-02-19 06:00')
UNION ALL
(SELECT "date" = '2018-02-17',"MB (orig) mailarch1"='',"MB mailarch2" = sum (originalsize)/1024/1024
from EVVSVSArchiveMail2.dbo.saveset with (nolock) inner join EVVSVSArchiveMail2.dbo.savesetproperty with (nolock) ON saveset.savesetidentity = savesetproperty.savesetidentity
WHERE archiveddate > '2018-02-17 20:00' and archiveddate < '2018-02-18 06:00'
union
SELECT "date" = '2018-02-18',"MB (orig) mailarch1"='',"MB mailarch2" = sum (originalsize)/1024/1024
from EVVSVSArchiveMail2.dbo.saveset with (nolock) inner join EVVSVSArchiveMail2.dbo.savesetproperty with (nolock) ON saveset.savesetidentity = savesetproperty.savesetidentity
WHERE archiveddate > '2018-02-18 20:00' and archiveddate < '2018-02-19 06:00')
The output I want is a table like:
date MB mailarchive1 MB mailarchive2
2018-02-17 8556 9234
2018-02-18 9432 9834
I am sure there is a much more elegant and straightforward query, but I am not able to improve it.
Has anyone of you scripted a similar thing and could let me know?
Thanks a lot,
Mike
Mike,
Give this one a try. Its output is not exactly how you asked for, but it is more flexible and provides totals by archive and by date. Include as many days/archives as you want by editing the WHERE clause.
SELECT CASE WHEN GROUPING(CAST(CAST(ss.ArchivedDate AS DATE) AS varchar(10))) = 1 THEN 'All Dates' ELSE CAST(CAST(ss.ArchivedDate AS DATE) AS varchar(10)) END AS 'Archived Date' ,CASE WHEN GROUPING(av.ArchiveName) = 1 THEN 'All Archives' ELSE av.ArchiveName END AS 'Archive Name' ,SUM(ss.ItemSize) AS [Total Size (KB)] ,(SUM(CAST(ss.ItemSize AS DECIMAL) / 1024)) AS [Total Size (MB)] ,(SUM(CAST(ss.ItemSize AS DECIMAL) / 1024 / 1024)) AS [Total Size (GB)] FROM Saveset AS ss JOIN ArchivePoint AS ap ON ap.ArchivePointIdentity = ss.ArchivePointIdentity JOIN EnterpriseVaultDirectory.dbo.ArchiveView AS av ON av.VaultEntryId = ap.ArchivePointId WHERE ss.ArchivedDate > DATEADD(DAY, -2, CONVERT(DATE, GETDATE())) --Filter to two days prior to today's date --AND av.ArchiveName IN ('Name1', 'Name2') --Filter by Archive Name --AND av.VaultEntryId IN ('ArchiveID1', 'ArchiveID2') --Filter by Archive ID GROUP BY CAST(CAST(ss.ArchivedDate AS DATE) AS varchar(10)) ,av.ArchiveName WITH ROLLUPResults look like this:
--Chris
