nbritton2015
Newcomer
- Joined
- Oct 31, 2015
- Messages
- 2
- Reaction score
- 0
- Points
- 0
I need to report out the expiration date of files archived as part of a project to move some data from tsm to a customer.
They want the data in folders by expiration date.
This is how i thought the query should work, but i am having trouble with the dateadd part. Can someone help me out please?
SELECT a.node_name, a.class_name, a.filespace_name, a.hl_name, a.ll_name, DATE(a.archive_date) as DATE, CAST(FLOAT(SUM(ao.bfsize))/1024/1024/1024 as DEC(14,1)) as size_gb, count(ao.bfsize) as number_of_objects, ac.RETVER, DATEADD(dd,ac.RETVER,DATE(a.archive_date)) AS Expire
FROM archives a, archive_objects ao, ar_copygroups ac, nodes n
WHERE a.object_id=ao.objid and a.node_name='GPETL001' and a.class_name=ac.class_name and ac.domain_name = n.domain_name
GROUP BY a.node_name, a.class_name, DATE(a.archive_date), a.filespace_name, a.hl_name, a.ll_name, ac.RETVER
They want the data in folders by expiration date.
This is how i thought the query should work, but i am having trouble with the dateadd part. Can someone help me out please?
SELECT a.node_name, a.class_name, a.filespace_name, a.hl_name, a.ll_name, DATE(a.archive_date) as DATE, CAST(FLOAT(SUM(ao.bfsize))/1024/1024/1024 as DEC(14,1)) as size_gb, count(ao.bfsize) as number_of_objects, ac.RETVER, DATEADD(dd,ac.RETVER,DATE(a.archive_date)) AS Expire
FROM archives a, archive_objects ao, ar_copygroups ac, nodes n
WHERE a.object_id=ao.objid and a.node_name='GPETL001' and a.class_name=ac.class_name and ac.domain_name = n.domain_name
GROUP BY a.node_name, a.class_name, DATE(a.archive_date), a.filespace_name, a.hl_name, a.ll_name, ac.RETVER