0

Ok, so it's kind if difficult for me as a non-native english speaker to get this into writing, so please bear with me.

I have a source-Table as following:

CREATE TABLE [dbo].[Mailbox](
[ObjectSID] [nvarchar](184) NULL, --ObjectSID of Mailbox
[Database] [nvarchar](64) NULL, --Exchange Database
[PrimarySMTP] [nvarchar](254) NULL, -- eMailAddress
[ItemCount] [int] NULL, -- SUM of eMails
[Itemsize_MB] [int] NULL, -- size of Mails
[Itemsize_del_MB] [int] NULL, -- size of deleted mails
[Arch_Database] [nvarchar](64) NULL, -- Name of the Archive Exchange Database
[Arch_ItemCount] [int] NULL, -- Sum of all archived mails
[Arch_Itemsize_MB] [int] NULL, -- Size of archived mails
[Arch_Itemsize_del_MB] [int] NULL,  --Size of deleted archived mails
[ScanTime] [date] NULL  --Date of the last SCAN
) ON [PRIMARY]
GO

As you can Imagine from the Name, Ill write Exchange-Information into this Database for every Mailbox in our Company, every month.

SAMPLE-DATA

Insert into Mailbox Values 
 (111,N'Database1',N'Sample.User1@domain.com',63913,16535,1,N'ARCH1',0,0,0,'2018-10-22')
,(111,N'Database1',N'Sample.User1@domain.com',63958,16540,2,N'ARCH1',0,0,0,'2018-10-24')
,(111,N'Database1',N'Sample.User1@domain.com',64533,16664,2,N'ARCH1',0,0,0,'2018-11-19')
,(222,N'Database2',N'Sample.User2@domain.com',296,11,0,N'ARCH2',39139,10867,0,'2018-11-19')
,(222,N'Database2',N'Sample.User2@domain.com',296,11,0,N'ARCH2',39139,10867,0,'2018-10-24')
,(222,N'Database2',N'Sample.User2@domain.com',296,11,0,N'ARCH2',39139,10867,0,'2018-10-22')
,(333,N'Database1',N'Sample.User3@domain.com',55292,12723,23,N'ARCH1',37302,7128,0,'2018-10-22')
,(333,N'Database1',N'Sample.User3@domain.com',55532,12855,25,N'ARCH1',37306,7128,0,'2018-10-24')

My query results in 1 line per Scan (ScanTime) per Mailbox (ObjectSID)

WITH 
MBBB (  ObjectSID
,Itemsize_MB
,Itemsize_del_MB
,Arch_Itemsize_MB
,Arch_Itemsize_del_MB
,ScanTime
,ROW
,[Database]) 
AS (SELECT  ObjectSID
,Itemsize_MB
,Itemsize_del_MB
,Arch_Itemsize_MB
,Arch_Itemsize_del_MB
,Scantime
,ROW_NUMBER() OVER(PARTITION BY ObjectSID ORDER BY ScanTime) ROW
,[Database]
FROM Mailbox),
Growth( [Database]
,ObjectSID
,Itemsize_MB
,Itemsize_del_MB
,Arch_Itemsize_MB
,Arch_Itemsize_del_MB
,ScanTime
,Growth) 
AS (select S.[Database]
,S.ObjectSID
,S.Itemsize_MB
,S.Itemsize_del_MB
,S.Arch_Itemsize_MB
,S.Arch_Itemsize_del_MB
,S.ScanTime
,ISNULL((S.Itemsize_MB+S.Itemsize_del_MB+S.Arch_Itemsize_MB+S.Arch_Itemsize_del_MB),0)-ISNULL((X.Itemsize_MB+X.Itemsize_del_MB+X.Arch_Itemsize_MB+X.Arch_Itemsize_del_MB),0) Growth
FROM MBBB S
LEFT JOIN MBBB X 
ON S.ObjectSID=X.ObjectSID 
AND S.Row=X.Row+1
where s.ROW  >= (select MAX(s.ROW)-3 
from MBBB s)) 

select  
g.[Database]
,g.ObjectSID
,SUM(g.Itemsize_MB + g.Itemsize_del_MB + g.Arch_Itemsize_MB + g.Arch_Itemsize_del_MB) as [Mailbox in MB]
,g.Growth
,g.ScanTime
from Growth g
Group By g.ObjectSID, g.[Database], g.ScanTime ,g.Growth
order by g.[Database]


