Jeff Moden (11/29/2011)

---

Cadavre (11/29/2011)

---

But I prefer CLR when I have to do something like this. The RegEx function in .Net is much faster at validating this sort of thing (I'll...

JayK (11/29/2011)

---

Hi,

I've been given the task of coming up with a query to allow our company send out emails to our customers based on their geographical location and order history,

Example...

The issue is what do you do when you have an invalid date? With the data being stored as VARCHAR, you almost certainly will have some invalid dates in there...

Guess based on your data.

BEGIN TRAN

--Sample data as show by the OP

DECLARE @TABLE...

Guess based on your data -

BEGIN TRAN

--First, let's build some sample data

DECLARE @tmpOutput AS TABLE (ContractDate DATETIME, ItemId CHAR(6), StartDate DATETIME,

EndDate DATETIME, W_Loc CHAR(3), CollectedGoods INT, HaulageRate MONEY)

INSERT INTO...

This is more difficult than the previous version by a fair way. Before, I simply generated a "CASE" statement to find the string.

With the new version, I'm looking at doing...

The best way would be a calendar table.

http://www.sqlservercentral.com/articles/T-SQL/70482/

http://www.sqlservercentral.com/articles/T-SQL/70743/

http://www.sqlservercentral.com/articles/Test-Driven+Development/71075/

Otherwise you're left with a nice big formula that can only return the last week-day of the month but doesn't adjust for holidays.

DECLARE...

Bear in mind that my solution is going to die a horrible death on a real table with even a...

Hope that CELKO doesn't see that comment 😉

So...

methexis (11/25/2011)

---

Thats fantastic,

I have already used this method on 2 other queries and it works a treat, not to mention nice and fast!

I cant thankyou enough for your help today.

Have...

Agreed.

He added the 31st when you mentioned that he didn't actually need to "distinct-tify" his two columns....

methexis (11/25/2011)

---

Excellent....it works!

Now I don't just want to take the code and go on my way. When I first attempted to do this I used a 'Where' clause to find...

OK, I'm going to paraphrase an article for you.

---

I have a simple question for you, if you get it right then I'll happily help.

Am...

Smash125 (11/25/2011)

---

When i try to create report using report wizard i get empty report. What all the correct setting.

i am using below query to generate report

WITH C AS

(

SELECT YEAR(OrderDate)...