bass8117 (10/27/2011)

---

below is part of code that includes many subselects, sums, etc. here's the problem I have,

in the CHRG0.CHRG_AMT column I have a reverse charge of -289.00 with a CHRG0.CHRG_BAL...

Actually, I didn't read your query closely enough. I didn't see that you were using a subquery.

The two approaches are equivalent and will give the exact same execution...

Here is a fairly simple way to get what you're looking for.

SELECT Year(ms.MonthStart), Datename(Month, MonthStart), WeekOfMonth, Sum(YourAmountField)

FROM YourTable AS l

CROSS APPLY ( SELECT DateAdd(Month, DateDiff(Month, 0, GetDate()), 0) AS MonthStart...

Depending on your indices and your data, the following may be more efficient.

SELECT

o.OrderID,

...

pschwartzbauer (10/26/2011)

---

Can you use the RANK() function? Something like...

SELECT A.Key, A.Date, A.Status, A.Ranking

FROM (SELECT Key, Date, Status

, RANK() OVER(PARTITION BY Key ORDER BY Date DESC) AS Ranking

FROM table) A

WHERE A.Ranking...

TEXT is being deprecated. Use varchar(max) instead.

Drew

You really should test before posting. I think what you wanted was actually

ISNULL(LEFT(middlename, 1) + '.', '')

This...

If it doesn't have a unique key, how are you able to determine it's length?

If your...

mpartridge (10/20/2011)

---

The problem we have is with this line:

td = '<a href="mailto:abc@xyz.com?Subject=Mailto%20Test&cc=xyz@abc.com">mailto:abc@xyz.com</a>', ''

SQL will not recognise that as a HTML link and pastes the whole thing into a table cell,...

The typical way to do this is with FOR XML PATH. Most examples will also use a stuff to remove an initial extraneous delimiter, but I didn't include that...

Sean Lange (10/19/2011)

---

Sure.

declare @Codes varchar(25) = '14, 8, 21458'

;with cte(n) as (select 1

union all select 14

union all select 214

union all select 314

union all select 15

union all select 8)

select *

from...

toddasd (10/19/2011)

---

Here is a second option. Will not be as performant as the splitter option.

Declare @Type_Code varchar(50)

set @Type_Code = '4,11,12'

Select * from table_Type_Codes Where @Type_Code like '%' + table_Type_Codes...

I would use LIKE instead. You'll probably still have to do an Index Scan (assuming that column is indexed on your claim table), but that will probably save you...

There's another approach that edges out the CROSS APPLY approach. Depending on your indexes, this approach might be better.

PARTITIONED AGGREGATE

WITH OrdersRanked AS (

SELECT [CustomerID],ShipVia

, Sum(Freight) OVER( PARTITION BY CustomerID...

Lowell (10/18/2011)

---

i had a ton of problems getting the function to paste cleanly into the forum, because it contains html codes, so here's a link to the text file of...