I want to create an indexed View.
I have this query:
SELECT srvmn.BBC_ID AS Inventory,
srvInv.ObjectID,
srv.State,
SUM( IIF(srvInv.Direction = srvInv.ReverseBroken, 1, 0)) AS Broken,
SUM(IIF(srvInv.Direction = 0, 1, -1)) AS Sum,
COUNT_BIG(*) AS CountLines
FROM table1 srvmn
JOIN table2 srv
ON srv.ServiceManID = srvmn.ID
JOIN table3 srvInv
ON srv.ID = srvInv.ServiceID
WHERE srv.State > 321
AND srv.State <> 123
GROUP BY srvInv.ObjectID,
srv.State,
srvInv.Direction,
srvInv.ReverseBroken,
srvmn.BBC_ID
ORDER BY srvInv.ObjectID
So, the Sum is not working properly also I fix it with this approach:
SELECT A.Inventory,
A.ObjectID,
A.State,
A.Broken,
SUM(A.Sum) AS Sum
FROM
(
SELECT srvmn.BBC_ID AS Inventory,
srvInv.ObjectID,
srv.State,
IIF(srvInv.Direction = srvInv.ReverseBroken, 1, 0) AS Broken,
SUM(IIF(srvInv.Direction = 0, 1, -1)) AS Sum,
COUNT_BIG(*) AS CountLines
FROM table1 srvmn
JOIN table2 srv
ON srv.ServiceManID = srvmn.ID
JOIN table3 srvInv
ON srv.ID = srvInv.ServiceID
WHERE srv.State > 321
AND srv.State <> 123
GROUP BY srvInv.ObjectID,
srv.State,
srvInv.Direction,
srvInv.ReverseBroken,
srvmn.BBC_ID
) AS A
GROUP BY A.Inventory,
A.ObjectID,
A.State,
A.Broken;
Result is OK, but Derived Tables are not allowed in Indexed Views.
I try to using "Over Partition" in Sum, but the result is not OK.
How I can fix this issue?
Correct Result :
Inventory ObjectID State Broken Sum
------------------------------------------------
NULL 2 500 0 -1
NULL 3 320 1 1
NULL 3 500 0 2
NULL 3 600 0 1
NULL 18 600 0 -1
NULL 20 600 0 -1
1162 20 600 0 1
NULL 24 600 0 1
NULL 26 310 0 -3
NULL 26 320 0 -1
NULL 26 600 0 7
442 26 500 0 8
442 26 500 1 1
442 26 600 0 -5
1162 26 600 0 0
NULL 27 500 0 4
NULL 27 600 0 2
1162 27 600 0 1
1162 28 600 0 3
Not Ok Result:
Inventory ObjectID State Broken Sum CountLines
-------------------------------------------------------------
NULL 2 500 0 -1 1
NULL 3 500 0 2 2
NULL 3 600 0 1 1
NULL 18 600 0 -1 1
1162 20 600 0 3 3
1162 20 600 0 -2 2
NULL 20 600 0 1 1
NULL 20 600 0 -2 2
NULL 24 600 0 -1 1
NULL 24 600 0 2 2
442 26 600 0 -7 7
NULL 26 600 0 -18 18
1162 26 600 0 1 1
NULL 26 600 0 25 25
442 26 500 1 1 1
442 26 600 0 2 2
1162 26 600 0 -1 1
442 26 500 0 8 8
NULL 27 600 0 -3 3
1162 27 600 0 1 1
Please try it :
SELECT
srvmn.BBC_ID AS Inventory,
srvInv.ObjectID,
srv.State,
SUM(IIF(srvInv.Direction = srvInv.ReverseBroken, 1, 0)) AS Broken,
SUM(IIF(srvInv.Direction = 0, 1, -1)) AS Sum
FROM table1 srvmn
JOIN table2 srv ON srv.ServiceManID = srvmn.ID
JOIN table3 srvInv ON srv.ID = srvInv.ServiceID
WHERE srv.State > 321 AND srv.State <> 123
GROUP BY
srvInv.ObjectID,
srv.State,
srvInv.ReverseBroken,
srvmn.BBC_ID