This solution requires 2012, since it uses LEAD, FIRST_VALUE, and LAST_VALUE.

WITH holding_ends AS (

SELECT *,

CASE

WHEN h.Amount = 0 THEN h.EffectiveDate

WHEN h.EffectiveDate = FIRST_VALUE(h.EffectiveDate) OVER(PARTITION BY h.Holder ORDER BY h.EffectiveDate ROWS...

Do you have any cases where items are restocked/resupplied? If so, then Alan's solution won't work.

SELECT *

INTO #YourData

FROM

(VALUES

('162386-00','CHB',263,203,143,83,23,0,0),('162386-00','GRR',44,44,44,44,44,44,44),

('162386-00','MAR',0,0,0,0,0,0,0),('162387-00','CHB',83,83,83,83,83,83,83),

('162387-00','GRR',22,12,2,0,0,0,0),('162387-00','MAR',0,0,0,0,0,0,0),

('162388-00','CHB',49,44,39,34,29,24,19),('162388-00','GRR',30,20,10,0,0,0,0),

...

I recently submitted my first article for SSC. I got some great feedback on my rough drafts. Thanks Chris, Graham, and Wayne. I'm not sure what more...

You need to look at pairs of weeks to determine if the first in the pair is greater than zero and the second is zero or less. Since you...

ROWS won't work for the overall total here, because the OP...

Something like the following should give you a start. You didn't say what you wanted to do once you found the overlaps.

SELECT *

FROM dtbl_employee_evolved AS a

INNER JOIN dtbl_employee_evolved AS...

A CASE expression is not an IIF expression. You are allowed and even encouraged to have as many separate tests as necessary. This along with the short-circuit evaluation...

Jacob Wilkins (9/9/2016)

---

The Dixie Flatline (9/9/2016)

---

Okay.... although I'm not going back into the divide by zero thread.

Both SET ANSI_WARNINGS OFF and SET ARITHABORT OFF will give...

When posting dates, it's best to use a location neutral formatting like YYYY-MM-DD. The dates posted can be interpreted as either DD/MM/YYYY or MM/DD/YYYY, which cause some confusion.

Drew

A MERGE won't work here. MERGE requires all of the actions be performed on the same table. Here you are inserting into one table (tbl_OOA_Source_Exception), but deleting from...

Here's an approach that builds off of your original query. You just need to make sure that all of the records match the pattern that you're expecting, so it...

Try the following instead.

SELECT item_id

, CTN

, RL

, FT

, M

FROM

(SELECT item_id

, unit_of_measure

, unit_size

FROM #test_uom

) A

PIVOT (

MIN(unit_size) FOR unit_of_measure IN ([CTN], [RL], [FT], [M])

) AS Pvt

You were providing the...

You run into a similar problem when you want to evaluate the ON clauses in an order different than the JOIN clauses. For instance, with the following query where...

For example the following from my days in fundraising. (NOTE, this is probably not the way I would approach this, but it illustrates the point.)

SELECT *

FROM Constituent c

INNER JOIN...

I think that you're overcomplicating it. This gives the results that you're looking for without any loops.

INSERT PolicyReviewLocks(PolicyNumber, UserID)

SELECT DISTINCT pr.PolicyNumber, @User

FROM PolicyReviews pr

INNER JOIN PolicyReviewStatus prs

ON prs.ReviewID =...