SQL Server - Get Records From The Last X Days

Oct 25, 2006

Hello,

I am writing a SQL query to count records in a table from the last "X" number of days.

There is a dateTime column that I want to base this off of:
Lets say..  I want to count the records that were added in the last 3 days.  What is the query I should use to get this value?

Here is what I have and it seems to not be working correctly:

SELECT @TotalListedLast3Days = COUNT (*) FROM myTable WHERE DATEDIFF(Day, DateAdded, getdate() ) <= 3

Can someone help so I am able to get the count of records added in the last "X" days?

Thank you very much.

View 2 Replies


ADVERTISEMENT

SQL Server - Select Records Added Today, Last 3 Days, 7 Days...

Oct 25, 2006

Hello,I am writing a query to select records added to a table today, in the last 3 days, in the last 7 days, and so on.Here is what I have (which seems that its not working exactly).   -- total listed today
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 0-- total listed yesterday
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 1-- total listed in the last 3 days
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 3I'd like to be able to select the count for records added within the last X number of days. Can someone please help me out?  Thanks so much in advance.

View 1 Replies View Related

Records That Are 90 Days Old

Feb 3, 2006

How can I select records from a schedule table that have end dates older than 90 days?

Can I do getdate() -90?


Code:

select *
from dbo.schedule
where enddate <> endate-(90 days?)

View 1 Replies View Related

Get All Records From Last Two Days?

Apr 18, 2006

Hi Everyone,I use the following to get records from the last two days in MySql:where date_entered <= curdate() and date_entered >=DATE_SUB(curdate(),INTERVAL 2 day)I'm looking to do the same in MS-Sql server but I'm just not getting it.I've got this so far which does not work:where hit_date <= GETDATE() and hit_date >= DATE_SUB(GETDATE(),INTERVAL 2day)then I tried this:WHERE hit_date >= DATEDIFF(GETDATE(), (GETDATE()-2)>Essentially, I need all records from the last two days.Any help or guidance in this matter would be greatly appreciated.-JohnyB

View 5 Replies View Related

Records Greater Than 30 Days

Sep 10, 2014

I have a query pulling all records with a disconnect date and a transaction date. However, I would like to retrieve any records that have a transaction date greater than 30 days from the disconnect date. I have been unable to figure out the correct formula to use. I think I need to use the datediff function in SQL, but I've never really used this function before.

View 2 Replies View Related

Select Records From Past 30 Days

Jul 21, 2004

What would my statement look like if I have a column in a table (SoftwareInstall) called InstalledOn which stores a date in shortDateString format and I want to select all the records where that date is <= 30 days previous to today's date. Any ideas?

View 1 Replies View Related

Job To Truncate Database Records More Than 15 Days Old

Jan 26, 2005

I have a SQL Server 2K db with some 10 tables and I want to setup a nightly job to truncate all db records which are more than 15 days old. Can anyone provide me with steps involved? Any help will be highly appreciated.

View 3 Replies View Related

Find Records For X Previous Days

Jul 13, 2005

On a webform, I have three button ... [7 days]  [15 days]  [30 days]When the user clicks one of the buttons, I want to return their orders for the past X days. The WHERE clause would include something like this (for 7 days):WHERE (Order_Date BETWEEN CONVERT(DATETIME, GETDATE() - 7, 102) AND CONVERT(DATETIME, GETDATE(), 102))How do I parameterize the number of days?Thanks,Tim

View 2 Replies View Related

Delete Records Older Than 5 Days .

Aug 20, 2002

Hello ,

I am little confused in writing the exact query i.e filling the correct details in the query .
To simplify let me explain....

I am using the query as

delete MYTABLE
where datediff(dd,loaddate,getdate())>5

I have a table now with loadate column which gets the default date and time . The loadate shows correct date and time when the data was imported in the table .

Now suppose if i want to delete previous 5 days records from today
( for e.g today is 20/08/2002 3:40:00 PM ) ideally it should delete all records which are 5 days older from today . i.e from 20/08/2002 3:40:00 PM to 15/08/2002 3:40:00 PM )
But when i execute the datediff command , it deletes the records previous than 15/08/2002 till 15/08/2002.
The records from 15/08/2002 to 20/08/2002 remain intact .

I am getting some different results .

Am i missing something in the query or i am confused about the calculation of the dates the datediff command performs .

Is the logic correct or i am missing someting important ?

Thanks and Regards
Admin001

View 1 Replies View Related

