Everything is working perfectly, thank you everyone!!

I have read through a few articles on stored procedure performance with parameters, variables and literals but still unable to understand why Parameters run faster than variables in the query...

Sergiy (6/1/2016)

---

SELECT CONVERT(DATE, DT.[time slot]) date,

CONVERT(VARCHAR(20),DT.[time slot], 108) + '-' + CONVERT(VARCHAR(20),DATEADD(n, 30, DT.[time slot]), 108)

FROM (SELECT dateadd(n, datediff(n,0, GETDATE())/30*30, 0) as [time slot]

) DT

Is it what you're...

drew.allen (6/1/2016)

---

That's because Sergiy switched the order of the second and third parameters for the DATEADD function in both of his examples. They should be

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

dateadd(n,...

Sergiy (6/1/2016)

---

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...

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...

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?...

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...

Thanks ! I wonder why I never thought of that !

Sergiy (5/31/2016)

---

jc85 (5/31/2016)

---

WHERE month(b.start_time) = @Loc_Month

AND year(b.start_time) = @Loc_Year

Is there any other way to avoid using function in WHERE clause in this scenario?

It doesn't have huge impact on query speed...

WHERE month(b.start_time) = @Loc_Month

AND year(b.start_time) = @Loc_Year

Is there any other way to avoid using function in WHERE clause in this scenario?

It doesn't have huge impact on query speed but as...

Sergiy (5/30/2016)

---

You never use values from derived table columns Noisy and Telephony in outer query.

Should it be this?

SUM(CASE WHEN DT.lang = 'EN' THEN CASE a.report_item WHEN 'Noisy' THEN Noisy WHEN...

ChrisM@home (5/27/2016)

---

Sergiy (5/26/2016)

---

ChrisM@home (5/26/2016)

---

Such clustered may be very good on query but it's gonna be a very...

Sergiy (5/26/2016)

---

jc85 (5/26/2016)

---

Sergiy (5/25/2016)

---

If that one goes not so bad then incorporate it into the bigger one:

SELECT dateadd(dd,0, datediff(dd,0,b.start_time)) as [date], b.source, b.lang, SUM(b.sel_test) Test, SUM(b.sel_test2) Test2

FROM TechDetails b

WHERE...

Sergiy (5/25/2016)

---

If that one goes not so bad then incorporate it into the bigger one:

SELECT dateadd(dd,0, datediff(dd,0,b.start_time)) as [date], b.source, b.lang, SUM(b.sel_test) Test, SUM(b.sel_test2) Test2

FROM TechDetails b

WHERE b.start_time >=...