Use the source column for the string-dates, as discussed here.

Thanks Alan. I should point out that it's quite a long way from being a full Levenshtein implementation! I don't think it would be impossible - just too costly.

Gail's suggestion using DENSE_RANK() is well worth a try when you get time.

mote.ajit2 (1/27/2014)

---

Thanks for reply!

Earlier I was collecting data in some temporary table so that I can use it as input to query on main table. Now I have removed that...

;WITH SampleData AS (

SELECT MyDate = CAST('2014-01-27 05:59:59.997' AS DATETIME) UNION ALL

SELECT '2014-01-27 06:00:00.000' UNION ALL

SELECT '2014-01-27 06:00:00.003' UNION ALL

SELECT '2014-01-27 17:59:59.994' UNION ALL

SELECT '2014-01-27 17:59:59.997' UNION ALL

SELECT '2014-01-27 18:00:00.000')

SELECT...

They're very narrow triangles - having dealt with this in the near past. Nevertheless, with two of them ORred together, the optimiser will likely choke. Try splitting them up and...

Jeff Moden (1/25/2014)

---

wit_jp2001 (1/24/2014)

---

Thanks,

I will try out the new query this afternoon. Just so that eveyone knows this process is to update the 1st SKU with the heaviest ...

DaveDB (1/24/2014)

---

This comment is very insulting.

"Perhaps the reason for my request for additional material is becoming clearer now.

The fact that accomplished professionals, who would usually be able to...

rabih_karam (1/24/2014)

---

as I understand,

"To calculate the number of days between date1 and date2, you can use either Day of year ("Y") or Day ("D"). When interval is Weekday ("W"), DateDiff...

wit_jp2001 (1/24/2014)

---

Thanks,

I will try out the new query this afternoon. Just so that eveyone knows this process is to update the 1st SKU with the heaviest SKU. ...

Does the stored procedure reference the Customers table?

Try to keep your code simple:

SELECT

h.company_no,

h.invoice_no,

h.invoice_locn,

h.invoice_type,

a.item_no as Kit_Ref_group,

b.item_no ,

max(CASE WHEN A.invoice_qty > B.invoice_qty THEN 0 ELSE A.invoice_qty END) as sales ,

b.invoice_qty - (CASE...

Anytime.

Third row up in my sig block has links to a couple of outstanding articles on APPLY. Well worth a read.

CROSS APPLY is an excellent tool for building complex WHERE clauses and would also help you to figure out if it's worthwhile building up an exclusion table as suggested. Once...

Dennis Post (1/24/2014)

---

If you have nolock, then you have the potential for dirty reads, duplicate rows and missed rows and it doesn't matter (for the latter 2) that each process...

Thanks Grant. Me too, there are over 70 reads of the lookup.