SQL Server 2014 :: First Day Of Month Value
Nov 3, 2015
I have a table as below.
Timestamp SourceIdChannelIdValue
2015-11-01 16:45:59.95311223,4923
2015-11-01 16:46:00.12712224,9357 *
2015-11-01 16:46:00.32713227,4183 *
2015-11-01 16:46:00.52714221,025 *
2015-11-01 13:17:14.17711223,0304 *
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,
[Code] ....
View 5 Replies
ADVERTISEMENT
Oct 29, 2013
I would like to know how to find fifth workingday* of a given month and year.
workingday - working day is just any day apart from saturday and sunday. no need to consider any other holidays.
ex: I would just give month number and year as input and i would like to know date and day of the fifth working day.
Input : 10/2013
Output : 7th October 2013, Monday.
View 4 Replies
View Related
Dec 19, 2014
Sample Data
SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE CheckRegistry (
CheckNumber smallint,
[code]...
How can I get the result orderd by the month number?
View 3 Replies
View Related
Jan 6, 2015
I am planning to take one full backup and Transactional Log backups for every month ..as i will be making the changes in database only once in a month .
And I am aware of that in case of disaster i need to restore database with all the Transactional Log backups . My Plan is to have Transactional log backups for 5 Years and after 5 years i would be taking a full backup .
So should I need to take any other precautions or concerns with this approach.?
View 9 Replies
View Related
Feb 27, 2015
How to convert 6 digit number to mm/yyyy.
View 5 Replies
View Related
Apr 5, 2008
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.
View 3 Replies
View Related
Apr 22, 2015
following table global_usage
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
ACASC CAL071287 2014-06-01
ACASC CAL071287 2014-08-01
ACASC CAL071288 2014-09-01
ACASC CAL071288 2014-11-01
ACASC CAL071190 2014-12-14
ACASC CAL071286 2015-01-22
ACASC CAL071165 2015-02-01
ACASC CAL071164 2015-03-01
I've tried a few thing like group,having even row_number() but I keep getting wrong results
View 6 Replies
View Related
Nov 9, 2015
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]
[Code] ....
View 6 Replies
View Related
Sep 10, 2014
This is my table and data
CVID | WorkExperience
--------------------------------
2838736
68181101
96568122
1135484
I need to convert into this result
CVID | WorkExperience
--------------------------------
283873 years
681818 years 5 months
9656812 years 2 months
1135484 months
View 5 Replies
View Related
Aug 11, 2015
Most of the data is in one table.
Company 1-Jan 1-Feb 1-Mar 1-Apr
RSP RSP RSP RSP
NON-RELO $295 1 $0 0 $1,400 7 $0 0 $1,195 4 $0 0 $4,700 8 $0 0
AMERICAN ESCROW & CL//AECC $2,650 4 $0 0 $3,720 8 $0 0 $2,339 4 $0 0 $2,460 2 $0 0
American Internation//AIRCO $9,131 30 $2,340 9 $10,927 35 $2,340 9 $9,142 31 $2,600 10 $18,406 54 $3,900 15
American Internation//AIR $20,611 63 $1,820 8 $23,892 75 $1,040 4 $35,038 111 $3,120 12 $3,778 16 $1,560 6
American Internation//Ab $64,248 206 $6,240 24 $59,800 187 $5,200 20 $87,115 264
I did something similar doing just record counts but this is far more complicated. I'm at a loss that this is even possible.
SUM(CASE datepart(month, tbFile.openedDate) WHEN 1 THEN 1 ELSE 0 END) AS 'January',
View 2 Replies
View Related
Aug 2, 2002
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
View 1 Replies
View Related
Jan 9, 2014
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
View 10 Replies
View Related
Mar 3, 2004
Hello,
Is there functions which returns the first and last date of a month in SQL server 2000 ?
View 4 Replies
View Related
Jul 29, 2015
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
View 9 Replies
View Related
Feb 7, 2007
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?
View 2 Replies
View Related
May 1, 2014
I would like to get the second last work day of the month.
View 8 Replies
View Related
Jun 3, 2014
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?
View 3 Replies
View Related
Mar 2, 2005
In SQL SERVER 2000,I had built a function that retrieved absolute day, month, and year between two dates.
View 6 Replies
View Related
Jan 17, 2014
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')
View 8 Replies
View Related
Mar 5, 2015
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
MonthYear CCG AttendedCity CCG DNACity CCG Cancelled
Oct2014010
Jan2015000
Jan2015000
Jan2015100
Feb2015000
Mar2015210
May2015010
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],
[Code] ....
View 2 Replies
View Related
Jul 2, 2015
I am having one requirement where I want to show only first Friday of every month of 2014,2015 and 2016 year.
View 9 Replies
View Related
Jun 17, 2008
--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
View 3 Replies
View Related
Sep 22, 2014
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?
View 1 Replies
View Related
Jan 23, 2015
How to retrieve only Monday dates from each month in sql server.
If i pass any value (Let say GETDATE()) then i should get all monday values from the current month.
I want to calculate bi weekly range data in the sql.
View 9 Replies
View Related
Mar 18, 2015
This is what I have for a query but i would like to take the year off. is there a way i can just have the month and day without the year attached?
View 3 Replies
View Related
Mar 20, 2015
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.
View 1 Replies
View Related
May 12, 2015
How to convert a date to the format as Month name and year(2 digits).
For e.g.- Jan 14, Feb 14......Mar 15
View 7 Replies
View Related
May 15, 2015
How I want to see the below dates via 6 different Select Statements but I am unsure how to get this?
01-May-2015
01-Apr-2015
01-Mar-2015
01-Feb-2015
01-Jan-2015
01-Dec-2014
I have this Select statement
select replace(convert(char(11),getdate(),113),' ','-')
But it is returning the 15-May-2015 and it should be 01-May-2015 for this select statement
View 4 Replies
View Related
May 19, 2015
I need some with selecting the number of days, in a month, between a date range. For example, my data looks like:
FileNumb | startdate | enddate
1 04/25/2015 05/02/2015
2 05/01/2015 05/10/2015
The output I am looking for would be:
FileNumb | Year | Month | Days
1 2015 4 6
1 2015 5 2
2 2015 5 10
View 9 Replies
View Related
Jul 6, 2015
Is there anyway I can find or make a report if a databases has been accessed/used since last month?
View 4 Replies
View Related
Sep 13, 2015
I have the data in the below format.
Month(YYYYMM) | Department | TotalCount | LeftCount
201401 xxxxxx 30 0
201402 xxxxxx 28 2
201406 xxxxxx 27 1
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.
Month(YYYYMM) | Department | TotalCount | LeftCount
201401 xxxxxx 30 0
201402 xxxxxx 28 2
201403 xxxxxx 28 0
201404 xxxxxx 28 0
201405 xxxxxx 28 0
201406 xxxxxx 27 1
View 6 Replies
View Related
Sep 10, 2007
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?
Thanks in advance.
View 3 Replies
View Related
Jan 31, 2014
I have a table with Month , Year as varchar. I realized it was a big mistake. Since its getting too complicated to query this way.
Year Month Productname
2013 11 ACB
2013 11 CDE
I would now like to add another column called date and store these Year Month as a date to my existing table
Year Month ProductName Date
2013 11 ACB 2013-11-01
2013 11 CDE 2013-11-01
Is there a way I can do it for all the columns of the existing table ??
View 3 Replies
View Related
