dwain.c (12/26/2013)

---

I hate to be the bearer of bad tidings during this holiday season, but this just came to my attention this morning.

Steven Willis, whom some of you may know...

eobiki10 (12/23/2013)

---

Thanks, Sean earlier pointed it out in an earlier response. However, I have this code from another table which I would want to add as a column to the...

Jeff Moden (12/23/2013)

---

ChrisM@Work (12/23/2013)

---

Jeff Moden (12/23/2013)

---

ChrisM@Work (12/23/2013)

---

Isn't this all rather irrelevant though? The source table is denormalised - it contains two types of data, products and subproducts. The output contains...

Probably - it fits your verbal specification. But don't take it from me. Always test.

Excellent. There's a lesson here - know your data. If you knew in advance of writing...

SELECT

r.dbPatID,

r.dbPatLastName,

r.dbAddDate,

r.LastName,

r.dbStaffLastName,

SUM(t.LedgerAmount) as Outstanding,

(SUM(CASE WHEN t.LedgerAmount <= 0 THEN t.LedgerAmount ELSE NULL END) * -1) as Charges,

SUM(CASE WHEN t.LedgerAmount > 0 THEN t.LedgerAmount ELSE NULL END)...

Check in Books Online for UPDATE, specifically UPDATE FROM. If you are still unsure, then take the time to read the posting etiquette article linked in my signature "please read...

Right joins give me a headache:

INSERT INTO tblPricingExc2 --table tblPricingExc2 has an Identity column as PK.

(Key, Flag, Id)

SELECT

tmpExc.Key ,

tmpExc.Flag ,

tmpExc.Id

FROM #tmpPrice tmpExc

LEFT JOIN tblPricingExc2 exc

ON tmpExc.Id = exc.Id

WHERE...

Jeff Moden (12/23/2013)

---

ChrisM@Work (12/23/2013)

---

Isn't this all rather irrelevant though? The source table is denormalised - it contains two types of data, products and subproducts. The output contains two types of...

Have you tried aggregating the bills in isolation from the rest of the query? Here's one way which looks clean and performs quite well:

SELECT

p.period

,m.clnt_matt_code AS [Client Code]

,m.matter_name

,o.offc_desc

,'Insolvency' AS matter_code

,ISNULL(l.Merged_Source_of_Work_Lender_Desc,k.name)...

Please post a sample data script as per this article [/url]- it's very difficult to tell what you are looking for.

Is bo_Live5.dbo.BLT_BILLM the only table in your FROMlist which has sets of rows having the same value of MATTER_UNO/matter_name?

Are you expecting, in your output, only one row per matter_name?

Your code is missing GROUP BY and will throw an error without it.

Tableb places no restriction on which rows of Tablea are selected for output because it's left-joined. As Jeff...

Please post some sample data and the expected output. It's very difficult to tell what you want from your description.

Tops my offering by a mile:

;WITH Subproducts AS (

SELECT PrimaryID = LEFT([Product Id],CHARINDEX('.',[Product Id],0)-1), *

FROM #Product_Table

WHERE IsPrimary <> 1)

SELECT

PrimaryID,

Subproduct = '',

quantity...