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


ADVERTISEMENT

Returning A Specified Range Of Records

Jul 14, 2005

Hi,

I have a SQL question which I suspect is very easy to answer but can't seem to find one for.

I have a table which contains about 500 records. i would like to display these records on a web page, but paginated, showing only 20 records per page. I have in the past returned a recordset containing all the records and paginated programmatically in ASP. In this instance I would like to be able to pass an upper and lower bound into my stored proc and return only those records I want to display. So on page 4 I would want to display only records 61-80. I can pass in the upper and lower bound to the SP as parameters but is there some T-SQL i can use to return this range of records only.

So, the SP would for example accept the parameters

@Upperbound = 80
@Lowerbound = 61

and would return a recordset of 20 records being the 61st thru 80th record from the queried table.

Thanks for the help
Guy

PS. I asked someone at work and they suggested using OFFSET and LIMIT. It seems to me as if these are only available in PostgreSQL or MySQL and not T-SQL. I guess I am looking for an equivalent I can use with SQL Server.

View 4 Replies View Related

Returning Range Of Records In MSSQL

Jan 25, 2005

Hi guys, I need to know if there is a way to select a range of records from a database. Kind of like using SELECT TOP 1000, but I need to be able to specify which records to return. So I imagine it would look like this:



SELECT TOP 2000-5000 * FROM customers WHERE groupid=2 ORDER BY FirstName DESC



Where this statement would return only records 2000 to 5000 of the returned results.

View 2 Replies View Related

Returning Range Of Records From SQL Server 7 - Old Issue Revisited

Oct 19, 2006

Hi all,I am using the following stored procedure in SQL 7 to return a range of records.CREATE PROCEDURE spRetMyTable@maxRows int, @lastRecord int, @SortPhrase varchar(122)ASbeginDECLARE @stSql VARCHAR(255) SET ROWCOUNT @maxRowsSET @stSql = 'SELECT * FROM (SELECT TOP 10 colPrimary, col1, col2, col3 FROM (SELECT TOP ' + CONVERT(VARCHAR, @lastRecord) +     'colPrimary, col1, col2, col3 FROM [MyTable] ORDER BY [colPrimary] ASC) AS tbl1 ORDER BY [colPrimary] DESC) AS tbl2 ORDER by [colPrimary] ASC'exec(@stSql)set rowcount 0endTo execute the above procedure I am issuing the command:execute spRetMyTable @maxRows 10, @lastRecord 1500The column [colPrimary] is the primary field in the table.I will be using the selected records in a gridView with paging (3 records per page). So if the next page is selected I would require to take out record 1491 to record 1494 and Add record 1501 to record 1503 in the datatable subject to the availability of records in the table MyTable.How do I provide sorting by col1, col2 or col3? It could be single column or multiple column sorting as chosen by the user during runtime.Is there any performance overhead in the above method in the first place?Could it be improved by any alternative process?I need YOUR ASSISTANCE to cater to the above issues in a feasible way.Thanks in advance. 

View 2 Replies View Related

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

Date Range Only Including Work Days -HELP!!

May 23, 2001

I've been fighting with this for a few days now, hopefully it's a simple answer.

I need to take a daterange between two dates and output the number of work days in that range.

I wrote this but it doesn't work. I know how to write a query that tells me if a date is a word day or not but not to actually determine a date range.

SELECT in_date,out_date,DATEDIFF((weekday, in_date, out_date) in (1,2,3,4,5)) AS no_of_days
from <table>
where ikey = 'whatever'

I am in desperate need of help as my deadline is coming up soon.

View 7 Replies View Related

T-SQL (SS2K8) :: Hot To Get Number Of Days For Given Date Range

Sep 15, 2014

--From the rows I want to know how many number of days a person was active for the given date range.

create table [dbo].[personstatus]
(
id int identity(1,1),
name varchar(100),
DateAdded date,
InactivationDate date ) ;
insert into [dbo].[personstatus] values

[Code] ....

--The output I am looking for.
/*
1) FromDt = '2014-01-01' ToDt ='2014-01-30'
KRISS = 7
VDENTI = 7 days

2) FromDt = '2013-01-01' ToDt ='2014-01-01'
KRISS = 1
VDENTI = 1 days

3) FromDt = '2013-01-01' ToDt ='2014-01-01'
KRISS = 0
VDENTI = 1 days

4) FromDt = '2013-01-01' ToDt ='2014-12-31'

KRISS = 8+24+8+21 = 61 Days
VDENTI = 31+24+31+30 = 116 Days
*/

