I've basically turned the statement around so the main UPDATE is to the Balance table and the OUTPUT/INTO statement INSERT the transaction into the loads table.

Ran for me as expected...

Jeff - I basically agree with you. I was too lazy to run with STATISTICS ON, so I went back and tried it.

In the SQL below I added primary...

Could you refactor the INSERT statements into the following UPDATE on the Balance table?

UPDATE b SET [AvlBalance] = [AvlBalance] - [LoadAmt]

OUTPUT Inserted.[CardProduct], x.[LoadAmt], x.[LoadDate], x.[LoadType]

INTO [dbo].[Loads]

FROM [dbo].[Balance] b

INNER JOIN (

SELECT...

Why not reverse the SELECT and avoid the INNER JOIN and GROUP BY by using sub-queries?

SELECT

T1.Name

,(SELECT t.TITLE FROM Test t WHERE t.id=1 and t.Name = t1.Name) AS Title

,(SELECT t.TITLE...

MidBar,

This version has a very slightly better query plan cost (49%/51%) than the CTE version and may not have the issue you report when 1 record.

SELECT date_and_time_start

,date_and_time_end

,DATEDIFF(day,date_and_time_start, date_and_time_end) as running_day_diff...

Try this:

SELECT id, date, [value]

FROM (

SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY id, date DESC) AS rk

FROM [temp]

) x

WHERE rk = 1

>> I've created a table made by an unique_id, a date (YYYY-MM-DD), a customer_id and a status columns.<

Microsoft is catching up with ANSI and other SQLs, so you need to...

Can't say if it is the most efficient solution or not but this should work:

DECLARE @tbl TABLE (id INT, dates DATETIME, username INT, active INT)

INSERT @tbl

select 1 as id...

Have you considered doing this?

SELECT prog

,MIN(ordno) AS so

,MAX(ordno) AS fo

,MIN(strtdt) AS strtdt

,MAX(fnshdt) AS fnshdt

FROM #tbl

GROUP BY prog

It returns the same result set as the previous two solutions. Also, solution...

I had a similar problem once and used a string splitter on the list to get what I needed out of it. I happened to choose a SplitString that...

scelements asked:

And I responded:

I probably can't because I developed it for use...

I probably can't because I developed it for use at my company so I'd consider it proprietary.

I believe that most of the articles I've read on performance suggest you do it like this:

select istatus, dtmoveout, bmovedout, *

from tenant

where istatus in (1,4)

and dtmoveout <...

Try something like this. It will run much faster than (many) individual updates.

UPDATE m

SET field_to_update = MyValue

FROM MyTable m

INNER JOIN (

SELECT '1' AS MyKey, '2' As MyValue

UNION ALL SELECT...

I need to obtain the max tableC.surrogateID2 and corresponding tableC.description, based on each tableA.productID, but only including a specific tableC.description_code. I also need to make sure that I am...