locked
appending data to the selected cell RRS feed

Answers

  • User-917364509 posted
    CREATE TABLE #tmp
    (
    [date] datetime,
    val int
    )
    
    INSERT INTO #tmp ([date], val) VALUES ('2012-04-01', 10)
    INSERT INTO #tmp ([date], val) VALUES ('2012-05-01', 20)
    INSERT INTO #tmp ([date], val) VALUES ('2012-04-02', 10)
    INSERT INTO #tmp ([date], val) VALUES ('2012-04-24', 34)
    INSERT INTO #tmp ([date], val) VALUES ('2013-04-24', 50)
    
    --Monthly
    SELECT DATENAME(MONTH, [date]) as Month, DATENAME(YEAR, [date]) as Year, SUM(val)
    FROM #tmp
    GROUP BY DATENAME(MONTH, [date]), DATENAME(YEAR, [date])
    
    --By weekday
    SELECT DATENAME(DW, [date]) as DayOfWeek, SUM(val)
    FROM #tmp
    GROUP BY DATENAME(DW, [date])
    
    --By year
    SELECT DATENAME(YEAR, [date]) as Year, SUM(val)
    FROM #tmp
    GROUP BY DATENAME(Year, [date])
    
    --By week
    SELECT DATENAME(WEEK, [date]) as Week, DATENAME(YEAR, [date]) as Year, SUM(val)
    FROM #tmp
    GROUP BY DATENAME(WEEK, [date]), DATENAME(YEAR, [date])
    
    DROP TABLE #tmp

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 12, 2012 6:44 AM

All replies

  • User-917364509 posted
    CREATE TABLE #tmp
    (
    [date] datetime,
    val int
    )
    
    INSERT INTO #tmp ([date], val) VALUES ('2012-04-01', 10)
    INSERT INTO #tmp ([date], val) VALUES ('2012-05-01', 20)
    INSERT INTO #tmp ([date], val) VALUES ('2012-04-02', 10)
    INSERT INTO #tmp ([date], val) VALUES ('2012-04-24', 34)
    INSERT INTO #tmp ([date], val) VALUES ('2013-04-24', 50)
    
    --Monthly
    SELECT DATENAME(MONTH, [date]) as Month, DATENAME(YEAR, [date]) as Year, SUM(val)
    FROM #tmp
    GROUP BY DATENAME(MONTH, [date]), DATENAME(YEAR, [date])
    
    --By weekday
    SELECT DATENAME(DW, [date]) as DayOfWeek, SUM(val)
    FROM #tmp
    GROUP BY DATENAME(DW, [date])
    
    --By year
    SELECT DATENAME(YEAR, [date]) as Year, SUM(val)
    FROM #tmp
    GROUP BY DATENAME(Year, [date])
    
    --By week
    SELECT DATENAME(WEEK, [date]) as Week, DATENAME(YEAR, [date]) as Year, SUM(val)
    FROM #tmp
    GROUP BY DATENAME(WEEK, [date]), DATENAME(YEAR, [date])
    
    DROP TABLE #tmp

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 12, 2012 6:44 AM
  • User-1389448849 posted

    thanks mm10, how can I apply this in SSRS, can you guide me

    Thursday, April 12, 2012 7:06 AM