My Problem:
If a Mailbox (ObjectSID) is deleted, the new Size is 0. Therefore the Growth should be negative last size.
i.e Mailbox of User A is 12GB in size on July. The Mailbox get's deleted, and is not listed in August (since it has been deleted) I want a Growth of -12GB (negative 12GB)
BUT, since the Mailbox is deleted, it is not showing as a new row_Number(Since Mailbox = NULL == No new ROW_Number() )
The Problem comes now with grouping in SSRS:
If I group all Mailboxes per Database, I see that for a Database If have a positive growth,
but the SUM of all Mailbox-Sizes is decreasing.
i.e
Database__Old Size___Growth__New Size
Database1 __ 10GB __ _4GB____9GB

I need some kind of join, if entry in left table, and not in right, then substract from 0)
I hope this is half way understandable

Thank you all very much in advance.

  • BUT, since the Mailbox is deleted, it is not showing as a new row_Number But it MUST present - so you need to obtain all mailboxes present in source table (in a separate CTE) and use this list as a main base in the LEFT JOIN chain of the final query or of the Growth CTE. – Akina Nov 21 '18 at 16:16
  • Please update your question tags and specify what RDBMS (SQL Server, Oracle, etc.) and version (SQL2008, SQL2012, etc.) you are using. Also, based on your sample data, what should the results be? – Scott Hodgin Nov 21 '18 at 16:58
  • I updated your tag to reflect SQL Server since you indicated you were using SSRS and I also found other questions you had asked here that did specify SQL Server. Feel free to remove that tag if it doesn't apply to this question. – Scott Hodgin Nov 21 '18 at 21:47
  • Hi Scott, Sorry, I was oof, due to a different Timezone. Yes, I am working on SQL2016. I'll make sure to tag it correctly next time :) – Gerrit Nov 22 '18 at 7:39
0

You didn't specify your RDBMS or version, but I looked at another question you asked here and noticed you were using at least SQL Server 2016. The following should work on SQL 2012 and later. I added a few more rows to your sample data to show another month of scans (December) so I could make sure I only presented the subtraction of a dropped mailbox one time.

You'll note that ObjectSID 333 did not have scan data for November or December. We reflect the drop during the November scan.

Take a look at my solution and let me know if I have misunderstood your requirements or you see an error in the results.

--demo setup
drop table if exists dbo.mailbox
CREATE TABLE [dbo].[Mailbox](
[ObjectSID] [nvarchar](184) NULL, --ObjectSID of Mailbox
[Database] [nvarchar](64) NULL, --Exchange Database
[PrimarySMTP] [nvarchar](254) NULL, -- eMailAddress
[ItemCount] [int] NULL, -- SUM of eMails
[Itemsize_MB] [int] NULL, -- size of Mails
[Itemsize_del_MB] [int] NULL, -- size of deleted mails
[Arch_Database] [nvarchar](64) NULL, -- Name of the Archive Exchange Database
[Arch_ItemCount] [int] NULL, -- Sum of all archived mails
[Arch_Itemsize_MB] [int] NULL, -- Size of archived mails
[Arch_Itemsize_del_MB] [int] NULL,  --Size of deleted archived mails
[ScanTime] [date] NULL  --Date of the last SCAN
) ON [PRIMARY]
GO
Insert into Mailbox Values 
 (111,N'Database1',N'Sample.User1@domain.com',63913,16535,1,N'ARCH1',0,0,0,'2018-10-22')
,(111,N'Database1',N'Sample.User1@domain.com',63958,16540,2,N'ARCH1',0,0,0,'2018-10-24')
,(111,N'Database1',N'Sample.User1@domain.com',64533,16664,2,N'ARCH1',0,0,0,'2018-11-19')
,(111,N'Database1',N'Sample.User1@domain.com',64533,16664,2,N'ARCH1',0,0,0,'2018-12-19')
,(222,N'Database2',N'Sample.User2@domain.com',296,11,0,N'ARCH2',39139,10867,0,'2018-11-19')
,(222,N'Database2',N'Sample.User2@domain.com',296,11,0,N'ARCH2',39139,10867,0,'2018-10-24')
,(222,N'Database2',N'Sample.User2@domain.com',296,11,0,N'ARCH2',39139,10867,0,'2018-10-22')
,(222,N'Database2',N'Sample.User2@domain.com',296,11,0,N'ARCH2',39139,10867,0,'2018-12-19')
,(333,N'Database1',N'Sample.User3@domain.com',55292,12723,23,N'ARCH1',37302,7128,0,'2018-10-22')
,(333,N'Database1',N'Sample.User3@domain.com',55532,12855,25,N'ARCH1',37306,7128,0,'2018-10-24')

