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:
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:
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
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.
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.
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.
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.
--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
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.
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
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...
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
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
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.
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.
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?
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.
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
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 ?
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()
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 ???
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)
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?
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.
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.