View 9 Replies View Related

Finding Last Saturday Date Then Range Last 28 Days

Jul 18, 2014

In my query I have a date field and using that date field I want to pass the date to a function, or whatever is most appropriate, then compare that date with a range of the last 4 weeks, which will be numbered 1-4, 1 being earliest and 4 being latest, and then return the week number.

I've determined I need some sort of look up but will need to be dynamic - thinking maybe a temp table, first using the date the report is run on and counting 28 days back from the most recent last saturday then setting number to 1 for first 7 days then 2 for next 7 days etc.

View 8 Replies View Related

Convert Date Range Into Week Days

Nov 3, 2006

Hi,

I don't know if this can be done, but hopefully one of the experts in this forum cna help me out.

I have a TimeOff table which contains 5 fields: timeoffID, employeename, startdate, enddate, and timofftype. I need to create a report which shows how many people take sick day (one of the timeofftypes) on each week day (i.e. Monday, Tuesday, Wednesday, and etc.) The problem is that I don't know how to convert the date range (from startdate to enddate) into indvidual week day for each timeoff record.

For example, on the table,

TimeoffID employeename startdate enddate timeofftype
1 Andy 11/02/2006 11/03/2006 Sick Day
2 Bill 11/03/2006 11/03/2006 Sick Day

The report needs to show:

Monday Tuesday Wednesday Thursday Friday Saturday Sunday
0 0 0 1 2 0 0


Thank you in advance!

View 4 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

Analysis :: Range Function - How To Get Last 60 Days Before A Month Starts

Apr 20, 2015

I am trying to get the last 60 days before a month starts. I have a set that is returned from the query below :

SELECT
   non empty
   [Measures].[TRANSACTIONS Count] on 0,
   non empty ([TRANSACTIONS].[Days].[Days],                                                          
                                     [TRANSACTIONS].[Transaction Month].[Transaction Month])  on 1 from [cube]   

I can get the cummulative count of last 60 days before month 2 by hardcoding the day of transaction of start of month like below :

WITH MEMBER
 [Measures].[Cumm Account Count]
AS
(
    AGGREGATE( [TRANSACTIONS].[Days].CurrentMember:NULL ,[Measures].[TRANSACTIONS Count])
)

 SELECT
   non empty [Measures].[Cumm Account Count] on 0,
   non empty [TRANSACTIONS].[Days].&[3]:[TRANSACTIONS].[Days].&[3].lead(60) on 1 from  [cube];
 
and for subsequent months by using the dates that the following month starts. How can I achieve the above result without having to use the day numbers, I tried to use the tail function (to get the months and star date) but it wont work because the range function  accepts members only...

View 4 Replies View Related

Returning Time Difference As Days/ Hours/ Minutes

Sep 16, 2005

I have written a function that returns the number of Days, Hours and minutes from a given number of minutes. On testinf the results are close but not quite there. Can anyone see where I have gone wrong or is there an easier way of doing this? Code is as follows:

CREATE FUNCTION dbo.GetTimeBetweenLong
(@StartTime DateTime, @EndTime DateTime, @CurrentDate DateTime)
RETURNS VarChar(50) AS
BEGIN
DECLARE @TotalTime Numeric
DECLARE @Minutes Numeric
DECLARE @Hours Numeric
DECLARE @Days Numeric
DECLARE @MinutesInDays Numeric

IF @EndTime IS NULL
BEGIN
SET @Days = DATEDIFF(Day, @StartTime, @CurrentDate)
SET @Hours = DATEDIFF(Hour, @StartTime, @CurrentDate) - (@Days * 24)
SET @Minutes = DATEDIFF(Minute, @StartTime, @CurrentDate) - ((@Days * 24)*60) - (@Hours * 60)
END
ELSE
BEGIN
SET @Days = DATEDIFF(Day, @StartTime, @EndTime)
SET @Hours = DATEDIFF(Hour, @StartTime, @EndTime) - (@Days * 24)
SET @Minutes = DATEDIFF(Minute, @StartTime, @EndTime) - ((@Days * 24)*60) - (@Hours * 60)
END

