jc85 (5/25/2016)

---

ChrisM@Work (5/25/2016)

---

jc85 (5/25/2016)

---

Thanks, added.

Incorrect result is returned, it...

Sergiy (5/25/2016)

---

jc85 (5/25/2016)

---

Thanks ChrisM@Work.

The query took quite some time to complete.

Additional info provided in case you need it.

ann_events_Tech_Details

[start_time] - clustered index

[id] - Unique, non-clustered index (PK)

ann_ReportItem

[source] - non-clustered index

[call_flow_name]...

jc85 (5/25/2016)

---

Thanks, added.

Incorrect result is returned, it is not...

Only one clustered index per table, but the index can contain more than one column.

"Different result" is too vague, please explain.

-- Try this too.

-- Add [source] to the clustered index, initially AFTER [start_time]

SELECT

[date] = CAST(b.start_time AS DATE),

[report_item] = (b.[source] + '-' + 'a.report_item'),

[EN] = SUM(CASE WHEN b.lang...

Can you display the 21 rows from table ann_ReportItem?

Ideally you would do this by posting a script to create a table then a series of inserts to populate it with...

There are a few changes I'd make to your query. Mostly it's about aligning the date filters properly. Try this:

SELECT

[date] = CAST(b.start_time AS DATE),

[report_item] = ( a .source...

jc85 (5/25/2016)

---

Sergiy (5/24/2016)

---

This is exactly the problem.

Make both PK's non-clustered and create a new clustered index on [start_time].

That's what you've got:

WHERE (dateadd(dd,0, datediff(dd,0,b.start_time))) >= @StartDate

AND (dateadd(dd,0, datediff(dd,0,b.start_time))) <= @EndDate

I...

Yes, according to several definitions of spam found on Google. Here's one good contender: "•multiple copies of an identical or...

Don't you have any ordinary (nonclustered) indexes on these tables?

Henning Rathjen (5/24/2016)

---

Hi jc85,

first I would try to use

CONVERT(char(10),b.start_time,121)

instead of dateadd(dd, 0, datediff(dd, 0, b.start_time)).

Furthermore I advise you not to use functions in WHERE caluses. Modify your parameters so...

AS Gail said, and the execution plan of the new query please. "Actual" plans as .sqlplan file attachments.

It's good news. Try this:

SELECT

CAST(b.start_time AS DATE) as date,

(a.source + '-' + a.report_item) as report_item,

SUM(CASE WHEN (b.lang = 'EN' AND b.sel_test = 1 AND a.report_item = 'Test')

...

Can you please confirm which version of SQL Server you are using? You've posted in the SQL Server 7,2000 forum section.

The STUFF function would be good for this too:

SELECT *, CompanyAddress = STUFF(CompanyAddress,pos+1,0,' ')

FROM #SearchRow

CROSS APPLY (SELECT pos = PATINDEX('%[0-9][a-z]%', CompanyAddress)) x

WHERE x.pos > 0