This is awesome advice. Is that in the...

Did you try using MAX? That works with VARCHARs.

I agree that you...

While Sean and Lowell are essentially correct in their suggestions that there's probably a better way to structure your tables to handle the tax consequences of an invoice, I have...

Sharon,

I think you've been around long enough to know that you get better help by posting DDL and consumable sample data. But I'm feeling generous on this Monday morning...

Are you looking for something like this?

SELECT RowID=ROW_NUMBER() OVER (ORDER BY AccountID, RandomDate)

,AccountID, RandomDate, NextDate, LeadInMonths

,RankForThisLeadInMonths=SUM(lm) OVER

...

You can also use a pattern-based string splitter like PatternSplitCM, which can be found in the 4th article in my signature links.

WITH SampleData (MixedStr) AS

(

SELECT '1234heaven56-guy'

),

...

This query will get you the output you requested but I'm sure it is way over-simplified.

SELECT invoice_no

,item_no=MAX(CASE WHEN item_type <> 'K' THEN item_no END)

...

Shanmuga Raj (1/9/2014)

---

dwain.c (1/9/2014)

---

Something like this perhaps?

WITH rCTE AS

(

SELECT b.Mode_ID, b.Mode_info, Mode_Detail=Mode_info, QA, QC, QY, Mode_Sno, b.Has_Nodes

FROM Travel_Quantity a

...

Not really. The results set is close but not exact, which means he'll need to...

Luis Cazares (1/9/2014)

---

If the intention is to simplify, here's another option.

SELECT a.invoic_no, a.item_no

,Sales= CASE WHEN item1 = a.item_no THEN a.invoic_qty ELSE 0 END

...

Something like this perhaps?

WITH rCTE AS

(

SELECT b.Mode_ID, b.Mode_info, Mode_Detail=Mode_info, QA, QC, QY, Mode_Sno, b.Has_Nodes

FROM Travel_Quantity a

JOIN Travel_Master b...

Using Dohsan's sample data, here is another solution that should work in SQL 2000 that is a bit more concise:

SELECT a.invoic_no, a.item_no

,Sales=CASE WHEN item1 =...

Phil Parkin (1/8/2014)

---

A few tasty techniques in that function Dwain. I have a quick follow-up question for you: as the function does not reference any external tables or views, does...

thava (1/8/2014)

---

intresting one i have one more option

how about this one

SELECT @Result = NULL;

-- Option 6:

SELECT @Result = CASE

WHEN @aa =@XXX AND @XXXCount...

Phil Parkin (1/7/2014)

---

Does your proc use a calendar table? If yes, I would think that adding a 'Holiday' column to it would be the easiest solution. If not, consider implementing...