Post reply

Running totals

  • December 17, 2011 at 9:39 am

    #252172

    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

    #1422846

    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

    #1422847

    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

    #1422848

    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

    #1422849

    Never mind.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply