December 17, 2011 at 9:39 am
I have wanting to create running totals in a view/query.
An example of what I am trying to do is, from a table of sales orders and their lines to put a line number for that order line
December 17, 2011 at 10:04 am
The best way to do this is with the OVER clause. You should read through the documentation, here http://msdn.microsoft.com/en-us/library/ms189461(SQL.110).aspx.
December 17, 2011 at 10:09 am
Thanks, sorted it
SELECT
CAST('ALT' AS CHAR(3)) AS RecordType,
CAST('' AS CHAR(14)) AS MainProduct,
CAST('1' AS CHAR(1)) AS ShowAlternative,
CAST('' AS CHAR(5)) AS SequenceNumber,
CAST('' AS CHAR(14)) AS Alternatives,
CAST('' AS CHAR(1)) AS DoNotUse,
CAST('' AS CHAR(5)) AS CatalogCode,
CAST('' AS CHAR(20)) AS DoNotUse,
CAST('' AS CHAR(1)) AS Reserved,
CAST(STK_ALTERNATIVE.STK_ALTN_PARENT AS CHAR(30)) AS MainProductLong,
CAST(STK_ALTERNATIVE.STK_ALTN_CHILD AS CHAR(30)) AS AlternativesLong,
CAST('' AS CHAR(900)) AS FreeForUse,
COUNT(DISTINCT STK_ALTERNATIVE.STK_ALTN_CHILD) AS TEST,
ROW_NUMBER() OVER(PARTITION BY STK_ALTERNATIVE.STK_ALTN_PARENT ORDER BY STK_ALTERNATIVE.STK_ALTN_PARENT asc, STK_STOCK_2.STK_SELLPRICE1 -STK_STOCK_2.STK_COSTPRICE1 desc) AS 'Row Number',
STK_STOCK_2.STK_SELLPRICE1 -STK_STOCK_2.STK_COSTPRICE1 AS Profit
FROM STK_ALTERNATIVE INNER JOIN
STK_STOCK ON STK_ALTERNATIVE.STK_ALTN_CHILD = STK_STOCK.STKCODE INNER JOIN
STK_STOCK_2 ON STK_STOCK.STKCODE = STK_STOCK_2.STKCODE2
GROUP BY STK_ALTERNATIVE.STK_ALTN_PARENT, STK_ALTERNATIVE.STK_ALTN_CHILD, STK_STOCK_2.STK_COSTPRICE1,
STK_STOCK_2.STK_SELLPRICE1
ORDER BY STK_ALTERNATIVE.STK_ALTN_PARENT asc, STK_STOCK_2.STK_SELLPRICE1 -STK_STOCK_2.STK_COSTPRICE1 desc
December 17, 2011 at 10:10 am
Actually, using the rownumber() function with the over clause. If you post the DDL (create table statements) for the tables involved, sample data (a series of insert into statements) for each table, and the expected results I am sure we can quickly show you want you need. As for sample data, just enough data to represent the problem domain; 3 or 4 rows in the parent table and 8 to 12 in the child table for this particular example.
December 17, 2011 at 10:10 am
Never mind.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply