Fruitful day

[sobrique]

It's been a while since I've had to do SQL.
Like, 'I messed around with it at Uni' sort of a while.
Not entirely true, because i've done some app bodging since.
But anyway.
Today's mission was to harvest info from a database, to produce a report of 'disk shares', sizes, customer and department.
The raw data is populated by a program called 'Storage Scope FLR', but because that's a generic system, it remains oblivious to our rather revolting way of doing customers and charging and stuff.

So mostly for my reference, here's about a day's work. (less usual daily interruptions, from backups failing, helpdesk calls etc.)
Pitiful really.

Use EMCStorageScopeFLR_CustomerReference;

SELECT ParentDir + Filename AS SharePath, 
       TotalActualSize/1024/1024 As TotalActualSizeMB, 
       Substring(
         Substring(ParentDir,CharIndex('$\',ParentDir)+2,20),1,
            CharIndex('\',Substring(ParentDir,CharIndex('$\',ParentDir)+2,20))-1) AS CustomerName,

       Substring(
         Substring(
           Substring(ParentDir,CharIndex('$\',ParentDir)+2,30),
             CharIndex('\',Substring(ParentDir,CharIndex('$\',ParentDir)+2,30))+1,20), 
         1, 
         CharIndex('\',    
           Substring(
             Substring(ParentDir,CharIndex('$\',ParentDir)+2,30),
               CharIndex('\',
                 Substring(ParentDir,CharIndex('$\',ParentDir)+2,20)
                )+1,30)
            ) -1
         ) AS CustomerDept

INTO #DiskUsageReport
FROM EMCStorageScopeFLR.dbo.ScannedDir 

WHERE EMCStorageScopeFLR.dbo.ScannedDir.MountPointName LIKE '%sfrug111%' 
   AND EMCStorageScopeFLR.dbo.ScannedDir.MountPointName LIKE '%fs001%' and depth = 6 
OR EMCStorageScopeFLR.dbo.ScannedDir.MountPointName LIKE '%sfrug111%' 
   AND EMCStorageScopeFLR.dbo.ScannedDir.MountPointName NOT LIKE '%fs001%' and depth = 7

ORDER BY ParentDir + Filename;

SELECT CustomerList.RefID, CustomerName, CustomerDept, Path
INTO #CustomerPathRef
FROM CustomerList JOIN PathRef ON CustomerList.RefID = PathRef.RefID;

UPDATE #DiskUsageReport
SET #DiskUsageReport.CustomerName = #CustomerPathRef.CustomerName, #DiskUsageReport.CustomerDept = #CustomerPathRef.CustomerDept
FROM #CustomerPathRef
WHERE #DiskUsageReport.SharePath = #CustomerPathRef.Path;

SELECT * FROM #DiskUsageReport
ORDER BY CustomerName;

DROP TABLE #DiskUsageReport;
DROP TABLE #CustomerPathRef;