DECLARE @db1 varChar(10), @db2 varChar(10), @catF Char(2), @catT Char(2), @sort varChar(4)
DECLARE @file1 varChar(10), @file2 varChar(10), @file3 varChar(10), @file4 varChar(10)
DECLARE @field1 varChar(10), @field2 varChar(10), @field3 varChar(10), @field4 varChar(10)
DECLARE @flag1 varChar(2), @flag2 varChar(2), @flag3 varChar(2), @flag4 varChar(2)
DECLARE @offset tinyInt, @timeTaken Int, @date DateTime, @fromDate DateTime
DECLARE @hour Char(2), @minute Char(2), @Seq Char(5)
-- USER defined variables
SET @offset = 0
SET @Seq = '00000'
SET @catF = 'AA'
SET @catT = 'ZZ'
SET @timeTaken = '99999999'
SET @sort = 'DESC'
-- SYSTEM defined variables
SET @db1 = 'TMSBIXNBL'
SET @file1 = 'TBI_PROCQ'
SET @field1 = 'PQSTATUS'
SET @field2 = 'PQCRTDATE'
SET @field3 = 'PQSTRDATE'
SET @field4 = 'PQCHGDATE'
SET @flag1 = 'C'
SET @flag2 = 'W'
SET @date = getdate() - @offset
SET @hour = '00'
SET @minute = '00'
SET @fromDate = substring(convert(char(10),@date, 101),7,4)
+'-'+substring(convert(char(10),@date, 101),1,2)
+'-'+substring(convert(char(10),@date, 101),4,2)
+' '+ @hour + ':' + @minute + ':00.000'
-- Process Query
EXEC ('SELECT
[PQGENSEQ] ''Seq''
, [PQSTRCODE] ''Cat''
, [' + @field1 + '] ''Sts''
, [PQFILE]
, [PQPARM1] ''Pd Fm''
, [PQPARM2] ''Pd To''
, ''|'' ''-''
, [PQPaRM5] ''Yr''
, [PQPARM8] ''Bld''
, ''|'' ''-''
, CASE
WHEN [' + @field1 + '] IN (''' + @flag1 + ''') THEN
cast(datediff(ss, [' + @field3 + '], [' + @field4 + ']) as varChar(10))
ELSE '' '' END ''SS2''
, ''|'' ''-''
, [' + @field2 + ']
, [' + @field3 + ']
, ''|'' ''-''
,
CASE
WHEN [' + @field1 + '] IN (''' + @flag2 + ''') THEN
	/* HOURS */
	CASE
	WHEN cast(datediff(ss, [' + @field3 + '], getdate()) / 3600 as int) < 10
	THEN ''0'' + cast(cast(datediff(ss, [' + @field3 + '], getdate()) / 3600 as int) as char(1))
	ELSE cast(cast(datediff(ss, [' + @field3 + '], getdate()) / 3600 as int) as char(2))
	END
	+ '':'' +
	/* MINUTES */
	CASE
	WHEN (datediff(ss, [' + @field3 + '], getdate()) - (cast((datediff(ss, [' + @field3 + '], getdate()) / 3600) as int) * 3600)) / 60 < 10
	THEN ''0'' + cast((datediff(ss, [' + @field3 + '], getdate()) - (cast((datediff(ss, [' + @field3 + '], getdate()) / 3600) as int) * 3600)) / 60 as char(1))
	ELSE cast((datediff(ss, [' + @field3 + '], getdate()) - (cast((datediff(ss, [' + @field3 + '], getdate()) / 3600) as int) * 3600)) / 60 as char(2))
	END
	+ '':'' +
	/* SECONDS */
	CASE
	WHEN (datediff(ss, [' + @field3 + '], getdate()) % 3600) % 60 < 10
	THEN ''0'' + cast((datediff(ss, [' + @field3 + '], getdate()) % 3600) % 60 as char(1))
	ELSE cast((datediff(ss, [' + @field3 + '], getdate()) % 3600) % 60 as char(2))
	END
WHEN [' + @field1 + '] IN (''' + @flag1 + ''') THEN
	/* HOURS */
	CASE
	WHEN cast(datediff(ss, [' + @field2 + '], [' + @field3 + ']) / 3600 as int) < 10
	THEN ''0'' + cast(cast(datediff(ss, [' + @field2 + '], [' + @field3 + ']) / 3600 as int) as char(1))
	ELSE cast(cast(datediff(ss, [' + @field2 + '], [' + @field3 + ']) / 3600 as int) as char(2))
	END
	+ '':'' +
	/* MINUTES */
	CASE
	WHEN (datediff(ss, [' + @field2 + '], [' + @field3 + ']) - (cast((datediff(ss, [' + @field2 + '], [' + @field3 + ']) / 3600) as int) * 3600)) / 60 < 10
	THEN ''0'' + cast((datediff(ss, [' + @field2 + '], [' + @field3 + ']) - (cast((datediff(ss, [' + @field2 + '], [' + @field3 + ']) / 3600) as int) * 3600)) / 60 as char(1))
	ELSE cast((datediff(ss, [' + @field2 + '], [' + @field3 + ']) - (cast((datediff(ss, [' + @field2 + '], [' + @field3 + ']) / 3600) as int) * 3600)) / 60 as char(2))
	END
	+ '':'' +
	/* SECONDS */
	CASE
	WHEN (datediff(ss, [' + @field2 + '], [' + @field3 + ']) % 3600) % 60 < 10
	THEN ''0'' + cast((datediff(ss, [' + @field2 + '], [' + @field3 + ']) % 3600) % 60 as char(1))
	ELSE cast((datediff(ss, [' + @field2 + '], [' + @field3 + ']) % 3600) % 60 as char(2))
	END
ELSE '' ''
END ''HH:MM:SS''
, ''|'' ''-''
, [' + @field4 + ']
, ''|'' ''-''
,
CASE
WHEN [' + @field1 + '] NOT IN (''' + @flag1 + ''') THEN '' ''
ELSE
	/* HOURS */
	CASE
	WHEN cast(datediff(ss, [' + @field3 + '], [' + @field4 + ']) / 3600 as int) < 10
	THEN ''0'' + cast(cast(datediff(ss, [' + @field3 + '], [' + @field4 + ']) / 3600 as int) as char(1))
	ELSE cast(cast(datediff(ss, [' + @field3 + '], [' + @field4 + ']) / 3600 as int) as char(2))
	END
	+ '':'' +
	/* MINUTES */
	CASE	WHEN (datediff(ss, [' + @field3 + '], [' + @field4 + ']) - (cast((datediff(ss, [' + @field3 + '], [' + @field4 + ']) / 3600) as int) * 3600)) / 60 < 10
	THEN ''0'' + cast((datediff(ss, [' + @field3 + '], [' + @field4 + ']) - (cast((datediff(ss, [' + @field3 + '], [' + @field4 + ']) / 3600) as int) * 3600)) / 60 as char(1))
	ELSE cast((datediff(ss, [' + @field3 + '], [' + @field4 + ']) - (cast((datediff(ss, [' + @field3 + '], [' + @field4 + ']) / 3600) as int) * 3600)) / 60 as char(2))
	END
	+ '':'' +
	/* SECONDS */
	CASE
	WHEN (datediff(ss, [' + @field3 + '], [' + @field4 + ']) % 3600) % 60 < 10
	THEN ''0'' + cast((datediff(ss, [' + @field3 + '], [' + @field4 + ']) % 3600) % 60 as char(1))
	ELSE cast((datediff(ss, [' + @field3 + '], [' + @field4 + ']) % 3600) % 60 as char(2))
	END
END ''HH:MM:SS''

FROM [' + @db1 + ']..[' + @file1 + ']

WHERE [' + @field4 + '] > = ''' + @fromDate + '''
and [PQGENSEQ] >= ''' + @Seq + '''
and datediff(ss, [' + @field3 + '], [' + @field4 + ']) <= ''' + @timeTaken + '''
and ([PQSTRCODE] >= ''' + @catF + '''
and [PQSTRCODE] <= ''' + @catT + ''')
ORDER BY [PQGENSEQ] ' + @sort + ' ' )

/*
UPDATE [TMSBIXNBL]..[TBI_PROCQ]
SET [PQSTATUS] = 'O'
WHERE [PQGENSEQ] IN ('05100')
*/

--TRUNCATE TABLE [TMSBIXNBL]..[TBI_IMPORT]