Returning Records Within Days Range

Oct 31, 2013

This is a follow on from one of my earlier threads where I was trying to return one specific record. In this case I am trying to return multiple records for the same person ID and within a number of days range. Snapshot of my date with same PersonID:

PersonID Arrival_Date Leaving_Date ArrivalID
======== ============ ============ =========
123456 01/12/2012 01/12/2013 arr_56464
123456 10/12/2012 10/12/2013 arr_56474
123456 13/12/2012 13/12/2013 arr_56494

And from this I want to check if one record's leaving date of the record is within 7 days of another record's arrival date. I also want to return the record that had a leaving date within 7 days of the next arrival date.I understand that if I self join on personID with the data above I will get 9 rows, for each row I will get 3 matches, I am using INNER JOIN to join to the same table but with a different alias so I assume this is the self join I should be using.

But then how do I process this? I would want to say for record 1 check the leaving date is within 7 days of arrival date of any other record matching that PersonId but not ArrivalID, and return both records.From my snapahot of code I would eventually want to return:

PersonID Arrival_Date Leaving_Date ArrivalID
======== ============ ============ =========
123456 10/12/2012 10/12/2013 arr_56474
123456 13/12/2012 13/12/2013 arr_56494

But can't seem to get this using a self join query like this:

select a.PersonID, a.Leaving_date,a.Arrival_Date,a.arrivalID
from arrivals a INNER JOIN arrivals b
ON b.personID = a.personID
WHERE
a.arrivalid != b.arrivalid
and DATEDIFF(DD, b.[Leaving_date], a.[Arrival_Date]) <=7
and DATEDIFF(DD, b.[Leaving_date], a.[Arrival_Date]) >=0

View 4 Replies View Related

Sql Query To Return Records That Are Only 45 Days

Dec 1, 2007

The topic pretty much sums up my question.

What would the sql query be to return only all the records in a table that are 45 days old from today.

Thank you for any help in this matter

Al

View 5 Replies View Related

Getting Records That 7 Days Pass Today's Date

Feb 26, 2008

I want to get all records that are 7 days pass today's date and not equal to today's date.  Don't know how to write it so I can get records 7 days old but with this procedure I'm still getting records that are due today.  Hope this makes sense.  Can someone assist me.
select * from libraryrequestwhere  duedate > getdate() and duedate != getdate()

View 5 Replies View Related

Delete Records In A Table Older Than 90 Days . Help

Aug 5, 2002

Hi ,

I have a scheduled job which does an text file import in my database . The data gets appended in my table every day from this import job .

Since my table is growing every day , i want to truncate the table after the data has been collected for three months i.e 90 days . The table will be empty and the new data will flow in through the import .

Any thoughts how to do it through query and schedule it ???

Thanks

View 1 Replies View Related

Calculate Median Of Difference In Days Between Records

Jan 24, 2006

I have a table of sample data

Samples(sample_no, sample_date..)

I have no idea how to do the following in sql server or if its even possible:

1. Calculate the difference in days between all samples.
2. Select the median result

Any trick to get this done would be really helpful

thanks,

DB

View 3 Replies View Related

Calculate Median Of Difference In Days Between Records

Jan 24, 2006

I have a table of sample data

Samples(sample_no, sample_date..)

I have no idea how to do the following in sql server or if its even possible:

1. Calculate the difference in days between all samples.
2. Select the median result

Any trick to get this done would be really helpful

thanks,

DB

View 10 Replies View Related

Get Count Of Records Grouped By Days And User

Dec 24, 2013

If I have a table like this:

ID User Date
20 22 1-1-2013
21 22 1-1-2013
22 31 1-1-2013
23 22 1-1-2013
24 22 1-2-2013
25 22 1-2-2013
etc...

How can I get the count of records grouped by date and user?

Date 22 31
1-1-2013 3 1
1-2-2013 2 0
etc...

Is this possible?

View 4 Replies View Related

Transact SQL :: Split Records If Total Days Greater To 1

Nov 5, 2015

I want to split the data every employeid wise based on fromdate and todate if totaldays>1.

sample output specified below ....but same output required for allthe empid's

