Need help on query performance

Question

Post reply

Need help on query performance

Viewing 15 posts - 61 through 75 (of 76 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110192 More actions · Answer 1

It goes like this:

Declare @StartDate datetime, @EndDate datetime

Set @StartDate = --beginning of the month

Dateadd(mm, datediff(mm,0, @Date), 0)

SET @EndDate = -- end of the month

Dateadd(mm, 1, @StartDate)

SELECT ....

WHERE @StartDate <= start_time and start_time < @EndDate

_____________
Code for TallyGenerator

jc85 Right there with Babe Points: 760 More actions · Answer 2

jc85

Right there with Babe

Points: 760

May 31, 2016 at 11:14 pm

#1882330

Thanks ! I wonder why I never thought of that !

jc85 Right there with Babe Points: 760 More actions · Answer 3

Date (start_time)--report_item----EN (lang)---BM (lang)

00:00 - 00:30-----Noisy----------------------3------------- 0

00:00 - 00:30-----No Dial Tone-------------6--------------2

00:30 - 01:00-----Noisy----------------------1--------------2

00:30 - 01:00-----No Dial Tone-------------0------------- 7

-----------------------------------------------------------------

My query is able to output the result as above but it is extremely long and involve another separate table in order to sort the Date format as 00:00 - 00:30, 00:30 - 01:00 till 23:30 - 00:00.

I was trying to use Sergiy's query to do it but unable to get the date in this format without using another table. Can anyone help? Full result is attached in excel sheet.

Thanks.

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 4

I would strongly advise using the database layer to return the data, and the presentation layer to make it look pretty to the user. If you insist on formatting in the query, though, surely it's just a question of casting your the dates as time, then casting as char, then concatenating, then chopping off the seconds portion?

John

jc85 Right there with Babe Points: 760 More actions · Answer 5

If I use presentation layer (crystal report in my case) to organize it, would it take longer time?

After chopping off the second portion, how could I compare the time? Does it look like this?

WHERE @start_time >= '00:00' and @start_time <= '00:30'

@start_time >= '00:30' and @start_time <= '01:00'

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 6

You asked about formatting the output. What you do in your WHERE clause doesn't make any difference to that. But the WHERE clause you posted doesn't contain any column names, so (assuming you don't get any errors) it will either return all rows or no rows. Please will you post the full query?

John

Sergiy SSC Guru Points: 110192 More actions · Answer 7

jc85 (6/1/2016)
Date (start_time)--report_item----EN (lang)---BM (lang)
00:00 - 00:30-----Noisy----------------------3------------- 0
00:00 - 00:30-----No Dial Tone-------------6--------------2
00:30 - 01:00-----Noisy----------------------1--------------2
00:30 - 01:00-----No Dial Tone-------------0------------- 7
-----------------------------------------------------------------
My query is able to output the result as above but it is extremely long and involve another separate table in order to sort the Date format as 00:00 - 00:30, 00:30 - 01:00 till 23:30 - 00:00.
I was trying to use Sergiy's query to do it but unable to get the date in this format without using another table. Can anyone help? Full result is attached in excel sheet.
Thanks.

Now you're gonna hit your head again asking "why did not i think of it?".

It's so easy. 🙂

You group you'd events by day:

dateadd(dd,0, datediff(dd,0,b.start_time)) as [date]

But what you need is to group by 30 min time slots.

So, if that's what you need - just do it!

dateadd(n,0, datediff(n,0,b.start_time)/30*30) as [time slot]

Then, when presenting the result you may split [time slot] into date and time parts - it's up to consumers of the report how do they wanna see it.

No need to touch WHERE clause at all.

_____________
Code for TallyGenerator

jc85 Right there with Babe Points: 760 More actions · Answer 8

John Mitchell-245523 (6/1/2016)
You asked about formatting the output. What you do in your WHERE clause doesn't make any difference to that. But the WHERE clause you posted doesn't contain any column names, so (assuming you don't get any errors) it will either return all rows or no rows. Please will you post the full query?
John

rpt_GetMenuSelectionByInterval will be called, passing call flow name and date selected by user.

Thanks in advance.

ALTER PROCEDURE [dbo].[rpt_GetMenuSelectionByInterval]

@call_flow_name nvarchar(50),

@SelectedDate datetime

AS

--Create a temporary Table

CREATE TABLE #tempTable(

interval_id int,

call_flow_name nvarchar(50),

source nvarchar(50),

lang nvarchar(5),

hit int,

report_item nvarchar(100)

)

IF @call_flow_name = 'Technical'

INSERT INTO #tempTable exec [rpt_GetMenuSelectionTechnicalIntervalCountTable] @call_flow_name, @SelectedDate

SELECT tb.interval as date, (tb.source + '-' + tb.report_item) as report_item, sum(isnull(tb1.EN,0))as 'EN',sum(isnull(tb1.BM,0))as 'BM'

FROM (

SELECT b.id, b.interval, a.report_item, a.call_flow_name, a.source

FROM ann_reportitem a, ann_interval b

WHERE a.report_id =8 AND a.call_flow_name =@call_flow_name

) as tb

LEFT JOIN

(

SELECT interval_id, call_flow_name, source, hit as EN, 0 as BM, 0 as MD, report_item FROM #tempTable WHERE lang ='EN'

UNION ALL

SELECT interval_id, call_flow_name, source, 0 as EN, hit as BM, 0 as MD, report_item FROM #tempTable WHERE lang ='BM'

UNION ALL

SELECT interval_id, call_flow_name, source, 0 as EN, 0 as BM, hit as MD, report_item FROM #tempTable WHERE lang ='MD'

) as tb1 ON tb.id=tb1.interval_id AND tb.call_flow_name=tb1.call_flow_name

AND tb.source = tb1.source and tb.report_item =tb1.report_item

GROUP BY tb.interval, tb.report_item, tb.source

ORDER BY tb.interval

--Drop Temp Table

DROP TABLE #tempTable

ALTER PROCEDURE [dbo].[rpt_GetMenuSelectionTechnicalIntervalCountTable]

@call_flow_name nvarchar(50),

@SelectedDate datetime

AS

SELECT

(CASE when b.start_time >= @SelectedDate + ' 00:00:00' and b.start_time < @SelectedDate + ' 00:30:00' then '1'

when b.start_time >= @SelectedDate + ' 00:30:00' and b.start_time < @SelectedDate + ' 01:00:00' then '2'

when b.start_time >= @SelectedDate + ' 01:00:00' and b.start_time < @SelectedDate + ' 01:30:00' then '3'

when b.start_time >= @SelectedDate + ' 01:30:00' and b.start_time < @SelectedDate + ' 02:00:00' then '4'

when b.start_time >= @SelectedDate + ' 02:00:00' and b.start_time < @SelectedDate + ' 02:30:00' then '5'

when b.start_time >= @SelectedDate + ' 02:30:00' and b.start_time < @SelectedDate + ' 03:00:00' then '6'

when b.start_time >= @SelectedDate + ' 03:00:00' and b.start_time < @SelectedDate + ' 03:30:00' then '7'

when b.start_time >= @SelectedDate + ' 03:30:00' and b.start_time < @SelectedDate + ' 04:00:00' then '8'

when b.start_time >= @SelectedDate + ' 04:00:00' and b.start_time < @SelectedDate + ' 04:30:00' then '9'

when b.start_time >= @SelectedDate + ' 04:30:00' and b.start_time < @SelectedDate + ' 05:00:00' then '10'

when b.start_time >= @SelectedDate + ' 05:00:00' and b.start_time < @SelectedDate + ' 05:30:00' then '11'

when b.start_time >= @SelectedDate + ' 05:30:00' and b.start_time < @SelectedDate + ' 06:00:00' then '12'

when b.start_time >= @SelectedDate + ' 06:00:00' and b.start_time < @SelectedDate + ' 06:30:00' then '13'

when b.start_time >= @SelectedDate + ' 06:30:00' and b.start_time < @SelectedDate + ' 07:00:00' then '14'

when b.start_time >= @SelectedDate + ' 07:00:00' and b.start_time < @SelectedDate + ' 07:30:00' then '15'

when b.start_time >= @SelectedDate + ' 07:30:00' and b.start_time < @SelectedDate + ' 08:00:00' then '16'

when b.start_time >= @SelectedDate + ' 08:00:00' and b.start_time < @SelectedDate + ' 08:30:00' then '17'

when b.start_time >= @SelectedDate + ' 08:30:00' and b.start_time < @SelectedDate + ' 09:00:00' then '18'

when b.start_time >= @SelectedDate + ' 09:00:00' and b.start_time < @SelectedDate + ' 09:30:00' then '19'

when b.start_time >= @SelectedDate + ' 09:30:00' and b.start_time < @SelectedDate + ' 10:00:00' then '20'

when b.start_time >= @SelectedDate + ' 10:00:00' and b.start_time < @SelectedDate + ' 10:30:00' then '21'

when b.start_time >= @SelectedDate + ' 10:30:00' and b.start_time < @SelectedDate + ' 11:00:00' then '22'

when b.start_time >= @SelectedDate + ' 11:00:00' and b.start_time < @SelectedDate + ' 11:30:00' then '23'

when b.start_time >= @SelectedDate + ' 11:30:00' and b.start_time < @SelectedDate + ' 12:00:00' then '24'

when b.start_time >= @SelectedDate + ' 12:00:00' and b.start_time < @SelectedDate + ' 12:30:00' then '25'

when b.start_time >= @SelectedDate + ' 12:30:00' and b.start_time < @SelectedDate + ' 13:00:00' then '26'

when b.start_time >= @SelectedDate + ' 13:00:00' and b.start_time < @SelectedDate + ' 13:30:00' then '27'

when b.start_time >= @SelectedDate + ' 13:30:00' and b.start_time < @SelectedDate + ' 14:00:00' then '28'

when b.start_time >= @SelectedDate + ' 14:00:00' and b.start_time < @SelectedDate + ' 14:30:00' then '29'

when b.start_time >= @SelectedDate + ' 14:30:00' and b.start_time < @SelectedDate + ' 15:00:00' then '30'

when b.start_time >= @SelectedDate + ' 15:00:00' and b.start_time < @SelectedDate + ' 15:30:00' then '31'

when b.start_time >= @SelectedDate + ' 15:30:00' and b.start_time < @SelectedDate + ' 16:00:00' then '32'

when b.start_time >= @SelectedDate + ' 16:00:00' and b.start_time < @SelectedDate + ' 16:30:00' then '33'

when b.start_time >= @SelectedDate + ' 16:30:00' and b.start_time < @SelectedDate + ' 17:00:00' then '34'

when b.start_time >= @SelectedDate + ' 17:00:00' and b.start_time < @SelectedDate + ' 17:30:00' then '35'

when b.start_time >= @SelectedDate + ' 17:30:00' and b.start_time < @SelectedDate + ' 18:00:00' then '36'

when b.start_time >= @SelectedDate + ' 18:00:00' and b.start_time < @SelectedDate + ' 18:30:00' then '37'

when b.start_time >= @SelectedDate + ' 18:30:00' and b.start_time < @SelectedDate + ' 19:00:00' then '38'

when b.start_time >= @SelectedDate + ' 19:00:00' and b.start_time < @SelectedDate + ' 19:30:00' then '39'

when b.start_time >= @SelectedDate + ' 19:30:00' and b.start_time < @SelectedDate + ' 20:00:00' then '40'

when b.start_time >= @SelectedDate + ' 20:00:00' and b.start_time < @SelectedDate + ' 20:30:00' then '41'

when b.start_time >= @SelectedDate + ' 20:30:00' and b.start_time < @SelectedDate + ' 21:00:00' then '42'

when b.start_time >= @SelectedDate + ' 21:00:00' and b.start_time < @SelectedDate + ' 21:30:00' then '43'

when b.start_time >= @SelectedDate + ' 21:30:00' and b.start_time < @SelectedDate + ' 22:00:00' then '44'

when b.start_time >= @SelectedDate + ' 22:00:00' and b.start_time < @SelectedDate + ' 22:30:00' then '45'

when b.start_time >= @SelectedDate + ' 22:30:00' and b.start_time < @SelectedDate + ' 23:00:00' then '46'

when b.start_time >= @SelectedDate + ' 23:00:00' and b.start_time < @SelectedDate + ' 23:30:00' then '47'

when b.start_time >= @SelectedDate + ' 23:30:00' and b.start_time <= @SelectedDate + ' 23:59:59' then '48'

ELSE 'Other' END )as interval_id,

b.call_flow_name, b.source, b.lang, isnull(count(b.call_flow_name),0) as hit, 'No Dial Tone' as report_item

FROM ann_events_Tech_Details b

WHERE(dateadd(dd,0, datediff(dd,0,b.start_time)))= @SelectedDate AND b.call_flow_name = @call_flow_name and b.sel_tel_nodialtone =1

GROUP BY