I have officialy exausted my google searching. I have the following Table of Data (Entire Month):
Date ,Server Name,Min CPU %,Avg CPU %,Max CPU %
01/01/2014,Server A ,10 ,7 ,15
01/01/2014,Server B ,43 ,25 ,66
01/01/2014,Server C ,45 ,48 ,67
02/01/2014,Server A ,58 ,5 ,98
02/01/2014,Server B ,22 ,29 ,80
02/01/2014,Server C ,33 ,42 ,67
What I need is output like the below:
Month ,Server Name, Min CPU%, Avg CPU %, Max CPU %
JANUARY ,Server A , 10 , 6 , 98
JANUARY ,Server B , 22 , 27 , 80
JANUARY ,Server C , 33 , 45 , 67
Important thing being, I want the MIN, AVG, MAX for Entire Month, by Server Name
Hello what I'd like to display the following in a matrix report:
Parameter selected: 3 (March), 2008 (Year)
Monthly TO Summed up ArtNo March <=March 1210 20,500 50,900 1220 21,200 64,000 1230 15,400 40,300 ... ... ...
So, in the rows I have the articles and in the column the selected month via parameter. In another column I need to sum up all monthly values up to the selected month, meaning in this example the sum of jan, feb and mar per article.
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
I have two tables Costtable (Id,ResourceId, Amount,Date) and ResourceTable (ResourceId,Name) which shows output as below.
I want to show 0 amount for rest of the name in case of September. For e.g. if rest of the Resources does not appear in cost table they should appear 0 in amount
My Desired output
My current query
SELECT RG.Id AS Id, RG.Name AS Name, ISNULL(SUM(AC.Amount), 0) AS Amount, RIGHT(CONVERT(varchar(10), AC.[Date], 105), 7) AS [YearMonth]
Does anyone know how I can get last day of month if I pass a function a given month and and given year. @Month = 2 @Year = 2004 The result I would need is 29 because there are 29 in the month of February in the 2004. Any help on this is greatly appreciated. Kellie
I only want the first value per channel in a new table ( the values marked with *)
I tried with sql below without succes.
set nocount on; declare @today date; set @today = CURRENT_TIMESTAMP; Select * into new_table from ( select @today as 'Now', dateadd(day, -(day(@today)) + 1, @today) as 'FirstOfMonth, Timestamp, SourceId,
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select Jan 1- 23rd feb 1-23rd march 1-23rd april 1-23rd ,value from table
Hi, I have done a quick search for a similar topic, but I don;t think they are what I need. What I want to do is (when creating a view in the database) I want to filter entries in my table that have only been created this month (the field I am looking at is called DateDB), I'm not sure how to do this, I have tried =MONTH(GETDATE()), but this doesn't return any values at all. Is there a way of doing this?
I am trying to add month to a date. Here is my code
declare @CollectionDate date='10-28-2014' select @CollectionDate ;WITH CTemp AS ( SELECT TransactionDate=CAST(@CollectionDate AS DATE) ,RemainingTransaction=1 UNION all
[Code] ....
It is working fine. But when I am giving date '10-30-2014' it shows me the error
Msg 241, Level 16, State 1, Line 3 Conversion failed when converting date and/or time from character string.
I can understand the problem that it is for the month of February. But How do I overcome the situation?
I'm trying to find the most succinct way to get the last occurrence of April 1st given a date.
At the moment I'm using this:
DECLARE @Date DATE = '20131217' SELECT CONVERT(DATE, CAST(DATEPART(YEAR, IIF( --If we're at the start of a year --we'll need to go back a year DATEPART(MONTH, @Date) IN (1,2,3), DATEADD(YEAR, - 1, @Date), @Date )) AS VARCHAR(4)) + '0401')
I have the query below which produces a succesful output but as there is more than one course date the month appears for example three times where there are three courses in Jan as the example output below how can I change the query to group these
SQL QUERY SELECT CONVERT(char(3), dbo.tblCourses.CourseDate, 0) AS Month, YEAR(dbo.tblCourses.CourseDate) AS Year, SUM(CASE WHEN a.AttendanceStatus IN (9) THEN 1 ELSE 0 END) AS [City CCG Attended], SUM(CASE WHEN a.AttendanceStatus IN (3) THEN 1 ELSE 0 END) AS [City CCG DNA],
--find day,month,year --for day select datediff(d,'01 may 2008',getdate()) -- --for month select datediff(m,'01 jun 2006',getdate()) -- --for year select datediff(year,'01 jun 2006',getdate()) above working fine but suppose difference is 1 year 4 month and 2 month 15 days then It's giving 1 year and 2 month respectively. but I want completely so I can use this in case of expired user in my project. User can be expired in 1 month,3 month and 1 year. So I'm not able to recognize. thanks
What's the best way to calculate a customers age and value by month and year?
I need to be able to calculate customer value by month and year, and then to calculate their age at each month in time. I've found a way of grouping sales by month and year that includes age for a particular contact like this:
select fh.contact_number , concat(year(fh.transaction_date), '-', month(fh.transaction_date)) as transaction_month_year , cast(fh.transaction_date as date) as transaction_date , sum(fh.amount) as ttl_amount_in_month
[Code] .....
It seems to work, but any better way to achieve this?
We had a requirement that need to sum the data based on quater we will be having 12 months data in the system for an year suppose we have 12 records for 2014 year. jan month sales data should be same when we were in feb month it should sum jan+feb sales and should show in sales column whereas we were in march month it should sum jan+feb+mar sales, then same for next quater also apr month it wil be same value in may it should be apr+may in may sales value etc ....
We will be having date column values as 201401,201402,.....
How can we implement in sql sever performance should be good.
In the above data, no record exist for 201403,201404,201405, query I wrote will give only the data for which there LeftCount exists, but I am looking for a query which get the data in the below format.
Hi I trying to find a way to determine the number of working days per month starting from the current date to the last day of the current month.And within the same store procedure determine the number of working days as normal (each month is independent from the next). For example: The store procedure is executed
September: @CurrentDate = 9/10/2007 @EndDate = last working day 9/30/2007
Total# of working days = 15
October: @CurrentDate = 10/1/2007 @EndDate = last working day 10/31/2007
Total# of working days = 23
November: @CurrentDate = 11/1/2007 @EndDate = last working day 11/30/2007
Total# of working days = 22 etc. Any ideas of how i can approch this?
I need to build a report that compares a count on a certain day of the week by month by year by stacks. That is,for first Monday in October 2012 against first Monday in October 2013 for stack DM1 against first Monday in October 2014 stack DM1, same for second Monday, first Tuesday, second Tuesday, ect. Attached is a sample dataset and what I want to achieve.