create table attendence(EmployeeID nvarchar(20),[From] datetime,[To] datetime,TotalDays float)
insert into attendence values('1417','2015-11-02 22:48:49.450','2015-11-04 22:48:49.450',3)
insert into attendence values('1418','2015-11-04 22:48:49.450','2015-11-04 22:48:49.450',1)
insert into attendence values('1419','2015-11-03 22:48:49.450','2015-11-04 22:48:49.450',2)
insert into attendence values('1420','2015-11-04 22:48:49.450','2015-11-05 22:48:49.450',2)
insert into attendence values('1421','2015-11-01 22:48:49.450','2015-11-04 22:48:49.450',4)

 OP
 -------------------------
 EmployeeID    [From]                          [To]                              TotalDays
 1417          2015-11-02 22:48:49.450         2015-11-02 22:48:49.450             3
 1417          2015-11-03 22:48:49.450         2015-11-03 22:48:49.450             3
 1417         2015-11-04 22:48:49.450          2015-11-04 22:48:49.450             3

View 3 Replies View Related

Reporting Services :: Select 10 Random Records From Past 30 Days For Each User

May 18, 2015

I have these columns. TicketID, User, Date...I want to select 10 random tickets for each user for the past 30 days. I not sure whether to do this via sql or using VB in the report design.

View 5 Replies View Related

Remove Weekends And Non Working Days When Calculating Days Difference Between Two Dates

Jan 7, 2014

I have an SQL code below which removes weekends and non working days when calculating days difference between two dates:

ce.enquiry_time represents when the enquiry was logged

(DATEDIFF(dd, ce.enquiry_time, getdate()) + 1)
-(DATEDIFF(wk, ce.enquiry_time, getdate()) * 2)
-(CASE WHEN DATENAME(dw, ce.enquiry_time) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, getdate()) = 'Saturday' THEN 1 ELSE 0 END)
-(SELECT COUNT(*) FROM nonworking_day WHERE nonworking_day.nonworking_date >= ce.enquiry_time AND nonworking_day.nonworking_date < dateadd(dd,datediff(dd,0,getdate()),1))

It works but I don't understand how it works it out. I am having issues understanding each coloured piece of code and how it works together.

View 1 Replies View Related

Days In Sql Server

Aug 25, 2005

Is there any way to find out in sql server that whether it is Monday to friday or saturday sunday.I have to write a stored procedure in which the logic shoukld be done only if there is a weekend.if saturday or sunday select * from status_tempPlease let me know if there is any function that is available in sql

View 4 Replies View Related

SQL Server 2008 :: Parent Records Ordering And Display Child Records Next To It?

Sep 7, 2015

declare @table table (
ParentID INT,
ChildID INT,
Value float
)
INSERT INTO @table
SELECT 1,1,1.2

[code]....

This case ParentID - Child 1 ,1 & 2,2 and 3,3 records are called as parent where as null , 1 is child whoose parent is 1 similarly null,2 records are child whoose parent is 2 , .....

Now my requirement is to display parent records with value ascending and display next child records to the corresponding parent and parent records are sorted ascending

--Final output should be

PatentID ChildID VALUE
33 1.12
null3 56.7
null3 43.6
11 1.2
null1 4.8
null1 4.6
22 1.8
null1 1.4

View 2 Replies View Related

Incorrect Syntax After Server Up For A Few Days

Sep 28, 2006

Dear All,

My VB.Net application connects to MSSQL. It is always running fine for a few days, but encounters "incorrect syntax" as following unless the server is restarted.

----
Exception occurred System.Runtime.InteropServices.COMException (0x80040E14): Line 1: incorrect syntax near 'CDO'.
at ADODB.ConnectionClass.Execute (String CommandText, Object& RecordsAffected, Int32 Options)
----

There are a few applications in the server. If certain service is stopped, my program continues to run. So I am sure that the MSSQL connections have been taken up, which causes the error. How to prove it? And is there any way to reserve some DB connections to a particular application only?

Thanks for any hint!

I attach my codes below. Anything wrong with the way that I handled the ADODB?


------------------------------------------------------------
Public Sub SendAllEmails()
Try
Dim cn As ADODB.Connection = openConn()
Dim rs As ADODB._Recordset
Dim rs2 As ADODB._Recordset
sqlstmt = "select * FROM EMAILTABLE"
rs = cn.Execute(sqlstmt)

While Not rs.EOF

Dim MAIL_ADD_USED As String = rs.Fields("MAIL_ADD_USED").Value.ToString

sqlstmt = "select * from NAMETABLE where EMAIL = '" & MAIL_ADD & "'"
rs2 = cn.Execute(sqlstmt)


If Not rs2.EOF And ErrMsg = "" Then

