Hi there,
if any body out there has done this before, can you please post the T-SQL script that calculate whatever last months of data based on a Date field.
Thanks very much,
Manny
I would like to calculate difference between end_date and current date in Months.And also how we can calculate the sum of difference in months between start_date and end_date for each ID?
CREATE TABLE datedifference (
id INT
,start_date INT
,end_date INT
)
INSERT INTO datedifference VALUES (10,20091202,20100629)
INSERT INTO datedifference VALUES (20,20071202,20090330)
INSERT INTO datedifference VALUES (30,20051202,20101031)
I want to get the number of days pending for a perticular column. I want the out put as a integer value.
This is what I used for my calculated field.
But when i write this day is underlined in red. It says unrecognized identifier.
=DATEDIFF(Day, GETDATE(),Fields!SubmittedDate.Value)
If I use "day", then it is not underlined but still it does not work.
When I use following query, I get no error message. but still no data in the column too.
=DATEDIFF("d", Fields!SubmittedDate.Value, "GETDATE()" )
Can anyone tell me why is that?
Thanks
I have a start date saved and an end date saved but when I query I only want to return the amount of months that have elapsed between the two, any idea?
View 4 Replies View RelatedI have a database of the table payments
and I want to create another table with
the olds payments (18 months past)
a have a field with the payment date
date_pay.
How I can do that?
Thanks
Luiz Lucasi
Is there an easy way to calculate a sum for the previous three months?
Data
Date,Area,PropertyID,Volume,DaysInMonth
197904,6,888888,9589,30
197905,6,888888,27403,31
197906,6,888888,17130,30
197907,6,888888,14321,31
197908,6,888888,15234,31
[Code] .....
Desired Output
Date,Area,PropertyID,Volume,DaysInMonth,3MonthSum, DaysInMonthSUm
197904,6,888888,9589,30,NULL,NULL
197905,6,888888,27403,31,NULL,NULL
197906,6,888888,17130,30,NULL,NULL
197907,6,888888,14321,31,54122,91
197908,6,888888,15234,31,58854,92
[Code] .....
How to calculate the cumulative of months.
Jan FebMar AprMay JunJul AugSep Oct Nov Dec
321 394571 577 617 692924 944956 1010 1308 1686
if i execute my query in this month(getdate) then it should sum from Jan to Aug Similarly i execute same query it should sum from Jan to Sept So on.
I am working in sqlserver 2008 R2 and below is my sample research query. I am trying to get previous 6 months data.
WITH CutomMonths
AS (
SELECT UPPER(convert(VARCHAR(3), datename(month, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0)))) Month
,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0) startdate
,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N + 1, 0) enddate
FROM (
VALUES (1)
[Code] ...
Current output what i am getting:
Expected Output:
I found why the April month i didn't get the $20 because the startdate of my perks CTE '2014-04-03'. If it is '2014-04-01' then i will get the expected output.
But i should not change the the date on perks. How to neglect this date issue and consider the month instead to get the expected output.
I have a report that has 41 fields one of these fields is the Admit Date. I need to create a field that shows a count of Admits in the past 12 months .
View 6 Replies View RelatedI have a challenge and I'm not sure the best route to go. Consider the following dataset.
I have a table of sales. The table has fields for customer number and date of sale. There are 1 - n records for a customer. What I want is a record per customer that has the customer number and the average number of months between purchases. For example, Customer 12345 has made 5 purchases.
CustomerNumber SalesDate
1234 05/15/2010
1234 10/24/2010
1234 02/20/2011
1234 05/02/2012
1234 12/20/2012
What I want to know is the average number of months between the purchases. And do this for each customer.
I have the following table
FeedBack Type Date
test2 positive 03/15/08
tes3 negative 03/01/08
.. ....
in my page i need to select the number of negative/positive comments within the last
1 month, 6 months, 12 months
How can I accomplish that?
thanks
I embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE" .
How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?
I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)
**all of the above returned text objectes in excel and not date objects.
Hi there.
I'm trying to extract data from my SQL server & everything in the script I've got is working (extracting correct data) except for one field - which is for the most part it's off by +2 days (on a few occasions - I see it off by just +1 day or even +3, but it's usually the +2 days).
I'm told that it's due to the conversion formula - but - since SQL is not my native language, I'm at a bit of a loss.
The DB table has the date field stored as a type: CHAR (as opposed to 'DATE')
Can anyone out there help?
Please advise. Thanks.
Best.
K7
I have a query to run a report where the results has a column named “Due Date” which holds a date value based on the project submission date.Now, I need to add 4 columns named, “45 Days Expectant”, “30 Days Overdue”, “60 Days Overdue” and “90 Days Overdue”.I need to do a calculation based on the “Due Date” and “System (I mean default computer date) Date” that if “System Date” is 45 days+ to “Due Date” than put “Yes” in “45 Days Expectant” row.
Also, if “Due Date” is less than or equal to system date by 30 days, put “Yes” in “30 Days Overdue” and same for the 60 and 90 days.how to write this Case Statement? I have some answers how to do it in SSRS (Report Designer) but I want to get the results using T-SQl.
Hi all,How to get the date from the 3 months ago? So, if user enter a date (10/10/2006), how can I get to 07/10/2006.Thanks in advance...
View 3 Replies View RelatedHi,
I need a select statement with the condition that the date (a timestamp with format yyyy-mm-dd) is either equal to or longer than 14 months from today's date.
I am using SQL Server
Can anyone point me in the right diretion?
Thanks
Hi to all,
I want to make an sql query that will subtract two dates and the difference will be number in months.
i have this table TestTable with Fields ACost, Adate, AMonth
my formula is this X = (Now-Adate), should give me number in months
and Y = (ACost/AMonth)
i want to get the product of X and Y, (X*Y).
how can i make this in sql views?
can anyone help me with this?
thanks a lot!
best regards,
saldiboy
I have a simple query which returns a purchase date plus the number of months warranty an item has. What I need to do is add these months to the purchase date to get a warranty expiry date. What is the best way to do this? My query and results are below, I need an extra column in the results with that calculated warranty date:
select item, purchase_date, warranty_period
from inventory where item like '05MC%'
item purchase_date warranty_period
-------------------- ----------------------- ---------------
05MC0001 2014-07-22 00:00:00 36
05MC0002 2014-07-11 00:00:00 36
What is the correct code for an expression where my "datetime" field is greater then or equal to 3 months ago?
View 1 Replies View RelatedHello
I had a requirement to filter records for the current year only... I went back to the user and explained that the logic wasn't sound... eg if somebody did the search early in January then they would probably get no results back. My suggestion was to go back two months if the current date is in January or february.
So, this is what I have.......
Code Snippet
where cs.startdate > case when year(cs.startdate) = year(getdate()) and month(getdate()) > 2 then
convert(char(4),year(getdate()))+'-01-01' else dateadd(month,-2,getdate()) end
Is there a neater/better way of coding this?
Jon
I am pulling data from table which has date field to it. I am using below query to get the date
select ArrivalDate As[ARRIVE DATE, , Date, 0], ArrivalTime As[ARRIVE TIME, 4, String, 0] from ArrivalInfo
but i am getting date as 20130925 in format but i want date to be in 09/25/2013 (mm/dd/yyyy)..
hi Dear All
How can i import data in SQL Server 2000 which contains the value of date field from 1800 yrs to till now
eg. 15/12/1860, 10/10/1896, 10/10/2006
but if i change the year of date value to 19?? then it's imported.
anybody can help me please!
Thanks in advance
Rahman
Hi all,
Im trying to get a users age from their date of birth which is stored in my table, and although I can get the age I cant actually use it in a where clause as it keeps stating invalid column name age. Below is a much reduced version of my code that shows my problem in a clearer way (the where clause is for example purposes as I dont intend to use it like this but it does show my problem):
SELECT DATEDIFF(YY, '8/5/1971', GETDATE()) - CASE WHEN (MONTH('8/5/1971') = MONTH(GETDATE()) AND DAY('8/5/1971') > DAY(GETDATE()) OR MONTH(GETDATE()) > MONTH('8/5/1971')) THEN 1 ELSE 0 END AS Age
WHERE Age = 35
Now this does get the persons correct age for the time of year and works fine until I include the WHERE clause, that is when I get the error. You can see that I have alias the returned column with the name Age but I keep getting the invalid column name Age error. What is wrong and how can I sort it out?
Now I did manage to resolve this problem using a temporary table (full actual code below) but Im certain my resolution cant be the best or easiest way to do this. Please help! Thanks
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROC [dbo].[sc_NewlyJoinedPeopleSearch]
@UserID uniqueidentifier,
@Days tinyint
AS
BEGIN
DECLARE @AgeFrom tinyint
DECLARE @AgeTo tinyint
DECLARE @DateToday DateTime
SET @AgeFrom = (SELECT AgeFrom FROM Users WHERE UserID = @UserID)
SET @AgeTo = (SELECT AgeTo FROM Users WHERE UserID = @UserID)
SET @DateToday = GETDATE()
SELECT UserID, DATEDIFF (YY, DateOfBirth, @DateToday) - CASE WHEN (MONTH(DateOfBirth)=MONTH(@DateToday) AND DAY(DateOfBirth) > DAY(@DateToday) OR MONTH (DateOfBirth) > MONTH (@DateToday)) THEN 1 ELSE 0 END AS UsersAge
INTO #Age
FROM Users
SELECT Users.UserID, UserName, AgeFrom, AgeTo
FROM Users JOIN #Age ON #Age.UserID = Users.UserID JOIN aspnet_Membership ON Users.UserID = aspnet_Membership.UserID
WHERE UsersAge > 30
ORDER BY CreateDate desc, Users.TimeStampUsers desc
DROP TABLE #Age
END
I need to send an email when my 'LastRunDate' field is 30 days old (i.e. It should send an email if the LastRunField = 7/21/2007).
I would need to include the matching fields in the database (i.e. MachineID, Description, etc.) then update that field to todays date.
I have a few values in the 'Frequency' field such as Daily, Monthly, Yearly. Daily would be 24 hrs, monthly 30 days, yearly 365 days from the lastrundate.
I am new to T-SQL & need a good p[lace to start.
Any sugesstions.
Thanks.
I need to find out All mondays / Tuesdays etc dates for next 6 months from the current date.
I have a table called DayOfWeek and the records are
1Sun
2Mon
3Tue
4Wed
5Thu
6Fri
7Sat
so if I pass "1" to the stored procedure, I need to find all Sunday dates for the next 6 months
And so if I pass "2" to the stored procedure, I need to find all Monday dates for the next 6 months...etc
I would like to display Car records to retrieve last Maintenance request for all customer who did not make request in past 9 months from current date.
MC refers to Maintenance.
MCLog data:
carId: SGY12345 (repeated)
mcDate: 2010-01-30
mcDate: 2012-03-30
carId: SGX55661 (repeated)
mcDate: 2015-05-30
mcDate: 2015-06-15
[Code] .....
Here is my Table:
CREATE TABLE Car (
carId Char (20) NOT NULL,
model Char (20) NOT NULL,
importDate smalldatetime NOT NULL,
custId Varchar (50) NOT NULL,
CONSTRAINT Car_PK PRIMARY KEY (carId));
[code]....
Hi,
I have a flat file that has a date column where the date fields look like 20070626, for example. No quotes.
The problem is that several of the date values are missing, and instead of the date value the field looks like this , ,
That is, there are several blank spaces where the date should be. The number of blank spaces between the commas doesn't appear to be a set number (and it could even be 8 blank spaces, I don't know, in which case I don't know if checking for the Len will produce the correct results, but that's another issue...)
So, similar to the numeric field blanks problem, I wrote a script to convert the field to null. This is the logic I used:
If Not Len(Row.TradeDate) = 8 Then
Row.TradeDate_IsNull = True
End If
The next step in my data flow after the script is a derived column where I convert TradeDate from 20070625 to 06/25/2007. So the exact error message I am receiving is this:
[OLE DB Destination [547]] Error: There was an error with input column "TradeDate - derived" (645) on input "OLE DB Destination Input" (560). The column status returned was: "The value could not be converted because of a potential loss of data.".
Do I need to add a conditional split after the script and BEFORE the derived column to redirect bad rows so they don't go to the derived column?
What am I doing wrong here?
Thanks
I am a student, and I am so confused by SQL code that calculates incomes of my contract in a year or each month of year.
I have 3 tables:
lodgings_Contract:
id_contract indentity primary,
id_person int,
id_room varchar(4),
day_begin datetime,
day_end datetime,
day_register datetime
money_per_month money
electric:
id_electric indentity primary key,
id_room varchar(4),
number_first int,
number_last int,
Sum_Number int,
money_electric money,
status bit
Water:
id_Water indentity primary key,
id_room varchar(4),
number_first int,
number_last int,
Sum_Number int,
money_water money,
status bit
Now what I want to do are statistics on how much money I got in a year or month. Here is my code to calculate incomes of year.
Select Year(day_register) as 'Year'
, Sum(money_per_month * month(day_end-day_register)) + sum(b.money_electric+c.money_water) as 'Incomes'
From lodgings_Contract a
, electric b
, Water c
Where a.id_room = b.id_room
And a.id_room = c.id_room
And b.status = 1
And c.status = 1
Group by Year(day_register)
Hello:
I have a field hold varchar. I would like to calculate how many character in the field when user enter the data and save in the database.
Is there any way to do?
Thanks,
Snow
How can i do the following:
Calculate the size of a varbinary(max) field or variable
Calculate the average of a varbinary(max) table column
I am using SQL 2005
Thanks for your posting
I've SSRS sales report to which I need to pass the dates for previous month's start date and end date which I am able to pass using below code. However, since the sales report has data from the past year(2014) I need to pass the dates for last year as well. The below code gives StartDate1 as 2015-02-01 and EndDate1 as 2015-02-28. I need to get the dates for past year like 2014-02-01 as StartDate2 and 2014-02-28 as EndDate2
SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000201', GETDATE()), '19000101') AS StartDate1,
DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '18991231') AS EndDate1
I need to create a date dimension where the lowest level is month. I've seen examples which use the list function such as
Source = List.Dates(#date(2000, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(2000,1,1)), #duration(1,0,0,0)),
The above increments by 1 day which is defined in the 1st argument of the #duration. My question is how can I dynamically change the value of this 1st argument such that its the number of days in the current month hence it will increment to only return the 1st date in the Month e.g
1/1/2000
1/2/2000
1/3/2000
etc..
I prefer to use an elegant approach if possible, the alternative would be return all dates, create a custom column from these dates which returns the month date - delete the dates column - get a distinct list of the month dates.
Hi All!
I need a query to find all dates from today to one-year back.
If I start from today day I need find all dates until 11/12/98.
Thanks a lot.
Greg.