4COLCP	1	.0000	.0000	        Y
4BRZZZ	1	65.0000	65.0000	        Y
4BLKCP	7752	.0170	131.7900	Y

  SELECT ProductNbr, SUM(Qty), SUM(Price, SUM(ExtPrice)
    FROM InvoiceDetails
   WHERE PrintYN = 'Y'
GROUP BY ProductNbr

CREATE TABLE InvoiceDetail(
	ProductNbr varchar(6),
	Qty int,
	Price money,
	ExtPrice money,
	PrintYN varchar(1),
	SortOrder int
	)

INSERT INTO InvoiceDetail VALUES('4COLCP',1,0,0,'Y',1)
INSERT INTO InvoiceDetail VALUES('4BRZZZ',1,65,65,'Y',2)
INSERT INTO InvoiceDetail VALUES('4BZFZZ',7752,.0051,39.54,'N',3)
INSERT INTO InvoiceDetail VALUES('4BZEZZ',7752,.0119,92.25,'N',4)
INSERT INTO InvoiceDetail VALUES('4BLKCP',1,0,0,'Y',5)

SELECT *,
	COALESCE((SELECT SUM(1) 
                      FROM InvoiceDetail B 
                      WHERE b.PrintYN = 'Y' AND  B.SortOrder < A.SortOrder),0)
                         AS 'myGroup'
INTO #Temp
FROM InvoiceDetail A

SELECT
	A.ProductNbr,
	B.Qty,
	B.Price,
	B.ExtPrice
FROM InvoiceDetail A INNER JOIN (SELECT	MAX(Qty) AS 'Qty',
					SUM(Price) AS 'Price',
					SUM(ExtPrice) AS 'ExtPrice',
					MAX(SortOrder) AS 'SortOrder'
				 FROM #Temp
				 GROUP BY myGroup) B ON A.SortOrder = B.SortOrder
ORDER BY A.SortOrder

DROP TABLE InvoiceDetail
DROP TABLE #TEMP	

create view DerivedGroup
AS
SELECT I.*, (SELECT COUNT(*) from InvoiceDetail D WHERE I.SortOrder > D.SortOrder  AND D.PrintYn = 'n') 
as Derived
FROM InvoiceDetail I
WHERE I.PrintYN  = 'y'
GO
create view FinalDervied
AS
select ProductNbr, Qty, Price, ExtPrice, SortOrder, Derived + Derived - (SELECT SUM(Derived) From derivedgroup D where  D.SortOrder <= G.SortOrder) as Derived
from derivedgroup G

Select G.ProductNbr, MAX(CASE WHEN I.Qty IS NULL THEN G.Qty ELSE I.Qty END) AS Qty,
		 SUM(CASE WHEN I.Price IS NULL THEN G.Price ELSE I.Price END) AS Price,
		 SUM(CASE WHEN I.ExtPrice IS NULL THEN G.ExtPrice ELSE I.ExtPrice END) AS ExtPrice
from FinalDervied G
left join InvoiceDetail I ON i.SortOrder between G.SortOrder - G.Derived and I.SortOrder AND G.Derived > 0
group by G.ProductNbr

quote:

---

Originally posted by ehorn

Nice work Brett.

---