ErrMsg = SendMail(MAIL_ADD, REPORT_TITLE)

If Not ErrMsg Is Nothing And ErrMsg.Equals("Success") Then
MAIL_DATE_SENT = Now.ToString
MAIL_STATUS = "S"

'wait to make sure the email is sent
System.Threading.Thread.Sleep(1000 * 30)
Else
MAIL_STATUS = "F"
End If

End If

rs2.Close()

sqlstmt = "update EMAILTABLE set " & _
" MAIL_STATUS = " & MAIL_STATUS & "," & _
" MAIL_ERRMSG = null" & _
" where EMAIL = " & MAIL_ADD

cn.Execute(sqlstmt)

rs.MoveNext()

End While

rs.Close()
cn.Close()

Catch e As Exception
EventLog1.WriteEntry("Exception: " & e.ToString)
End Try

End Sub
----------------------------------------------------------------------

View 3 Replies View Related

I Want To Transfer ONLY New Records AND Update Any Modified Records From Oracle Into SQL Server Using DTS

Jul 23, 2005

I need a little help here..I want to transfer ONLY new records AND update any modified recordsfrom Oracle into SQL Server using DTS. How should I go about it?a) how do I use global variable to get max date.Where and what DTS task should I use to complete the job? Data DrivenQuery? Transform data task? How ? can u give me samples. Perhaps youcan email me the Demo Package as well.b) so far, what I did was,- I have datemodified field in my Oracle table so that I can comparewith datelastrun of my DTS package to get new records- records in Oracle having datemodified >Max(datelastrun), and transferto SQL Server table.Now, I am stuck as to where should I proceed - how can I transfer theserecords?Hope u can give me some lights. Thank you in advance.

View 2 Replies View Related

SQL Server 2012 :: Last 30 Days Aggregate Query

Dec 1, 2014

I'm trying to write a query that returns last 30 days data and sums the amount by day. However I need to do it for every year not just the current one(I need to go back as far as 2000).

declare @t table (id int identity(1,1), dt datetime, amt MONEY)
insert into @t (dt, amt)
select '2014-11-30 23:39:35.717' as dt, 66 as amt UNION ALL
select '2014-11-30 23:29:16.747' as dt, 5 as amt UNION ALL
select '2014-11-22 23:25:33.780' as dt, 62 as amt UNION ALL

[Code] ....

--expected output
select '2014-11-30' AS dt, 71 AS Amt UNION ALL
select '2014-11-22' AS dt, 62 AS Amt UNION ALL
select '2014-11-20' AS dt, 66 AS Amt UNION ALL
select '2014-11-18' AS dt, 102 AS Amt UNION ALL

[Code] ....

View 8 Replies View Related

SQL Server 2008 :: Calculate Number Of Days

Oct 8, 2015

I have client table which has client_id Eff_from and Eff_to columns.Eff_from and Eff_to are the dates that client is eligible for service. I need to know the average number of days from the day that he became not eligible and new eligibility date .

CLIENT_IDEFF_FREFF_TO
1001 12/24/200712/8/2010
100112/13/20123/26/2013
1001 5/27/20138/2/2013
10019/24/201310/30/2016

for expl days between
12/8/2010 and 12/13/2012
3/26/2013 and 5/27/2013
8/2/2013 and 9/24/2013

then AVG them.

View 3 Replies View Related

SQL Server 2012 :: Return Count By Individual Days

Aug 5, 2014

i am using the followig query :

select count (*) from MEMBERS,dbo.MEMBER_PROFILE
where MEMBER_PROFILE.member_no = members.member_no
AND JOIN_DATE between '07-01-2013 00:01' and '07-31-2013 11:59'
and email <> 'selfbuy_customer@cafepress.com'
and ROOT_FOLDER_NO is not null
and email not like '%bvt.bvt'

This returns the count for the month but I want to see what the total each day was.

View 5 Replies View Related

SQL Server 2012 :: Running Totals For Previous X Days?

May 13, 2015

get the desired results for the following sample data set. I was able to come up with a query that returns the the expected results however only for a given day, so I'd need to union several select statements the get the desired results which is definitely not ideal. I'd like to pass a parameter in (number of days) instead of doing a unions for each select.

DECLARE @T TABLE (Id INT, Category VARCHAR(1), [Date] DATE)
INSERT INTO @T
SELECT 1 AS Id, 'A' AS Category, '2015-5-13' AS ActivationDate UNION ALL
SELECT 1, 'A', NULL UNION ALL
SELECT 1, 'A', '2015-5-13' UNION ALL
SELECT 1, 'A', NULL UNION ALL

