none
Year to date

    Question

  • DECLARE @TempData as TABLE
    (
    Name nvarchar(1111),
    MonthName nvarchar(1000), 
    CountName int

    )

    INSERT INTO @TempData
    VALUES ('Class 1', 'September 2013', 1),
     ('Class 2', 'October 2013', 4),
    ('Class 2', 'November 2013', 4),
    ('Class 3', 'December 2013', 6),
    ('Class 1', 'January 2014', 2)

    SELECT * FROM @TempData


    Name    MonthName        CountName
    Class 1  September 2013   1
    Class 2  October 2013       4
    Class 2  November 2013    4
    Class 3  December 2013     6
    Class 1  January 2014       2


    I would like a year to date, so September CountName will remain 1 but OCtober will be Sep + October values ie 4+1 = 6 and so on...


    Name    MonthName        CountName
    Class 1  September 2013   1
    Class 2  October 2013        5
    Class 2  November 2013    9
    Class 3  December 2013   15
    Class 1  January 2014       2

    Sunday, March 23, 2014 12:38 PM

Answers

  • You have been active in this forum before. You should know that you should always include which version of SQL Server you are using for accurate help.

    This works on SQL 2008:

    DECLARE @TempData as TABLE
    (
    Name nvarchar(1111) NOT NULL,
    Month char(6) NOT NULL PRIMARY KEY, 
    CountName int NOT NULL

    )

    INSERT INTO @TempData
    VALUES ('Class 1', '201309', 1),
     ('Class 2', '201310', 4),
    ('Class 2', '201311', 4),
    ('Class 3', '201312', 6),
    ('Class 1', '201401', 2)

    SELECT * FROM @TempData

    SELECT Name, Month,
           (SELECT SUM(CountName)
            FROM   @TempData b
            WHERE  b.Month <= a.Month
              AND  b.Month LIKE substring(a.Month, 1, 4) + '%')
    FROM   @TempData a
    ORDER  BY Month

    I took the liberty to change how the month information is stored, because else the subquery is a nightmare to write.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, March 23, 2014 1:09 PM

