Sql-server – subtract missing Value from Last Value

cterowsql serversql-server-2016

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.

Best Answer

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 |
Related Question