October 11, 2012 at 2:09 pm
I have a table (docs) that keeps track of the creation date of documents (CreateDate). I want to create a simple SQL query that tells me, for any given month, how many documents are in the system (Total documents with a createdate before 2011-01, Total documents with a createdate before 2011-02, etc). I'm thinking I should be using OVER, but I can't get it to work. I'm working with the following fields
[Name] [nvarchar] (248) NOT NULL
[CreatedBy] [int] NULL
[CreateDate] [datetime] NULL
[ModifiyDate] [datetime] NULL
[ModifiedBy] [int] NULL
I would like to see results where each month is a new cumulative total like so
MonthTotalDocs
2012-061500000
2012-071700000
2012-082000000
I can get this far:
SELECT DISTINCT datename(year, CreateDate) + ' - ' + CAST(Datepart(month, CreateDate) as nvarchar(2)) as [Month], COUNT(*) as [TotalDocs]
FROM docs
Where (CreateDate < DATEADD(month, datediff(month, 0, getdate()), 0))
Group By datename(year, CreateDate) + ' - ' + CAST(Datepart(month, CreateDate) as nvarchar(2))
That just gives me the documents created during each month though. How do I modify this to make that column a cumulative?
October 11, 2012 at 3:14 pm
This is a running total problem. Jeff has a great article about it here. http://www.sqlservercentral.com/articles/T-SQL/68467/%5B/url%5D
If you can't figure out how to put that all together post back here. In order to help with code you will need to post ddl (create table scripts), sample data (insert statements) and desired output based on your sample data. You can take a look at the first link in my signature about best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply