.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Query to Return Rolling 12 Month Totals

Posted By:      Posted Date: October 13, 2010    Points: 0   Category :Sql Server


I have a table that looks like this:

[Date], [Person], [Value]

22/03/2008, Person A, 3.1

22/03/2008, Person B, 4.2

03/04/2008, Person B, 5.1

04/04/2008, Person B, 3.0

06/04/2008, Person C, 3.0

I need a query to return a table that shows the totals of each person's value for each month of the proceding 12 months from Date X.  So if Date X is 30/04/2008 then my result table would look like this:

[Month], [Total]

March, 7.3

April, 11.1

Thanks in advance.





View Complete Post

More Related Resource Links

Check if Linq query return result

HelloI have Linq query likeDim Prood= (From p InProdutcts _                                          where p.ProductID="InputInteger"                                                         ..... ) .SingleOrDefaultI could Use Count if I Use .ToList Instead of .SingleOrDefault , But now that I use 'SingleOrDefault', how Could I find if Prod contains any record or it's Empty.

How to create a calcuated member, which will always return the date one month before the currentmemb

I need to create a calculated member in the cube script, which will be subsequently used in various other script calculations.  It needs to return the date, which is 1 month before the currentmember of the Date dimension. I tried something like this: CREATE MEMBER CURRENTCUBE.[Measures].[LastPD]   AS           PARALLELPERIOD([Date].[Calendar].[Month], 1, [Date].[Calendar].currentMember) ,VISIBLE = 1; However when I then try to check the values with this query: SELECT [Measures].[LastPD] on 0, [Date].[Calendar].[Date].members on 1 FROM [TravCSAT] ; I only get NULLs for [LastPD].  Any idea what I am doing wrong?

Return default value from LINK query

I am using the  query listed below which includes multiple joins, in the result of the query I need to do some value replacement values Example: db.ASSETS.BUILDING_ID=300 that ID does not exist in db.BUILDINGS, in that case I need to return BUILDING_ID=0, db.BUILDINGS.NAME="Unassigned" Please help  var productQuery = from assets in db.ASSETs join relocatableUnits in db.UNITs on assets.UID equals Units.RUID into assets_units from Units in assets_units.DefaultIfEmpty() join build in db.BUILDINGs on assets.BUILDING_ID equals build.BUILDING_ID into assets_bins from build in assets_bins.DefaultIfEmpty() join test in db.TEST_SUITEs on assets.TEST_SUITE_ID equals test.TEST_SUITE_ID into test_bins from test in test_bins.DefaultIfEmpty() join testTrgt in db.TEST_SUITEs on assets.T_TEST_SUITE_ID equals testTrgt.T_SUITE_ID into testTrgt_bins from testTrgt in testTrgt_bins.DefaultIfEmpty()

MDX query to only return first row.

I have a given MDX query AND would like to only return a single row instead of the full result set. also I need to do this in a way so I can simple add a line to it without re-writting the entire statement. any suggestions how to do this? background: I running stored MDx queries against the cube using OPENQUERY. the result set I parse first to understand what is part of my dimension (not time) and what column defines the time so I can later on work with the results. any hekp is appreciated.

Display the Sum of hours and Month Year using sql Query

Hi,  I have,hours(in varchar),Id in table tbl_x ,i need the sum of hours, monthyear(eg. mar 2010)for the last three months.But with two separate queries i am getting these results.but actually i need in the following format   Month/Year      TotalHours-----------------    ----------------Mar 2010            0400Apr 2010             0450-----------------------------------to get month/Year i am using this querySELECT right(convert(varchar, fromdate, 106), 8) from tbl_x where id='11101' for the sum of hours SELECT SUM(CAST(hours AS INT))AS TOTALHRS FROM tbl_x WHERE id='11101'

Return linq query in function

I try to return a var query from a function, but i'll get the error: Error:Error 41 Cannot implicitly convert type 'System.Collections.Generic.IEnumerable<AnonymousType#1>' to 'System.Collections.Generic.IEnumerable<System.Xml.XmlElement>'. An explicit conversion exists (are you missing a cast?)  Code: protected static IEnumerable<XmlElement> GetModels(byte MerkID) { string rawXML = AutoDisk.GetModels(12345, true, MerkID, "2010", ""); XmlDocument xml = new XmlDocument(); xml.LoadXml(rawXML); var query = from feed in xml.DocumentElement.ChildNodes.Cast<XmlNode>() select new { nModelID = feed.SelectSingleNode("nModelID").InnerText, Modelnaam = feed.SelectSingleNode("Modelnaam").InnerText }; return query; } Whats wrong and how can i make this working ?  

Query to return TOP n with 'Other' grouping for all remaining rows