IF(@Days <0)
BEGIN
SET @Days = @Days - @Days - @Days
END

IF (@Hours < 0)
BEGIN
SET @Hours = @Hours - @Hours - @Hours
END

IF (@Minutes <0)
BEGIN
SET @Minutes = @Minutes - @Minutes - @Minutes
END

RETURN CONVERT(nVarChar(10),@Days) + ' Days, ' + CONVERT(nVarChar(5), @Hours) + ' Hours, ' + CONVERT(nVarCHar(5), @Minutes) + ' Mins'

END

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

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() ) <= 3Can 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 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

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

Records Within Particular Date Range

Jun 11, 2014

I have to find the records with in a paricular dates(from date and to date). In some cases @FromDate or @ToDate could be null. in the following query, when i am passing values @FromDate and @ToDate and execute the SP i am getting records which are not in the given range.

SELECT * FROM TABLE P WHERE
CONVERT(VARCHAR(10), P.[FromDate], 101) BETWEEN
CASE When @FromDate IS NULL Then CONVERT(VARCHAR(10),@minFromDate, 101)
ELSE CONVERT(VARCHAR(10), @FromDate, 101) END
AND CASE WHEN @ToDate IS NULL Then CONVERT(VARCHAR(10), @maxToDate, 101)

[Code] .....

View 4 Replies View Related

Select Records Within A Range...

Dec 14, 2007

I currently use this query to select 4000 contacts from our database:

SELECT TOP 4000 * FROM wce_contact
where website not like ''
and expressemail like '%@%'
and idstatus like ''
and salutation not like '%there%'
and task not like '%x%'

So I use this query to send out emails, then I want the next set of contacts, so I am obv after the contacts 4001 to 8000. How would I right this into the above query?

Hope it all makes sense?

JT

View 2 Replies View Related

Set A Range Of Records For SqlDataSource To Return?

Mar 1, 2008

Is there a way to limit the records that SqlDataSource returns with sql query, say records 10 to 20 only?

View 10 Replies View Related

Selecting A Range Of Records In A SQL Statement

Feb 12, 2005

I want to run a query that returns say 100 records...but I only want to return first 10 for first page on a web page, then on page 2 the webpage will return rows 11 to 20 of the same SQL statement...page 3 returns rows 21 to 30 rows etc....(eg. like Google or bulletin boards, browsing auctions in ebay etc.).

I could probably get my application logic to handle this (ASP.net), for instance I could possibly get a datareader to skip the first 10, output the next 10 then stop for page 2 (records between 11 amnd 20) but is there way to do this in SQL Server at the database level using an SQL Statement?

I can use TOP 10 to get the first set of records for the first page eg.

SELECT TOP 10* FROM Suppliers

...but how do I get between 11 and 20, 21 and 30 and so on?

I've already mentioned I could handle this in my application logic, but then each time the same SQL Statement is fetching all 100 records, even though the web page will only display a certain range. I'm building an intranet website that can potentially run queries that return 100'000s records, even though initially only top 20 or so records are display, each page they subsequently go to will rerun that same query that returns all 100'000 records. So handling this as part of the query would be better for performancr I reckon.

Someone gave me the syntax..

SELECT *
FROM employees
LIMIT 10,10;


..but it didn't seem to work.

Thanks for any help!!

View 14 Replies View Related

Extract Records (Rows) With In A Particular Range

Apr 16, 2004

Hello Friends,
Is there any way in sql server to get records with in a particular range. Lets say that we got 100 records satisfying a query, I want only first 20 records or records in the range 40 - 60. Kindly suggest me how to accomplish this in sqlserver and if possible provide a code snippet regarding this. Thanks in advance.

Regards,
Ch.Praveen.

View 5 Replies View Related

Query To Display Records In Given Range?

May 11, 2015

I have a column in the table which contains below values

Range
XXXX100
xxxx101
xxxx102
...
xxxx1020

below values are strings. I tried using below query but it does not return all values in the range.

Select * from table where Range Between 'XXXX100' AND 'xxxx1020'

May be in need to extract the number to display the values in given range.

View 4 Replies View Related







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