Jeff Moden (3/19/2014)

---

masoudk1990 (3/18/2014)

---

I prefer to write this kind of queries with this technique:

Select

(SELECT DOB FROM [Table] WHERE DOB IS NOT NULL) AS DOB,

(SELECT LNAME FROM [Table] WHERE...

This is similar to the "greedy knapsack" problem (Googleable) but I can tell you flat out it ain't gonna be easy to do in SQL.

:hehe: I like brain teasers! 😛

Both yours and mine look a bit nasty but I think you can do this with a single MERGE statement:

DECLARE @VendorProductINT ...

Just STUFF it:

declare @String varchar(25) = 'asdf-qwer';

SELECT STUFF(@String, 1+charindex('-', @String), 999, '');

🙂

If you insist Jason. 😛 Bad code can be judged on many levels (some of...

funreal7/brendan woulfe/Kenneth J. Moore/sneumersky,

Thanks to all for taking the time to read and comment. What a great community we have here when everyone takes a moment to contribute.

paul.s.lach (3/18/2014)

---

Dwain,

I liked your analysis of the different options for building a resultset that included both an in-effect and an out-of-effect date, BUT I think you missed the boat on...

Sean Lange (3/18/2014)

---

As soon as I saw the...

Oh God Steve, don't get me started!

Good editorial!

I believe that this article might be relevant to your question:

Creating a Date Range from Multiple Rows Based on a Single Date[/url]

Johan,

Thanks and glad you like it.

As a little additional background, one of the things that led me to write this (besides people on the forums always asking the question), is...

Duplicate post. I responded to this question here:

http://www.sqlservercentral.com/Forums/Topic1551846-3077-1.aspx

While MickyT's solution will work (thanks Micky for the sample data!), I tend to shy away from the window aggregate functions for performance reasons.

WITH SampleData AS

(

SELECT *

FROM (VALUES

(12335,CAST('19900101' AS...

Something like this perhaps?

WITH CTE AS

(

SELECT 11011 AS CUSTOMERID, GETDATE() AS LoadDate

UNION ALL

SELECT 11011, LoadDate + 1

FROM CTE

),

Milestones AS

(

SELECT TOP...

I agree, nice Spackle article Wayne.

It included a couple of things I didn't know about PARSENAME (because I'm probably one of the ones that underutilizes it), like it returning NULL...