aslam.jeewa (9/12/2013)

---

Thank you the most amazing, incredible SQL query. I cannot begin to describe how much this query assisted me. I have one request though please.

I now need to run...

Which of these is closer to the end of the week?

SELECT DATEADD(millisecond, -1, CAST('2013-09-13' AS DATETIME2))

,DATEADD(microsecond, -1, CAST('2013-09-13' AS DATETIME2))

You might want to Google on calendar tables, because I believe you're going to need one to generate a sequence of months back in time as far as you want...

I love hierarchy problems. If Jeff's articles don't help you, I suggest you post DDL, some sample data and the expected results.

Along will come many I'm sure offering various...

ChrisM@home (9/12/2013)

---

paulcauchon (9/12/2013)

---

I see the hitch up here...you need to see the min date and max date from each of the consecutive color/ID group pairings.

Originally, we had been treating the...

You can try this:

WITH SampleData (col) AS

(

SELECT 'Foodhold USA,mlc.'

UNION ALL SELECT 'Beverage Partners Worldwide (North canada)......'

UNION ALL SELECT 'Bread World 8'''

),

Tally (n) AS

(

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT...

What happens if the credit amount is larger than the total for all other rows?

Or is some sort of matching required to exclude only the row with the matching amount...

Jack Corbett (9/12/2013)

---

Brandie Tarvin (9/12/2013)

---

Ed,

Go to http://www.sqlsaturday.com and see if there is a way to register for event notifications. That way if one pops up near you again,...

Thanks for letting me know.

I assume the second one, being simpler, should be faster. If you can confirm that, it would be interesting too.

Have a good day.

Jeff Moden (9/12/2013)

---

dwain.c (9/11/2013)

---

BTW. I really like this.

Jeff Moden (8/31/2013)

---

That's why I teach my Developers to learn the more performant methods... so they can do it in their sleep...

I would have to second that.

You can do away with the temporary table.

with t4 as (

select '2011-01-02' as dd, 123 as prodID union

select '2011-01-03' as dd, 345 as prodID union

select '2011-01-04' as dd, 345 as...

BTW. I really like this.

Jeff Moden (8/31/2013)

---

That's why I teach my Developers to learn the more performant methods... so they can do it in their sleep and won't have...

Another way that should look familiar:

WITH Amt (Amt) AS (SELECT 2000)

SELECT RIGHT(10000000000000+Amt*100,13)

FROM Amt;

If they're always squares, perhaps something like this?

WITH Squares

(

Upperleft_X, Upperleft_Y, Upperright_X, Upperright_Y

,Lowerleft_X, Lowerleft_Y, Lowerright_X, Lowerright_Y, Cell

) AS (

...