Hi all, I've been given a specific type of report to produce, and am having difficulty in getting my SQL right to return the data as needed. I need to return TOP n Categories based on amount of sales (which I can do) but then a final category with a sum of all other sales. The purpose would be to capture all of the small sales values into one 'Other' category so that the chart I'll produce does not have lots and lots of small-value columns. I found the following post which seems to come close: SELECT TOP 10 CompanyID, CompanyName, SUM(GrossSales) [GrossSales] FROM CompanySales GROUP BY CompanyID, CompanyName ORDER BY SUM(GrossSales) desc UNION SELECT -1, 'Other' [CompanyName], SUM(GrossSales) [GrossSales] FROM CompanySales WHERE CompanyID NOT IN ( SELECT TOP 10 CompanyID, CompanyName, SUM(GrossSales) [GrossSales] FROM CompanySales GROUP BY CompanyID, CompanyName ORDER BY SUM(GrossSales) desc ) But this gives a syntax error on UNION, which I'm guessing is due to the ORDER BY clauses. However I think the ORDER BY is needed to ensure I return the results in the correct order. Does anyone have any ideas of how to do this? Thanks for any assistance Matt

how to query return all data in Array Object ,Collection Object

how to query return all data in Array Object ,Hash Table ,And DataTable dataRowSelect * From Doc a

function or query to return continuous span of months $ years as rows in query



I am using SS2008.

There are many situations in which I need to return a table where the rows are EVERY calendar month between two dates, not just the months where there is data present.

Is there a function that would take a beginning and end date mmddyyyy which would do this?

For example if I wanted 1/2004 through 8/2010?




and so on to





Thanks in advance,




Query acting weird.. larger date range works in 1min 26sec and a smaller range say 1 month - 3 month


I have no clue why my query is acting weird

If i try to run it for 1/1/2010 9/30/2010 the query takes around 1min 26 sec and  return around a million rows

and If i run for 8/1/2010 to 8/31/2010 it takes forever to it...

basically i am getting data from 5 tables and putting it in a temp table and then updating that temp table 2-3 times with some information and then displaying it.

I am stumped as to why it works fast for a longer date range and runs the  snail for a small period of time..

I am in the verge of pulling my hair and going crazy..

any help will be appreciated.




One to many query: return most common value


I am working on a one to many query for an assets database.  Here is some sample data

Server Name        Application        Primary_User
Server1                 SQL                   DBA
Server1                 Citrix                 IT
Server1                 Oracle                DBA
Server2                 Sharepoint         Web
Server3                 SQL                   DBA
Server3                 Sharepoint         Web
Server3                 Norton               Security
Server3                 IDS                   Security

The desired output is one row per server with the server name, count of applications, and the Primary User that appears the most (not just the first, last, min, or max).

It would look like this

Server Name    Applications  Primary_User
Server1            3                  DBA
Server2            1                  Web
Server3            4                  Security

Is there a sub query that can accomplish this?  Thanks in advance!

Using SQL to return matches from 2 different bits of data in one query


Hi there,

i am writing a website that has a social networking element to it, and i want to display mutual friends of the person being viewed with the person that is viewing.

is it possible to search one database table and look for matching entries using 2 different usernames? if so how would i do it?

hope u can help,

many thanks

Query to return conditional counts both distinct and non distinct.


I have the following table structure:

Table EmailTracking



Table EmailTrackingDetail


The following query returns the default recordset:


SELECT     TOP (200) EmailTracking.EmailAddress, EmailTrackingDetail.TranType, EmailTrackingDetail.TranDate
FROM         EmailTracking INNER JOIN
                      EmailTrackingDetail ON EmailTracking.RecordID = EmailTrackingDetail.EmailTrackingRecordID







2010-09-21 16:59:29.980



2010-09-21 17:19:58.790



2010-09-27 12:00:30.940

SQL to return the name of a month


Hi There,

I have a table with a DATETIME column in it. It stores all of our printing information, input from HP's web jet admin.

Is there any way that I can get a list of the months of data available in there? For example, I want to dynamically create a HTML dropdown box using PHP.

To make this a little clearer - if I had the following entries in my table:


01/01/2010 00:00:00
01/01/2010 00:00:00
01/02/2010 00:00:00
01/02/2010 00:00:00
01/04/2010 00:00:00

The SQL statement would need to return the following values:

January 2010
February 2010
April 2010

Hope I have made myself clear?


different results return from .NET and sql query analyzer



Below is my code how to get the records from a stored procedure. I can get the correct results from sql query analyzer, but less results in .NET. Why it happened? How to solve it? Thank.

strSQL = "EXECUTE sp_Collection '" & strRECID &"','" & strPayPeriodFrom & "','" & strPayPeriodTo & "','" & intPayDays & "' "

objDB = DatabaseFactory.CreateDatabase()
            objDS = objDB.ExecuteDataSet(CommandType.Text, strSQL)

objDB = DatabaseFactory.CreateDatabase()

 objDS = objDB.ExecuteDataSet(CommandType.Text, strSQL)

*NOTE: connected to correct database

sql query for month and year of date

I have a table with smallint columns for day,month,year. Now I want to select data lets say for month 10,year 2009 to month 2,year 2010. Can anyone help me in writing this query?
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend