T-SQL (SS2K8) :: How To Return 3 Month Rolling Average Count Per Username
Mar 30, 2015
how to return the 3 month rolling average count per username? This means, that if jan = 4, feb = 5, mar = 5, then 3 month rolling average will be 7 in April. And if apr = 6, the May rolling average will be 8.
Columns are four:
username, current_tenure, move_in_date, and count.
DDL (create script generated by SSMS from sample table I created, which is why the move_in_date is in hex form. When run it's converted to date. Total size of table 22 rows, 4 columns.)
CREATE TABLE [dbo].[countHistory](
[username] [varchar](50) NULL,
[current_tenure] [int] NULL,
[move_in_date] [smalldatetime] NULL,
[Cnt_Lead_id] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
12/1/07 What i'm trying to do is return a result with total charges from 3 months previous based on a date parameter or just as a whole for that matter. For example:
If my date parameter is 6/14/07, i'd like my total charges to be 15 (6+5+4). If my date parameter is 7/10/07, the charges would be 18 (7+6+5)
I hope that makes sense of where i'm going with this. I've played with this using the "Guru's Guide" solution by Ken Henderson, but still to no avail yet. Here's his code syntax:
Code Block
SELECT a.DayCount, a.Sales, SUM(b.Sales) FROM Sales a CROSS JOIN Sales b WHERE (b.DayCount <= a.DayCount) AS RunningTotal GROUP BY a.DayCount,a.Sales ORDER BY a.DayCount,a.Sales Here is the result set i'm looking for:
Charges
Date
1
1/1/07
2
2/1/07
6
3/1/07
9
4/1/07
12
5/1/07
15
6/1/07
18
7/1/07
21
8/1/07
24
9/1/07
27
10/1/07
30
11/1/07
33
12/1/07 Each date's charges are a culmination of 3 months worth of charges.
Any help would be greatly appreciated. I'll be trying to figure this one out concurrently and if i do, i'll post back!
Example table structure: Id int, PK Name varchar AddDate smalldatetime
Sample data: Id Name AddDate 1 John 01/15/2005 2 Jane 01/18/2005 . . . 101 Jack 01/10/2006 102 Mary 02/20/2006
First, I need to find the month which has the most records, I finally produced the correct results using this query but I am not convinced it's the most efficient way, can anyone offer a comment or advice here?
select top 1 count(id), datename(mm, AddDate) mth, datepart(yy, AddDate) yr from dbo.sampletable group by datename(mm, AddDate), datepart(yy, AddDate) order by count(id) desc
Also, I'm really having trouble trying to get the overall average of records per month. Can anyone suggest a query which will produce only one number as output?
I need calculating a rolling 3 month average cost from the two dataset below. Which is the 3 month Average of Dataset1 / Dataset 2.
Dataset 1:
SELECT(factAdmissions.ContractCode + '-' +factAdmissions.BenefitPlanCode) AS [Contract Code], factAdmissions.AdmitCCYYMM, ISNULL(sum(AmountPaid),0)As [Amount Paid] FROM factAdmissions
[Code] ....
Dataset2:
Select
(factMembership.ContractCode+'-'+ factMembership.BenefitPlanCode) As Product, EffectiveCCYYMM, ISNULL(count(Distinct MemberId),0) As MemberCount From factMembership Where EffectiveCCYYMM >= '200701'
We have a table that has customers invoices and payment records. In some cases a customer has 10 lines with 10 different invoice numbers but may have paid 2 or more invoices with one check. I need to know how many unique payments were made per customer.
I have a temp_max column and a temp_min column with data for every day for 60 years. I want the average temp for jan of yr1 through yr60, averaged... I.E. the avg temp for Jan of yr1 is 20 and the avg temp for Jan of yr2 is 30, then the overall average is 25. The complexity lies within calculating a daily average by month, THEN a yearly average by month, in one statement. ?confused?
Here's the original query. accept platformId CHAR format a6 prompt 'Enter Platform Id (capital letters in ''): '
SELECT name, country_cd from weather_station where platformId=&&platformId;
SELECT to_char(datetime,'MM') as MO, max(temp_max) as max_T, round(avg((temp_max+temp_min)/2),2) as avg_T, min(temp_min) as min_temTp, count(unique(to_char(datetime, 'yyyy'))) as TOTAL_YEARS FROM daily WHERE platformId=&&platformId and platformId = platformId and platformId = platformId and datetime=datetime and datetime=datetime GROUP BY to_char(datetime,'MM') ORDER BY to_char(datetime,'MM');
We have an inventory of devices we service and wish to show the total numbers of active devices per month going 12 months back. How would I go about this?
trying to develop a report that shows the sum total for each month during a specified date range.
I have parameters asking for the start date and end date of the date range, the report needs to show the months in between this date range.
One of the fields in the dataset is durationminutes which I need to sum for each month then divide by 60 to show hours. I then need to show the total of each month in a bar graph (with each month along the x axis).
If anyone can shed some light on how I am going to do this it would be greatly appreciated.
Hi All, I wanna pull a 12 month rolling data and donno how to do that, can anybody help ? Problem: lets say if i run the query today i should get data between oct-3-2006 and oct-3-2007, my main colums is monthyear which is date datatype. Appreciate your help. Thanks Chinna
ID varchar (contains alphanumeric values,not unique) Territory (combined with ID unique) Total_Used int can be null Date_ date (date of the import of the data) ID Territory Total_Used Date_ ACASC CAL071287 2014-06-01 ACASC CAL071287 2014-08-01 ACASC CAL071288 2014-09-01
[Code] .....
Now the problem,per month I need the most recent value so I'm expecting
Thanks for taking the time to read my post. I greatly appreciate it!
What i'm trying to do is get a distinct count of account numbers within a rolling period. My actual take is rather large but i've created a smaller-like version below. Please reference this table.
Account Date
1 1/1/08
2 1/2/08
3 1/2/08
2 2/8/08
4 2/9/08
1 2/15/08
1 3/5/08
5 3/6/08
4 3/9/08
3 3/10/08
1 4/1/08
5 4/9/08
2 4/15/08
3 4/26/08
1 5/3/08
2 5/15/08
3 5/29/08
6 5/30/08
Let's say i want to return distinct count of accounts within a 2-month rolling period meaning in February, i'd get a distinct count for accounts in January & February, then in March i'd get a distinct count for February & March, then in April i'd get it for March & April, and so on... my results table would like the table below:
Account Month
3 1
4 2
5 3
5 4
5 5
I had asked this before but it was a summing equation and not a unique count. I've tried to play with the summing equation to kind of make it work, but i'm starting to get a headache. It's probably so simple!
Here's my previous post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2289509
Actually i posted this problem earlier , hoping that i got the result , did not look in depth untill i realized the values i got was getting was wrong.
The Scenario is still the same.
3 MTD should be the past two completed months as well as the dates in the current month as well. So taking the example above – if the reporting period for Feb was 01/30/2007 to 02/27/2007, and March was 02/28/2007 to 03/27/2007, the 3 MTD should then be the average from 01/30/2007 to 04/10/2007.
I am creating a Calculated Member as measure.
Basically if i select the average on 76th day of the year, it should basically be the average of 76th Day ( well offcourse whould ignore the empty cells )
For Example the result set should be very similar , It is basically doing MTD
Running balance is basically adding up , where as 3 month to date average should be average of 3 month previous average
suppose today is 75th day , so it should be average of 30days(1st Month ) + 31days(2nd month)+ 14days(of this month)
The problem i have here is which i am coming close to conclusion
When i use month level it gives me average of month level , the result on month level is fine. But my requirment is to have it on date level. but how do i have rolling average of 3 month in a date level, if i do a date level with 90 days lag which is not correct which is average of 90 days from current day.
AVG([Tbl Date Key].[Report Hirerachy].CurrentMember.Lag(2):[Tbl Date Key].[Report Hirerachy].CurrentMember,[Measures].[Ab1_Avg]
)
When i drill down to date level , which would be assumed
AVG([Tbl Date Key].[Report Hirerachy].date.Lag(2):[Tbl Date Key].[Report Hirerachy].date,[Measures].[Ab1_Avg]
)
its doing a lag on 3 days lag as appose to , I need the lag on 3 months on a day which would be 90 days
should i be doing a lag on days.
The problem at 90 days lag would be every it would lag 90 days average , but what i am looking for it is when it is on the middle of the month it should be
suppose today is 15th day of month, so it should be average of 30days(1st Month ) + 31days(2nd month)+ 14days(of this month)
I am storing duration of a lot of jobs in a column in a table per job. This duration is in seconds and an integer datatype.
Sample data: Job     Duration   date Job1    25      2015/9/23 Job2    30      2015/9/23 Job3    45      2015/9/23 Job4    1       2015/9/22
Now I need to get average duration per day for every month. Is this possible? I have a calendar table that has every single day month year microsecond millisecond  second minute and hour.
I would like to AUTOMATICALLY count the event for the month BEFORE today
and
count the events remaining in the month (including those for today).
I can count the events remaining in the month manually with this query (today being March 20):
SELECT Count(EventID) AS [Left for Month], FROM RECalendar WHERE (EventTimeBegin >= DATEADD(DAY, 1, (CONVERT(char(10), GETDATE(), 101))) AND EventTimeBegin < DATEADD(DAY, 12, (CONVERT(char(10), GETDATE(), 101))))
Could anyone provide me with the correct syntax to count the events for the current month before today
and
to count the events remaining in the month, including today.
I am a SQL newbie trying to create a registration page. The registration data is stored in "tblUsers," and the username is stored in the field named "UID" -- which is the primary key. If the user enters a username that already exists in the database, I want to return the "That username already exists" message and keep them on the registration page. If the username doesn't exist, I want to write all their registration info to the database.
I tried the code below using both an existing username and then using a username that didn't exist. Both times I got the "that username already exists" error message on the top of the confirmation page, and the record was not written to the database.
I also tried changing the "If objRec.RecordCount <> 0" to "If objRec.RecordCount >0" I got it to write to the database if the username didn't exist, but if the username did exist, I got the microsoft error '80040e14' (The changes you requested to the table were not successful because they would create duplicate values...)
Hi AllWe have an order processing database which includes the standard OrderHeader/Order Lines tables. I'm trying to write a query to get the averagenumber of lines per order over a given period, and I'm stuck :-(I can get the number of lines per order with:SELECT COUNT(*) FROM OrderDetailsINNER JOIN Order Header ONOrderHeader.OrderNo = OrderDetails.OrderNoWHERE OrderHeader.OrderDate ..... {various criteria} ...GROUP BY OrderDetails.OrderNumberBut how do I then get an average of this for the period?TIAMike Bannon
Im trying to get the average Fuel Consumption for A Manufacturer that produces two or more cars, so far ive only been able to find all manufacturers Average Fuel consumption.
Heres what I have so far
Select aManufacturer.MName, avg(FuelCons) From aCar Join aBuilts On aBuilts.CName = aCar.CName Join aManufacturer On aBuilts.MName = aManufacturer.MName Group by aManufacturer.MName
This produces nearly all I want only I need to be able to get only the Manufacturers who produce two or more Cars, ive tried implementing a few Count statements but nothings working, any ideas?
I am having tables with 12 columns as jan,feb,.......dec.
Now I want to calculate average salary for each record, but condition is that if any month salary is zero then that column also exclude from average calculation.
For example : if jan and feb column values are zero then i want to calculate (mar+apr+...+dec)/10.
I have a need to create a line graph report in SSRS 2008. The report should show the top 10 servers from a group of servers with the highest CPU utilization for the last day. The report is for Microsoft System Center Operations Manager 2012. I have a SQL query that will return the average CPU for all of the servers in the group, with the average for each hour (24 records per server).
How can I get the top 10 servers with the highest average CPU? I think I need to create an average of the averages, then select the top 10. Here is the SQL query I have so far:
Use OperationsManagerDW GO SELECT vPerf.DateTime, vPerf.SampleCount, cast(vPerf.AverageValue as numeric(10,2)) as AverageCPU, vPerformanceRuleInstance.InstanceName,
I have a matrix report with 2 column SaleAmount and ProfitAmounts by Month like
Sale Profit Dealer 5/1/2007 6/1/2007 7/1/2007 5/1/2007 6/1/2007 7/1/2007 A 100 200 300 20 25 15 B 200 250 50 30 45 19
how can i do following 3 things
1)Add Total column for Sale and Average column for Profit 2)Sort report by lastMonth of Sale (here 7/1/2007) High to low 3)if last month of sale(here 7/1/2007) is less than second last month here (6/1/2007) whole row should be red
So some have just 1 test and some have multiple. I have a count for each. What I need to do is use that count and get an average time between each test per student.
I have got this matrix and I am trying to calculate the average amount of working days in a month. At the moment, I have divided the total number of jobs by 21 for every month which is a hard coded value. However, I am not sure how to retrieve this value dynamically. Is there any formula that can find out the working days?
This is my function, it returns SQLDataReader to DATALIST control. How to return page number with the SQLDataReader set ? sql server 2005, asp.net 2.0
Function get_all_events() As SqlDataReader Dim myConnection As New SqlConnection(ConfigurationManager.AppSettings("...........")) Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection) myCommand.CommandType = CommandType.StoredProcedure
Dim parameterState As New SqlParameter("@State", SqlDbType.VarChar, 2) parameterState.Value = Request.Params("State") myCommand.Parameters.Add(parameterState)
Dim parameterPagesize As New SqlParameter("@pagesize", SqlDbType.Int, 4) parameterPagesize.Value = 20 myCommand.Parameters.Add(parameterPagesize)
Dim parameterPagenum As New SqlParameter("@pageNum", SqlDbType.Int, 4) parameterPagenum.Value = pn1.SelectedPage myCommand.Parameters.Add(parameterPagenum)
Dim parameterPageCount As New SqlParameter("@pagecount", SqlDbType.Int, 4) parameterPageCount.Direction = ParameterDirection.ReturnValue myCommand.Parameters.Add(parameterPageCount)
myConnection.Open() 'myCommand.ExecuteReader(CommandBehavior.CloseConnection) 'pages = CType(myCommand.Parameters("@pagecount").Value, Integer) Return myCommand.ExecuteReader(CommandBehavior.CloseConnection) End Function
Variable Pages is global integer.
This is what i am calling DataList1.DataSource = get_all_events() DataList1.DataBind()
How to return records and also the return value of pagecount ? i tried many options, nothing work. Please help !!. I am struck
This is an SPC chart controlled by a slicer that operates a powerpivot table. This is then copied (by cell formula) into a "normal" table where the average, UCL, LCL and Erlang are calculated which are just basic calculations involving average and standard deviation. To make it work the values in the Average, UCL,LCL and Erlang must be repeated all the way down the table to create the chart. In the current format it works well and using a standard table keeps the chart range dynamic.
However this is a very clunky solution involving repeating the data tables in excel. I need to create dozens of charts and it will get large and slow.I would like to create the whole thing in a powerpivot table using measures so i can use powerpivot charts and ultimately powerBI. The data column PasID is a text column so I need a measure that calculates the "count of PasID" for each day(the row labels) and then repeats that value down a whole column in the same way the standard table does. I couldn't figure out how to get the correct number repeat down the whole column, which measures to use,Whether to create calculated columns in the data model or any of it. SO I need to be able to get a count of a text column then display the average of that count in a second column all the way down.
I am trying to get the number of rows for each month.
table name:sitez ID Name crDate access =========================== 1 Bob .. 2014-01-11 .. 1 2 Jerry .. 2014-01-22 .. 2 3 Jim .. 2014-05-06 .. 1 4 Jason .. 2014-12-11 .. 1 5 Jen .. 2014-11-21 .. 3
I am using the results to make a bar graph so I am querying the database for a given year and expecting 12 results back (running SQL 2012).
Select count(*) as ttl FROM sitez WHERE year(crdate) = 2014 and access = 1 group by all month(crdate)
This should return: 1 0 0 0 1 0 0 0 0 0 0 1
However when testing the script I was only getting back: 1 1 1
which didn't knowing which months were 0
By changing the GROUP BY to GROUP BY ALL, it now puts in the zeroes. However I'm still having issues with incorrect results.
When I query the database, the results for December 2014 shows '13' when I execute:
Select count(*) as ttl FROM sitez WHERE year(crdate) = 2014 and access =3 group by all month(crdate)
There are ZERO rows made with a year of 2014 and an access of 3.I verified this by going a straightforward select * from sitez where crdate = 2014 and access = 3
Why I'm seeing ghost results?
All I need is 12 results, ordered by crdate month.
