SET NOCOUNT ON

DECLARE @vStartDate DATETIME = '2017-04-18 00:00:00', 
		@vEndDate DATETIME =  '2017-04-18 23:59:59', 
		@weekSec INTEGER = 604800,
		@vCompany INTEGER = 7,
		@vMaxAllowed INTEGER = 30, 
		@vCarcount INTEGER,
		@vOverageCounter INTEGER,
		@vY2K DATETIME = '2000-01-01 00:00:00',
		@vStartSeconds INTEGER,
		@vEndSeconds INTEGER,
		@vStartCount INTEGER,
		@vRowCounter INTEGER = 0;

-- DATETIME conversion--DATETIME VALUE in DB is TIME since 1/1/2000 @ 00:00
SET @vStartSeconds = DATEDIFF(second, @vY2K, @vStartDate);
PRINT 'Report start date/time: ' + CAST(@vStartDate AS VARCHAR)
SET @vEndSeconds = DATEDIFF(second, @vY2K, @vEndDate);
PRINT 'Report end date/time: ' + CAST (@vEndDate AS VARCHAR)
------------------------------------------------------------------------------

SET @vStartCount = 0;
SET @vCarCount = 0;
SET @vOverageCounter = 0;

--GET START COUNT
WITH CTE AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY M_Trans.Badge ORDER BY M_Trans.[DateTime] DESC) AS 'Row #',
	   M_Trans.Badge,
	   [DateTime] AS 'Time Used',
	   RecType
FROM M_Trans
LEFT JOIN M_Subscribers ON M_Trans.Badge = M_Subscribers.Badge
GROUP BY M_Trans.Badge, ISLastVer, Deleted, M_Trans.CompanyID, [DateTime], RecType
HAVING ISLastVer = 1 AND Deleted = 0 AND M_Trans.CompanyID = @vCompany AND [DateTime] BETWEEN (@vStartSeconds-@weekSec) AND (@vStartSeconds)
)
SELECT @vStartCount = COUNT(Badge) FROM CTE WHERE [Row #] = 1 AND RecType = 51;

--SELECT CTE.Badge AS 'Card #', COUNT(CTE.Badge) FROM CTE WHERE [Row #] = 1 ORDER BY [Time Used] DESC

---@vCarCount = @vStartCount
---Iterate through each transaction from start datetime to end datetime and if 51, increment carcount, if 52, decrement carcount, if 0 do nothing
---If @vCarCount >= to @vMaxAllowed and if RecType = 51 then increment @vOverageCounter else do nothing (or add zero to @vOverageCounter)
---Return @vOverageCounter
PRINT 'Starting car count: ' + CAST(@vStartCount AS VARCHAR);

DECLARE @cBadge INTEGER, @cRecType INTEGER, @cDateTime INTEGER;
DECLARE parkEVENT_CURSOR CURSOR FOR 
	SELECT Badge, RecType, [DateTime] FROM M_Trans WHERE CompanyID = @vCompany AND [DateTime] BETWEEN @vStartSeconds AND @vEndSeconds ORDER BY [DateTime] ASC;

SET @vCarCount = @vStartCount;

OPEN parkEVENT_CURSOR

FETCH NEXT FROM parkEVENT_CURSOR INTO
	@cBadge, @cRecType, @cDateTime

WHILE @@FETCH_STATUS = 0
BEGIN
	IF (@cRecType = 51)
		BEGIN
		SET @vCarCount = @vCarCount + 1
		IF (@vCarcount >= @vMaxAllowed)
		BEGIN
			SET @vOverageCounter = @vOverageCounter + 1;
			PRINT 'Card used during overage: Card # ' + CAST(@cBadge AS VARCHAR) + ' used on ' + CAST((DATEADD(s, @cDateTime, @vY2K)) AS VARCHAR)
		END
		ELSE
			SET @vOverageCounter = @vOverageCounter + 0;
		END
	ELSE
		BEGIN
		IF (@cRecType = 52)
			SET @vCarCount = @vCarCount - 1;
		ELSE
			SET @vCarCount = @vCarCount + 0;
		END;
SET @vRowCounter = @vRowCounter + 1;
FETCH NEXT FROM parkEVENT_CURSOR INTO
	@cBadge, @cRecType, @cDateTime;
END;
CLOSE parkEVENT_CURSOR;
DEALLOCATE parkEVENT_CURSOR;
PRINT 'Ending car count: ' + CAST(@vCarCount AS VARCHAR)
PRINT 'Overage count: ' + CAST(@vOverageCounter AS VARCHAR)
PRINT 'Total number of access events: ' + CAST(@vRowCounter AS VARCHAR)