how to customize any other way to query

Hi team,

I'm trying to optimize this query size and best method, please let me share if have any best ideas on this optimize.

select '0sce to 2sec' as Bucket_Name,count(*) from tbllog 
where DATE_PART('second', endtimestamp::timestamp - starttimestamp::timestamp) <=2 and Process= @Process and  starttimestamp >= @StartTime and starttimestamp < @EndTime Union select '2sec to 5sec' as Bucket_Name,count(*) 
from tbllog  where Process= @Process and starttimestamp >= @StartTime 
and starttimestamp < @EndTime and DATE_PART('second', endtimestamp::timestamp - starttimestamp::timestamp)<=5 and DATE_PART('second', endtimestamp::timestamp - starttimestamp::timestamp)> 2 Union select '5sec to 10sec' as Bucket_Name,count(*) 
from tbllog   
where Process= @Process and starttimestamp >= @StartTime and starttimestamp < @EndTime and DATE_PART('second', endtimestamp::timestamp - starttimestamp::timestamp)<=10 
and DATE_PART('second', endtimestamp::timestamp - starttimestamp::timestamp)> 5 Union select 'Greater than 10sec' as Bucket_Name, count(*) from tbllog  
where Process= @Process and starttimestamp >= @StartTime and starttimestamp < @EndTime and DATE_PART('second', endtimestamp::timestamp - starttimestamp::timestamp)>10 ) 
ORDER BY Bucket_Name

Thanks

Hello,

Depending on which target database you are using, following query may or may not work for you.

Furthermore, I have changed presentation which again may or may not be suitable for your requirement.

However, the aim is to demonstrate an alternate way to achieve the similar result.

select [Process]
,sum(case when DATE_PART('second', endtimestamp::timestamp - starttimestamp::timestamp) between 0 and 2 then 1 else 0 end) '0sce to 2sec'
,sum(case when DATE_PART('second', endtimestamp::timestamp - starttimestamp::timestamp) between 3 and 5 then 1 else 0 end) '3sec to 5sec'
,sum(case when DATE_PART('second', endtimestamp::timestamp - starttimestamp::timestamp) between 6 and 10 then 1 else 0 end) '6sec to 10sec'
,sum(case when DATE_PART('second', endtimestamp::timestamp - starttimestamp::timestamp) > 10 then 1 else 0 end) 'Greater than 10sec'
from tbllog
where 1 = 1
and [Process] = isnull(@Process, [Process])
and starttimestamp between @StartTime and @EndTime
group by [Process]
order by [Process]

Hope it helps!

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke4y ago
Accepted answer
Hello,

Depending on which target database you are using, following query may or may not work for you.

Furthermore, I have changed presentation which again may or may not be suitable for your requirement.

However, the aim is to demonstrate an alternate way to achieve the similar result.
select [Process]
  ,sum(case when DATE_PART('second', endtimestamp::timestamp - starttimestamp::timestamp) between 0 and  2 then 1 else 0 end) '0sce to 2sec'
  ,sum(case when DATE_PART('second', endtimestamp::timestamp - starttimestamp::timestamp) between 3 and  5 then 1 else 0 end) '3sec to 5sec'
  ,sum(case when DATE_PART('second', endtimestamp::timestamp - starttimestamp::timestamp) between 6 and 10 then 1 else 0 end) '6sec to 10sec'
  ,sum(case when DATE_PART('second', endtimestamp::timestamp - starttimestamp::timestamp) > 10 then 1 else 0 end) 'Greater than 10sec'
from tbllog
where 1 = 1
  and [Process] = isnull(@Process, [Process])
  and starttimestamp between @StartTime and @EndTime
group by [Process]
order by [Process]
Hope it helps!