Use this to find what accounts were edited by whom for a specific time period. Dates AND User IDs will need to be changed to the appropriate range for this to work.
use manitou;
go
SELECT DISTINCT CU.CONTID
, CL1.USRID
, CL1.EVDATE
FROM CLOG201901 CL1 -- set this to the appropriate month!!
INNER JOIN CUSTOMER CU ON CU.SERIALNO = CL1.SERIALNO
WHERE CL1.EVDATE = (
/*finds the lowest evdate for each serialno where an edit occurred and the edit comment is like 'new%'
and correlates that to the serialno in the outer query*/
SELECT MIN(CL2.EVDATE)
FROM CLOG201801 CL2 --ON CL2.SERIALNO = CU.SERIALNO -- set this to the appropriate month!
INNER JOIN CUSTOMER CU ON CU.SERIALNO = CL2.SERIALNO
WHERE CL2.EVTYPE = 18 --21 = Customer Active --18 = Record Stored
AND CL2.EVSTRING LIKE 'NEW%'
AND CL2.SERIALNO = CL1.SERIALNO
AND CL2.USRID IN('DCLARK', 'RSTOVERINK','JWILSON','SWRIGHTAM', 'BBAX', 'KBEAMON', 'MPORTER', 'TRAVENS') -- ENTER USER IDS HERE
)
AND CL1.EVSTRING LIKE 'NEW%'
AND CL1.EVTYPE = 18
AND CL1.USRID IN('DCLARK', 'RSTOVERINK','JWILSON','SWRIGHTAM', 'BBAX', 'KBEAMON', 'MPORTER', 'TRAVENS') -- ENTER USER IDS HERE
ORDER BY CU.CONTID
/*
Dynamically build temp tables based on the date range input by the report user
From Chris Losciuto Interface Security Systems, LLC
*/
DECLARE @startdate DateTime;
DECLARE @enddate DateTime;
DECLARE @fomStart DateTime;
DECLARE @fomEnd DateTime;
DECLARE @clogSQL nvarchar(max);
/*
Set the Start and End Dates below:
*/
SET @startdate = '20190101'
SET @enddate = '20191231'
SET @clogSQL = '';
--fomend takes the end date entered and adds a day, so 5/31/14 becomes 6/1/14 to get alarms that occur on 6/1 GMT but was 5/31/14 23:59 Central Station Time
SET @fomEnd = DATEADD(month, DATEDIFF(month, 0, DATEADD(d,1,@enddate)), 0);
--Starts with 0 (standard SQL Server start date) gets number of months between that and the @startdate. Then adds those number of months to date 0 to get the month.
SET @fomStart = DATEADD(month, DATEDIFF(month, 0, @startdate), 0);
--takes the DateTo and loops back to one month before the DateFrom to get alarms that started but not ended before the start date (DateFrom).
WHILE (@fomEnd >= DATEADD(month,-1,@fomStart))
BEGIN
IF @clogSQL <> ''
BEGIN
SET @clogSQL = @clogSQL + '
UNION ALL
';
END
--Have set @clogsql to blank so this is the first thing executed in the WHILE Loop. It starts with the DateTo month and builds and INSERT Statement
--This runs until it gets to the DateFrom - 1 month
ELSE
SET @clogSQL = 'INSERT INTO #clog
';
SET @clogSQL = @clogSQL + 'SELECT
cl.serialno,
evdate,
dateadd(mi,offset,evdate),
stcode,
fachrs,
evtype,
area
FROM clog' + CONVERT(nvarchar(6),@fomEnd,112) + ' cl'
SET @fomEnd = DATEADD(month,-1,@fomEnd)
END
PRINT (@clogSQL)
--EXEC (@clogSQL)
