cancel
Showing results for 
Search instead for 
Did you mean: 

SQL - query multiple archive-databases

dummyscripter
Level 2

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

1 ACCEPTED SOLUTION

Accepted Solutions

ChrisLangevin
Level 6
Employee

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 ROLLUP

 

Results look like this:

Capture.JPG

 

--Chris

 

View solution in original post

4 REPLIES 4

ChrisLangevin
Level 6
Employee

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 ROLLUP

 

Results look like this:

Capture.JPG

 

--Chris

 

Hi Chris

Wow, thats an impressive script. Thank you very much!
It is useful to me indeed.

However, my question was obviously not accurate enough .... I apologize.

I want to know the total amount of archived date within two different databases.
The DBs are named 'EVVSVSArchiveMail1' and 'EVVSVSArchiveMail2'.
I want it to know for two different days.
The output should look like:

23-02-_2018_11-13-17.jpg

Of course on two rows only, but my stupid script does four rows.

May I ask anybody to assist me? 
And again, sorry for this stupid question Smiley Frustrated

Mike

 

 

G_Fry
Level 4
Partner Accredited

Your script seems to give you want you need?  what improvement are you expecting or want?

Realise the value in Enterprise Vault. ->www.cloudficient.com

Hi G_Fry

Thanks for your answer.

I was able to improve my script, problem solved.

Regards, 
Mike