[code]....

View 9 Replies View Related

SQL Server 2008 :: Selecting Days In Month Between Two Dates

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

SQL Server Admin 2014 :: CheckDB Running For Two Days

Jun 3, 2015

We carried out an in-place upgrade on our production server on Saturday - going from 2008 R2 to 2014.

We had tested this method out in dev/test and pre-production with only minor post issues to fix.

However, on production we had an issue whereby checkdb was hitting 100% CPU and caused overnight processes to hang. The checkdb statement was terminated and disabled by a colleague at 1 am.

Since then we have restored this database to a dev server and ran checkdb against it with no_infomsgs and all_errormsgs but it still hasn't finished since Monday morning!

The database is just over 800 GB and whilst checkdb was crippling the cpu, logical reads are less than one. However, sp_whoisactive is showing that it has done 56 million reads so far and this number increases periodically so it looks like it keeps going back to re-check the database with a deep dive.

Also, on a different environment, we ran check table statements and one of them took over 9 hours for a single table but came back clean (see attachment).

We need to wait for the output but the database is still in use in production and the mess will just get worse if it is indeed corrupted.

View 5 Replies View Related

SQL Server 2008 :: Can Display All Days In A Range Even With No Data?

Aug 10, 2015

I need to display all the dates within a range even with no data

For example right now my query get the records with the range say...

The range is 7/1/15 thru 7/7/15

I Get...
Joe 7/1/15 xxx
Joe 7/3/15 ccc
Joe 7/5/15 xxx

I want...
Joe 7/1/15 xxx
Joe 7/2/15
Joe 7/3/15 ccc
Joe 7/4/15
Joe 7/5/15 xxx
Joe 7/6/15
Joe 7/7/15

View 9 Replies View Related

SQL Server 2012 :: Using DateDiff To Work As Excel Network Days

Jan 2, 2015

So I am trying to work out the difference between today's date (GETDATE()) and a Target Date in a specific table (targetdate)

When I use the DATEDIFF function it is including non working days in the calculation (weekends and bank holidays). Although our date calandar table provided to us from a third party supplier will tell you the weekends, it does not tell you the bank holidays.

Luckily there is another table in the database called - ih_non_work_days.

The format of the date is "2014-12-25 00:00:00.000" for example in that table.

How do I using my "targetdate" and today's date calculate in days the differance - excluding the dates that exist in the ih_non_work_days database?

So for now my basic script looks like -

SELECT com.comm_reference AS 'Referance'
,com.current_task_target_date AS 'TargetDate'
, DATEDIFF(D,com.current_task_target_date,GETDATE()) AS 'Incorrect Date Calculation'
FROM [dbo].[em_communication] as com

View 2 Replies View Related

SQL Server 2012 :: Add Business Days To A Date Using Calendar Table

May 12, 2015

I have a date that I need to add 'n' number of business days to. I have a calendar table that has a 'IsBusinessDay' flag, so it would be good if I was able to use this to get what I need. I've tried to use the 'LEAD' function in the following way;

SELECT A. Date, B.DatePlus3BusinessDays
FROM TableA A

LEFT JOIN (Select DateKey, LEAD(DateKey,3) OVER (ORDER BY datekey) AS DatePlus3BusinessDays FROM Calendar WHERE IsBusinessDay = 1) B ON A.DateKey = B.DateKey

Problem with this is that because I am filtering the Calendar for business days only, when there is a date that is not a business day in TableA, a NULL is being returned.

Is there any way to do a conditional LEAD, so it skips rows that are not business days? Or do I have do go with a completely different approach?

View 9 Replies View Related

SQL Server 2008 :: Rank Function Taking Care Of Gap Between Days

May 24, 2015

Working on sqlserver 2008 R2

CREATE TABLE OrderRanking
(
OrderID INT IDENTITY(1,1) NOT NULL,
CustomerID INT,
OrderDate date
)
INSERT OrderRanking (CustomerID, OrderDate)

[Code] ...

Looks fine but what I need is DRP with this:

CustomerID OrderDate 'DRP taking care of the gap in the days'
1 '01-01-2015' 1
1 '01-01-2015' 1
2 '02-01-2015' 1
2 '02-01-2015' 1
2 '05-01-2015' 4
2 '05-01-2015' 4

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved