Microsoft SQL Add to Query Monthly

Question

So I'm building a query for a graph in .net. My issue is, I have no clue on how to add up the month to the next month. So here is my query below and what I am getting from it.

I would like that for example, April would be = to April + Jan, Fev, March ... and so on so June would be = to June + Jan, Feb, March, Apr, May ....

SELECT COUNT(*) As 'Deployed',  
    DATENAME(YEAR, LastModified) As 'Year', 
    DATEPART(Month, LastModified), 
    DATENAME(Month, LastModified) As 'Month'
FROM [InventoryDatabase].[dbo].[Hardware_RefreshList] 
WHERE Asset_Type = 'Laptop' AND Status = 'Completed' AND Department != 'SNBc' 
      AND DATENAME(YEAR, LastModified) = '2017' 
GROUP BY DATEPART(Month, LastModified), DATENAME(YEAR, LastModified), 
         Month(LastModified), DATENAME(Month, LastModified), 
         DATEADD(MONTH, DATEDIFF(MONTH, 0, LastModified), 0)
ORDER BY  DATEPART(Month, LastModified)

Result from the query
2 2017 3 March 6 2017 4 April 8 2017 6 June 6 2017 7 July 9 2017 9 September 29 2017 10 October 10 2017 11 November 54 2017 12 December

The question is a "bit" confusing. `Add to the next month` means one sum for Jab+Feb, another for Feb+March or whatever. Yet the next paragraph describes adding from the start of the year *up to* the target month. Which is something called `YEAR to Month` by the way, and has many answers in SO — Panagiotis Kanavos, Jan 24 '17 at 15:24
Please post your original data, what the desired outcome is, and a *clear* description of what you want to do. Make sure the query is well-formatted as well. As it is, it's hard to guess what each of the result fields is supposed to show — Panagiotis Kanavos, Jan 24 '17 at 15:24
Do you mean to get all month even if there is no value for an specific month? — McNets, Jan 24 '17 at 15:25
Post your input data and the expected output. That way it is easy to check the solution. — David Söderlund, Jan 24 '17 at 15:27
You are looking for a "running total". This is what you need to research. [Here's a place to start](http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server) — S3S, Jan 24 '17 at 15:33

score 0 · Answer 1 · answered Jan 24 '17 at 16:18

You can do this with count() over()

select 
     [Year]      = datepart(year , LastModified)
   , [Month]     = datepart(Month, LastModified) 
   , [MonthName] = datename(Month, LastModified) 
   , DeployedThisMonth = count(*)
   , DeployedSoFar = count(*) over (
        order by datepart(year , LastModified)
               , datepart(Month, LastModified)
     )
  from [InventoryDatabase].[dbo].[Hardware_RefreshList]
  where Asset_Type = 'Laptop' 
    and status = 'Completed' 
    and Department != 'snbc' 
    and datepart(year, LastModified) = 2017
  group by 
      datepart(Month, LastModified)
    , datepart(year, LastModified)
    , Month(LastModified)
    , datename(Month, LastModified)
    --, dateadd(month, datediff(month, 0, LastModified), 0)
  order by datepart(Month, LastModified)

SqkZim that will work Count() and oveR(). thanks for the help — , Jan 24 '17 at 16:50

Microsoft SQL Add to Query Monthly

1 Answers1