Jeff Moden (7/15/2011)

---

Except for in a function or view where you can't actually use a SET statement, I have no problem with the idea of setting DATEFIRST to whatever I...

Ich habe keine Ahnung, was die Frage bedeutet. 😉

Maybe you can ask using English? Please?

It seems like the statistics of All_Data_Export are out of date (estimated: 1.2mill rows vs. actual 180k).

Did you try to use the CROSS APPLY approach instead of the JOIN on...

Please post the table def for dbo.All_Data_Export including all index definitions as well as the actual execution plan (attached as sqlplan file). It seems like there is no covering index.

Are there any indexes on dbo.Preguntas?

If so, can you post the index definition, too?

I would slightly change the CASE function to protect against wrong results using a different @@language setting:

case datediff(dd,0,dateadd(dd, N, @RunDate )) % 7

when 0 then 3

else 1

end

What is the business logic behind it?

All of the subqueries use a TOP 1 without an ORDER BY. What's the purpose?

Why not simply using the following approach (example of one...

Please stay consistent with your explanation.

In your original example you used 'f%' but not 'x%'. In your latest reply you mentioned 'flr%' but not 'px%' presenting sample code that doesn't...

Glad I could help 😀

Please post the index definitions you might have on your source tables.

I recommend to add an index on #DataHistory(ID,historyid) and #Data(ID).

Other than that, all I can think of to speed...

Please don't hijack other threads (even more if those threads are almost three years old).

Open a new thread and post your question with more a more detailed description than just...

At a first glance it looks like for some values in Item_Name the Opening values are running totals but for other values those are plain values.

For instance, the opening...

I strongly vote against using DATEPART(ww) since it depends on the setting of @@DATEFIRST which can be changed either by SET DATEFIRST or SET LANGUAGE or the language setting of...

Are you looking for something like this?

declare @tbl table

(col1 int, col2 char(2))

insert into @tbl

SELECT 1,'x1' UNION ALL

SELECT 1 ,'f2' UNION ALL

SELECT 2 ,'f1' UNION ALL

SELECT 3 ,'x3' UNION ALL

SELECT...

Something like this?

SELECT

T.c.value('@table[1]','VARCHAR(30)') as TableName,

U.v.value('@column[1]','VARCHAR(30)') as ColumnName,

U.v.value('.[1]','VARCHAR(30)') as LookupValue

FROM @xml.nodes('lookupsEntry') T(c)

CROSS APPLY T.c.nodes('value') U(v)