I actually tried to create a test on SQL 2012 (don't have SQL 2014 available) and when I tried to create the unique constraint, it failed saying that it had...

Jason A. Long (11/19/2015)

---

drew.allen (11/19/2015)

---

Jason A. Long (11/19/2015)

---

drew.allen (11/19/2015)

---

The inclusion of the effective date in your key indicates that TableA is tracking some entity over time. If this is...

This is not a problem with FIRST_VALUE, only LAST_VALUE. If you do not specify a window, it defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. For FIRST_VALUE,...

Jason A. Long (11/19/2015)

---

drew.allen (11/19/2015)

---

The inclusion of the effective date in your key indicates that TableA is tracking some entity over time. If this is the case, then the...

The inclusion of the effective date in your key indicates that TableA is tracking some entity over time. If this is the case, then the entity will be in...

Piet's on the right track. Try the following:

SELECT

i.AccountID AS [ProductID1],

MAX(o.AccountID) AS [ProductID2],

MAX(a.AccountID) AS [ProductID3]

FROM [Product1] i

LEFT OUTER JOIN [Product2] o

ON i.ProductAccountID = o.AccountID

LEFT OUTER JOIN [Product3] a

ON i.ProductAccountID = a.AccountID

GROUP...

Your data doesn't cover all possible cases, so it's not clear which approach to use. What do you want to happen with the following data:

Product1Product2Product3

35NULLNULL

35NULL290

35NULL300

35450NULL

35500350

35659NULL

14NULLNULL

14NULL296

40 NULL NULL

Drew

More specifically, CASE statements cannot return Boolean values, because SQL...

You can only delete from one table at a time. You can use a temp table to store the ids that you want to delete. You may also...

Try the following:

SELECT client_id, office_id, start_year, start_month, start_day, sub_order_number, item_id

, LAST_VALUE(sub_order_guid)

OVER(

PARTITION BY client_id, office_id, start_year, start_month, start_day, sub_order_number

ORDER BY amount, sub_order_number

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

) AS order_guid

FROM...

First, hierarchies are not inherently balanced. Trying to force them to be balanced is like trying to force a square peg in a round hole. What is the...

This sounds like the same formatting issue that I proposed a solution for in the thread on store hours.

See if you can adjust that approach to your current situation.

Drew

mm7861 (11/17/2015)

---

Thanks Drew, out of curiosity why do you think LAG is not best for the job? I tried the LAG version and your version and the LAG version runs...

First, your GROUP BY and SELECT clauses are intertwined. You cannot simply remove the GROUP BY clause without updating your SELECT clause.

Second, this line contains a GROUP BY clause...

MIN won't always give the correct answer, because the first order may not be the smallest order. You want to use FIRST_VALUE instead of MIN.

Drew