cancel
Showing results for 
Search instead for 
Did you mean: 

Opscenter Reporting

Sid1987
Level 6
Certified

Hi Techiz,

 In the query below when I am using DATEDIFF, its not giving me correct date range output.

 

SELECT
nb_JobFilesArchive.jobId as 'Job id',
domain_JobArchive.clientName as 'Client',
domain_JobArchive.policyName as 'Policy Name',
domain_JobArchive.scheduleName as 'Schedule Name',
lookup_JobStatusCode.name as 'Exit Status',
(domain_JobArchive.bytesWritten / 1024) as 'Size in KB',
nb_JobFilesArchive.fileinfo as 'File List',
UTCBigIntToNomTime(domain_JobArchive.startTime) as 'Start Time',
UTCBigIntToNomTime(domain_JobArchive.endTime) as 'End Time'
FROM (domain_JobArchive inner join nb_JobFilesArchive on nb_JobFilesArchive.jobId=domain_JobArchive.id) inner join lookup_JobStatusCode on domain_JobArchive.StatusCode=lookup_JobStatusCode.id
AND DATEDIFF(hour,UTCBigIntToNomTime(domain_JobArchive.startTime),GETDATE())<=24;

 

DATEDIFF(hour,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 24

even if i use

DATEDIFF(day,UTCBigIntToNomTime(domain_JobArchive.endTime), GETDATE()) <= 1

 

Number of rows are pretty less, not showing all status for 24 hours, How do I achieve it? More over the report it generates, shows rows in thousands and each page i check has the same set of entries and when I email it i get only 250 rows.

 

Thanks

Sid

1 ACCEPTED SOLUTION

Accepted Solutions

DG-2005
Level 5

made a sligth modification to your query (added exit code)

 

SELECT
nb_JobFilesArchive.jobId as 'Job id',
lookup_JobStatuscode.id as 'Status Code',
domain_JobArchive.clientName as 'Client',
domain_JobArchive.policyName as 'Policy Name',
domain_JobArchive.scheduleName as 'Schedule Name',
lookup_JobStatusCode.name as 'Exit Status',
(domain_JobArchive.bytesWritten / 1024) as 'Size in KB',
nb_JobFilesArchive.fileinfo as 'File List',
UTCBigIntToNomTime(domain_JobArchive.startTime) as 'Start Time',
UTCBigIntToNomTime(domain_JobArchive.endTime) as 'End Time'
FROM (domain_JobArchive inner join nb_JobFilesArchive on nb_JobFilesArchive.jobId=domain_JobArchive.id) inner join lookup_JobStatusCode on domain_JobArchive.StatusCode=lookup_JobStatusCode.id
AND DATEDIFF(hour,UTCBigIntToNomTime(domain_JobArchive.endTime),GETDATE())<=24
ORDER BY lookup_JobStatuscode.id DESC

 

as for the job and DATEDIFF i have saved a few queries which might be worth looking into:

FROM domain_Job

DATEDIFF(hour, UtcBigIntToNomTime(endTime), GETDATE()) <= 72

DATEDIFF(mi, utcbiginttonomtime(StartTime), utcbiginttonomtime(EndTime)) AS "Duration (Min)",

 

 

View solution in original post

2 REPLIES 2

DG-2005
Level 5

made a sligth modification to your query (added exit code)

 

SELECT
nb_JobFilesArchive.jobId as 'Job id',
lookup_JobStatuscode.id as 'Status Code',
domain_JobArchive.clientName as 'Client',
domain_JobArchive.policyName as 'Policy Name',
domain_JobArchive.scheduleName as 'Schedule Name',
lookup_JobStatusCode.name as 'Exit Status',
(domain_JobArchive.bytesWritten / 1024) as 'Size in KB',
nb_JobFilesArchive.fileinfo as 'File List',
UTCBigIntToNomTime(domain_JobArchive.startTime) as 'Start Time',
UTCBigIntToNomTime(domain_JobArchive.endTime) as 'End Time'
FROM (domain_JobArchive inner join nb_JobFilesArchive on nb_JobFilesArchive.jobId=domain_JobArchive.id) inner join lookup_JobStatusCode on domain_JobArchive.StatusCode=lookup_JobStatusCode.id
AND DATEDIFF(hour,UTCBigIntToNomTime(domain_JobArchive.endTime),GETDATE())<=24
ORDER BY lookup_JobStatuscode.id DESC

 

as for the job and DATEDIFF i have saved a few queries which might be worth looking into:

FROM domain_Job

DATEDIFF(hour, UtcBigIntToNomTime(endTime), GETDATE()) <= 72

DATEDIFF(mi, utcbiginttonomtime(StartTime), utcbiginttonomtime(EndTime)) AS "Duration (Min)",

 

 

Sid1987
Level 6
Certified

I used start instead of end time and it works, I donno how I missed to include the column on which i am going apply the outside () inner join. Thanks...