All replies

  • Sql server 2012 and onwards

    SELECT *,
     SUM(CountName) OVER(PARTITION BY RIGHT(MonthName,4) ORDER BY RIGHT(MonthName,4)
                    ROWS BETWEEN UNBOUNDED PRECEDING
                             AND CURRENT ROW) AS runqty FROM @TempData


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, March 23, 2014 12:44 PM
    Answerer
  • I am using 2008 only. please help with SQL Server 2008
    Sunday, March 23, 2014 12:47 PM
  • You have been active in this forum before. You should know that you should always include which version of SQL Server you are using for accurate help.

    This works on SQL 2008:

    DECLARE @TempData as TABLE
    (
    Name nvarchar(1111) NOT NULL,
    Month char(6) NOT NULL PRIMARY KEY, 
    CountName int NOT NULL

    )

    INSERT INTO @TempData
    VALUES ('Class 1', '201309', 1),
     ('Class 2', '201310', 4),
    ('Class 2', '201311', 4),
    ('Class 3', '201312', 6),
    ('Class 1', '201401', 2)

    SELECT * FROM @TempData

    SELECT Name, Month,
           (SELECT SUM(CountName)
            FROM   @TempData b
            WHERE  b.Month <= a.Month
              AND  b.Month LIKE substring(a.Month, 1, 4) + '%')
    FROM   @TempData a
    ORDER  BY Month

    I took the liberty to change how the month information is stored, because else the subquery is a nightmare to write.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, March 23, 2014 1:09 PM
  • Here you go

    ;WITH cte
    AS
    (
    SELECT *,ROW_NUMBER() OVER (PARTITION BY RIGHT(MonthName,4) ORDER BY RIGHT(MonthName,4)) AS rn
     FROM @TempData
    ) SELECT *, (SELECT SUM(CountName) FROM cte c WHERE RIGHT(c.MonthName,4)=RIGHT(cte.MonthName,4)
      AND c.rn<=cte.rn) cn
      FROM cte


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, March 23, 2014 1:10 PM
    Answerer
  • try this

    DECLARE @TempData AS TABLE 
      ( 
         name      NVARCHAR(1111), 
         monthname NVARCHAR(1000), 
         countname INT 
      ); 
    
    INSERT INTO @TempData 
    VALUES      ('Class 1', 
                 'September 2013', 
                 1), 
                ('Class 2', 
                 'October 2013', 
                 4), 
                ('Class 2', 
                 'November 2013', 
                 4), 
                ('Class 3', 
                 'December 2013', 
                 6), 
                ('Class 1', 
                 'January 2014', 
                 2); 
    
    --SELECT * FROM @TempData; 
    WITH cte 
         AS (SELECT *, 
                    RIGHT(monthname, 4)                         [Year], 
                    Row_number() 
                      OVER( 
                        partition BY RIGHT(monthname, 4) 
                        ORDER BY Cast('01 '+monthname AS DATE)) RowNum 
             FROM   @TEMPDATA) 
    SELECT name, 
           monthname, 
           (SELECT Sum(countname) 
            FROM   cte ci 
            WHERE  ci.year = c.year 
                   AND c.rownum >= ci.rownum), 
           year 
    FROM   cte c 


    Satheesh
    My Blog | How to ask questions in technical forum


    Sunday, March 23, 2014 2:19 PM
  • Thank you for trying to do DDL. Too bad it is wrong. Tables need keys, etc. Why do you create un-sortable, English dependent codes?? I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantages are that it will sort with the ISO-8601 data format required by Standard SQL and it is language independent. 

    Have you ever had a class on the design of encoding schemes? 

    CREATE TABLE Foobars
    (class_name NVARCHAR(11) NOT NULL, 
     foobar_month CHAR(10) NOT NULL
     CHECK (foobar_month LIKE '[12][0-9][0-9][0-9]-[01][0-9]-00') ,
    foobar_cnt INTEGER NOT NULL 
     CHECK (foobar_cnt >= 0)
    );

    INSERT INTO Foobar
    VALUES 
    ('Class 1', '2013-09-00', 1),
    ('Class 2', '2013-10-00', 4),
    ('Class 2', '2013-11-00', 4),
    ('Class 3', '2013-12-00', 6),
    ('Class 1', '2014-01-00', 2);

    >> I would like a year to date, so September foobar_cnt will remain 1 but October will be September + October values ie 4+1 = 6 and so on... << 

    SELECT class_name, foobar_month, foobar_cnt
           SUM(foobar_cnt) 
            OVER (PARTITION BY SUBSTRING(foobar_month, 1, 4)
                   ORDER BY foobar_month
                    ROWS BETWEEN PRECEDING UNBOUND AND CURRENT ROW)
           AS foobar_running_tot
      FROM Foobars;



    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, March 24, 2014 4:24 AM
  • Hi Milli_22,

    Try this:

    DECLARE @RT INT = 0
    DECLARE @YEAR INT = 0
    
    ;WITH cteAccumulativeSum AS
    ( 
        SELECT TOP 100 PERCENT
               CountName
          FROM @TempData
         ORDER BY CAST('01 ' + MonthName AS DATE)
    )
    UPDATE @TempData
       SET @RT = CountName = (CASE WHEN @YEAR <> RIGHT(MonthName, 4) THEN 0 ELSE @RT END) + CountName
          ,@YEAR = RIGHT(MonthName, 4)
    OUTPUT INSERTED.*

    Hope this helps.
    ~ J.

    Monday, March 24, 2014 5:21 AM
  • As per your posted sample data you've month name stored so in that case you need to first convert it to date otherwise you wont be able to calculate running value based on date sequence

    so do something like

    ;WITH CTE AS
    ( 
        SELECT Name,
               CountName,
               MonthName,
               ROW_NUMBER() OVER (ORDER BY CONVERT(datetime,'01 ' + MonthName,106),Name) AS Rn
          FROM @TempData
         
    )
    SELECT c.*,Total
    FROM CTE c
    CROSS APPLY (SELECT SUM(COuntName) AS Total
                 FROM CTE
                 WHERE Rn < = c.Rn
    )c1


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    Monday, March 24, 2014 6:16 AM
  • Have you read that the OP is using SQL Server 2008?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, March 24, 2014 6:20 AM
    Answerer
  • DECLARE @RT INT = 0
    DECLARE @YEAR INT = 0

    ;WITH cteAccumulativeSum AS
    (
        SELECT TOP 100 PERCENT
               CountName
          FROM @TempData
         ORDER BY CAST('01 ' + MonthName AS DATE)
    )
    UPDATE @TempData
       SET @RT = CountName = (CASE WHEN @YEAR <> RIGHT(MonthName, 4) THEN 0 ELSE @RT END) + CountName
          ,@YEAR = RIGHT(MonthName, 4)
    OUTPUT INSERTED.*

    This may or may not work, but it relies on undefined behaviour, and there is all reason to stay away from it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, March 24, 2014 9:00 AM