β07-01-2014 03:20 AM
Hi Techiz,
In the query below when I am using DATEDIFF, its not giving me correct date range output.
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
Solved! Go to Solution.
β07-01-2014 05:13 AM
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)",
β07-01-2014 05:13 AM
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)",
β07-02-2014 08:55 AM
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...