Michael answered the original question but I'd like to go one step further:

Since all you need to return is the result of a select statement, you could use an iTVF...

:blush:OUCH!!:blush:

I must have been totally out of my mind when replying... Of course, it's not possible to use column alias from a SELECT clause in GROUP BY, since the grouping...

could you be a little more specific regarding the errors you get?

Not really:

CRATES...

A simple SELECT DISTINCT would return your required result... 😀

We'd need a little more info what you define as "overlapping".

what is your expected output based on your sample data?

How about the following approach?

CREATE VIEW [dbo].[viewname]

AS

SELECT SUM(QUANTITY) AS QtyTotal, RECEIPTNUM, RCPTLNNM, SLTSQNUM, SERIALNUM, QUANTITY, DATERECD, DTSEQNUM, UNITCOST, TRXSORCE, ITEMNMBR, QTYTYPE, BIN, MFGDATE, EXPNDATE, ROW_ID

FROM tablename

group by RECEIPTNUM, RCPTLNNM, SLTSQNUM,...

Please post the error message you get as well as the statement you want to use to create the view.

I guess a view is the right way to do it.

Please tell us a few more details what you're trying to do. I'm sure (at this point) you won't need any kind of loop for the task...

It's been a while since one of our vendors...

Would it be an option to create a sproc executed as a different user (impersonation) who's allowed to run xp_cmdshell with the package source path as input variable and assign...

K. Brian Kelley (11/4/2010)

Think of schema like we do namespaces. Create appropriate schema. Put the stored procedures in the schema. Assign appropriate permissions based on schema. That's how you...

Another option would be to create an SSIS package and run it from within your sproc.

I think because of last activity date is less than 10/01/2009. the simple join won't work.

Your select top 1 clause lacks an ORDER BY. Therefore it'll return the first RANDOM row from your case_activities table.

I guess you'd need an ORDER BY ca.activity_datetime DESC.

As a side...