care to share the code Louis?

seems my googlefoo is working today 😀

this a problem that I was aware...

try this article........

http://sqlinthewild.co.za/index.php/2011/02/22/and-now-for-a-completely-inappropriate-use-of-sql-server/

ok...here is an answer

;

WITH acte

AS...

Sean Lange (4/18/2014)

---

JLS, I can't get that link to load. It just spins and spins. From the url is it using...

what do you want the result to be

100 pens or 80 mice ....you could have either for exactly $1000

or a mixture of other products as well......????

as others have already...

quick Google....heres an article that may help

http://davidduffett.net/post/5334646215/get-a-comma-separated-list-of-values-in-sql-with-for

does this get anywhere close...??

;

WITH cte

AS (

SELECT SalesOrderNumber,

ItemNumber,

QuantityOrdered,

MIN(VersionNumber) AS VerMin,

MAX(VersionNumber) AS VerMax

FROM Orders

GROUP BY SalesOrderNumber,

ItemNumber,

QuantityOrdered

)

SELECT cte.SalesOrderNumber,

cte.ItemNumber,

OMin.OrderDate,

OMin.RequestedDeliveryDate,

OMin.PromisedDeliveryDate,

OMin.QuantityOrdered,

OMax.PostingDate,

OMax.QuantityShipped

FROM Orders AS OMin

INNER JOIN cte ON OMin.SalesOrderNumber = cte.SalesOrderNumber

AND OMin.VersionNumber = cte.VerMin

AND OMin.ItemNumber =...

I am concerned that your sample data may be oversimplified...have you considered and provided test data for ALL possibilities.....I am think along the lines of:

>item being deleted from order

>order lines...

is the "quantity changed" column actually recorded in the Orders table?

it doesn't appear in your test setup

sidebar...js this a MS NAV database?

itortu (4/16/2014)

---

I made a comment that reads "the desired output"

That is what the output after processing the orders, respectively s19856 and s20026 should look like.

The column QuantityChanged should show...

Sean

the following works,,,just left out the "QuantityChange" column (don't think its relevant to the question)

CREATE TABLE Orders

(

SalesOrderNumberNVARCHAR(20)

,VersionNumberINT

,ItemNumberNVARCHAR(20)

,OrderDateDATETIME

,RequestedDeliveryDateDATETIME

,PromisedDeliveryDateDATETIME

,QuantityOrderedDECIMAL(38,18)

,QuantityChangeDECIMAL(38,18)

,PostingDateDATETIME

,QuantityShippedDECIMAL(38,18)

,LocationCodeNVARCHAR(10)

)

INSERT INTO Orders (SalesOrderNumber, VersionNumber, ItemNumber, OrderDate, RequestedDeliveryDate,PromisedDeliveryDate, QuantityOrdered, PostingDate,QuantityShipped, LocationCode)

VALUES ('S19856',1,'10568','2014-02-13 00:00:00.000','2014-03-14...

quick note...make sure you understand what is the first day...

your test data.....

USE [tempdb]

SET NOCOUNT ON

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SampleTable]') AND type in (N'U'))

DROP TABLE [dbo].[SampleTable]

GO

CREATE TABLE [dbo].[SampleTable](

[ColA] [int] NULL,

[ColB] [int] NULL,

[ColC] [varchar](50) NULL

)...