My solution

;WITH MailBoxAgg
AS (
    --Select all distinct ScanTimes from Mailbox and join
    --against Mailbox where the ScanTime <= one of the distict ScanTimes
    --If the Mailbox ScanTime <> Distinct ScanTime, that means the mailbox was dropped
    --so force 0 into [Mailbox in MB]
    SELECT [database]
        ,ObjectSID
        ,CASE 
            WHEN mb.ScanTime <> st.ScanTime
                THEN 0
            ELSE sum(Itemsize_MB + Itemsize_del_MB + Arch_Itemsize_MB + Arch_Itemsize_del_MB)
            END AS [Mailbox in MB]
        ,mb.ScanTime
        ,st.ScanTime AS stscan
    FROM (
        SELECT DISTINCT ScanTime
        FROM Mailbox
        ) st
    JOIN Mailbox mb
        ON mb.ScanTime = (
                SELECT max(ScanTime)
                FROM Mailbox
                WHERE [Database] = mb.[Database]
                    AND ObjectSID = mb.ObjectSID
                    AND ScanTime <= st.ScanTime
                )
    GROUP BY ObjectSID
        ,[Database]
        ,mb.ScanTime
        ,st.ScanTime
    )
SELECT [database]
    ,objectsid
    ,[Mailbox in MB]
    ,CASE
        --if the Mailbox ScanTime <> DistinctScanTime, get the
        --previous row value and negate it to represent negative 
        --growth for a dropped mailbox 
        WHEN scantime <> stscan
            THEN lag([mailbox in MB]) OVER (
                    PARTITION BY [database]
                    ,objectsid ORDER BY scantime
                    ) * - 1
        --if the previous row [mailbox in MB] is null,
        --this is the first row for the mailbox, so force current MB as growth
        WHEN lag([mailbox in MB]) OVER (
                PARTITION BY [database]
                ,objectsid ORDER BY scantime
                ) IS NULL
            THEN [mailbox in MB]
        --subtract the previous mailbox mb from the current to reflect growth
        ELSE [mailbox in MB] - lag([mailbox in MB]) OVER (
                PARTITION BY [database]
                ,objectsid ORDER BY scantime
                )
        END AS Growth
    ,ScanTime
    ,stscan
FROM MailBoxAgg

The results

| database  | objectsid | Mailbox in MB | Growth | ScanTime   | stscan     |
|-----------|-----------|---------------|--------|------------|------------|
| Database1 | 111       | 16536         | 16536  | 2018-10-22 | 2018-10-22 |
| Database1 | 111       | 16542         | 6      | 2018-10-24 | 2018-10-24 |
| Database1 | 111       | 16666         | 124    | 2018-11-19 | 2018-11-19 |
| Database1 | 111       | 16666         | 0      | 2018-12-19 | 2018-12-19 |
| Database1 | 333       | 19874         | 19874  | 2018-10-22 | 2018-10-22 |
| Database1 | 333       | 20008         | 134    | 2018-10-24 | 2018-10-24 |
| Database1 | 333       | 0             | -20008 | 2018-10-24 | 2018-11-19 |
| Database1 | 333       | 0             | 0      | 2018-10-24 | 2018-12-19 |
| Database2 | 222       | 10878         | 10878  | 2018-10-22 | 2018-10-22 |
| Database2 | 222       | 10878         | 0      | 2018-10-24 | 2018-10-24 |
| Database2 | 222       | 10878         | 0      | 2018-11-19 | 2018-11-19 |
| Database2 | 222       | 10878         | 0      | 2018-12-19 | 2018-12-19 |
  • Thank you soo much. I was trying to get this done for quite some time, but couldn't get it done. – Gerrit Nov 22 '18 at 7:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.