Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Bookmark
- Permalink
- Email to a Friend
- Report Inappropriate Content
Hello,
I am looking to build SQL query to see Cluster's Used, Unused and Allocated capacity from past 7 weeks. One pointer for each week.
I am able to generate for current date from view "report_storage_summary_aux" but dont know how to use fact tables.
SELECT storage_name, storage_pool_used_capacity FROM report_storage_summary_aux WHERE storage_name LIKE 'Cluster1' AND storage_pool_used_capacity <> 0 OR storage_name LIKE 'Cluster2' AND storage_pool_used_capacity <> 0
Thanks
Gaurav
Solved! See The Solution
1 REPLY 1
- Bookmark
- Permalink
- Email to a Friend
- Report Inappropriate Content
For others visibility. This query works for me.
SELECT DISTINCT STORAGE.name, STORAGE.dataCenter AS DC, STORAGE.family, DATEDIM.fullDate AS Date,sum(STORAGE_FACT.CapacityMB) AS CapacityMB, sum(STORAGE_FACT.usedCapacityMB) AS usedCapacityMB FROM storage_dimension STORAGE, date_dimension DATEDIM, storage_and_storage_pool_capacity_fact STORAGE_FACT WHERE STORAGE_FACT.storageTk=STORAGE.tk AND STORAGE.dataCenter IN('DC1','DC2','DC3') AND DATEDIM.tk=STORAGE_FACT.dateTk AND DATEDIM.fullDate>(DATE_ADD(CURDATE(),INTERVAL -30 DAY)) AND GROUP BY STORAGE.name, DATEDIM.fullDate
Thanks