--SELECT * FROM tCommon.vRowCounts WHERE SchemaName = 'tTrans' ORDER BY ROWS DESC
--GO
--SELECT * FROM tCommon.vRowCounts WHERE DataSupplierId = 1843 AND tablename = 'property'
USE TEMPORARY
GO
DROP FUNCTION dbo.GetTransDeedCountInline
GO
CREATE FUNCTION dbo.GetTransDeedCountInline (@DataSupplierId SMALLINT,
@UnvPropEdition TINYINT,
@UnvPropId INT)
RETURNS TABLE
RETURN
SELECT COUNT(*) AS Counter
FROM Diablo.tTrans.TransDeed t (NOLOCK)
WHERE T.UnvPropEdition IS NOT NULL
AND t.DataSupplierId = @DataSupplierId
AND T.UnvPropEdition = @UnvPropEdition
AND t.UnvPropId = @UnvPropId
GO
DROP FUNCTION dbo.GetTransDeedCount
GO
CREATE FUNCTION dbo.GetTransDeedCount (@DataSupplierId SMALLINT,
@UnvPropEdition TINYINT,
@UnvPropId INT)
RETURNS INT
BEGIN
RETURN
(SELECT COUNT(*)
FROM Diablo.tTrans.TransDeed t (NOLOCK)
WHERE T.UnvPropEdition IS NOT NULL
AND t.DataSupplierId = @DataSupplierId
AND T.UnvPropEdition = @UnvPropEdition
AND t.UnvPropId = @UnvPropId)
END
GO
IF OBJECT_ID('TEMPDB..#TEMP1') IS NOT NULL
DROP TABLE #TEMP1
GO
IF OBJECT_ID('TEMPDB..#TEMP2') IS NOT NULL
DROP TABLE #TEMP2
GO
IF OBJECT_ID('TEMPDB..#TEMP3') IS NOT NULL
DROP TABLE #TEMP3
GO
SELECT PropId,
(SELECT COUNT(*)
FROM Diablo.tTrans.TransDeed t (NOLOCK)
WHERE T.UnvPropEdition IS NOT NULL
AND t.DataSupplierId = p.DataSupplierId
AND T.UnvPropEdition = p.UnvPropEdition
AND t.UnvPropId = p.UnvPropId) AS COUNTER
INTO #TEMP1
FROM Diablo.tTax.PROPERTY p (NOLOCK)
WHERE DataSupplierId = 1843
GO
SELECT PropId,
b.COUNTER
INTO #TEMP2
FROM Diablo.tTax.PROPERTY p (NOLOCK)
CROSS APPLY dbo.GetTransDeedCountInline(p.DataSupplierId, p.UnvPropEdition, p.UnvPropId) b
WHERE DataSupplierId = 1843
GO
SELECT PropId,
dbo.GetTransDeedCount(p.DataSupplierId, p.UnvPropEdition, p.UnvPropId) AS COUNTER
INTO #TEMP3
FROM Diablo.tTax.PROPERTY p (NOLOCK)
WHERE DataSupplierId = 1843
GO