JJR333 (2/5/2015)

---

Thank you Scott. It is slowly dawning.

The first NOT clause makes all the difference.

Is this T-SQL logic different from say Excel VBA logic?

SUM the turnover of all stores NOT...

You can put the SELECTs directly in the ISNULL, you don't have to use separate queries:

SELECT ISNULL((select H from table_1 where a = 'X' and b = 'Y'),

...

Move the specific nbr_ky to the inner query to limit the rows that have to be read there:

CROSS APPLY (

SELECT TOP (1)

stock_nbr_ky as priorRecord_stock_nbr_ky,

stock_nbr,

item_desc,

unit_price,

ui_conversion_factor,

lin_tamcn_ky,

reportable_commodity_type_ky,

security_commodity_type_ky,

stocking_unit_of_issue_cd,

stores_account_cd,

stock_item_cd,

federal_supply_class_cd,

history_record_generated_dt_tm,

FROM his.cat_stock_nbr priorRecord

WHERE priorRecord.stock_nbr_ky = stockNew.stock_nbr_ky

AND priorRecord.cat_stock_nbr_his_ky...

JJR333 (2/5/2015)

---

ScottPletcher (2/5/2015)

---

if @IDHuis is not = 'WH' then select the row, and ignore the check on D.AfdelingZPT; but if = 'WH', then also check to see if D.AfdelingZPT <>...

JJR333 (2/5/2015)

---

Scott thank you.

I can't get my head around it though. 🙂

For one location (@IDHuis = 'WH')

I need to exclude the Hrs from the sum of one department (D.AfdelingZPT...

For efficiency, you might want to specify RECOMPILE on the proc and only test the D.AfdelingZPT column if you have to:

AND (@IDHuis, '' <> 'WH' OR...

There are steps you need, in order:

1) drop the nonclustered index

2) drop the existing clustered index

3) create the new clustered index

4) add the new nonclustered index, if any.

If you are having performance issues, you need to review the indexes themselves too, in particular verifying that you have the best clustered index on each table (hint: it's usually...

Jeff Moden (2/5/2015)

---

ScottPletcher (2/5/2015)

---

Jeff Moden (2/4/2015)

---

RamSteve (2/4/2015)

---

The Solution is as below

That's one solution but it relies on @@DATEFIRST which is subject to change based on selected language and other...

Jeff Moden (2/4/2015)

---

RamSteve (2/4/2015)

---

The Solution is as below

That's one solution but it relies on @@DATEFIRST which is subject to change based on selected language and other things. You...

New Born DBA (2/4/2015)

---

ScottPletcher (2/4/2015)

---

The key thing for performance is the clustered index. Yes, you can instead create gazillions of nonclustered covering indexes, but that actually doesn't help overall...

As a DBA, I dislike doing thousands of logical I/Os -- such as a calendar table -- when instead a simple mathematical calcs can yield the same results:

DECLARE @num_years int

SET...

The key thing for performance is the clustered index. Yes, you can instead create gazillions of nonclustered covering indexes, but that actually doesn't help overall performance much, and could...

New Born DBA (2/4/2015)

---

FYI--When you run this SQL Statement, there are 207,716 records. 1 more thing I also have to mention is that column C1000020534 has only 1 value "Case"...

The row length excluding the Branch column is ~64 bytes:

8 for bigint

40 for nchar(20)

4 for var length ptr area

1 for...