Comparing The Dates In Different Rows
May 10, 2008
Hi,
I have a table that holds pay rate changes with a field for the rate start date and a field for the rate end date. When an employee gets given a new pay rate, the existing rate is given an end date and a new row is added with the rate start date being the day following the end date of the old pay rate.
I need to identify the staff who have had a rate change within the past month, therefore an end date on one row that is within one month of the current month, and a start date on another row that is one day after an end date on a separate row and within one month of the current month.
Is someone able to help me out please?
Thanks
View 8 Replies
ADVERTISEMENT
Jun 21, 2005
I want to know if there is a way to compare dates in the sql statement with dates that I input into a database and todays date. the datatype that I'm using is smalldatetime.The statement I used is:Select Date from Table where Date > 'Today.now'I get an errorCould this be done or is there another approach?
View 1 Replies
View Related
Jun 26, 2006
Hello,
I am trying to retrieve the data that are not more than 3 months. How do I do this? The closest thing that I can do is...
(dbo.CLASSIFIEDADS.PostDate > CONVERT(DATETIME, '2006-03-26 00:00:00', 102))
I want to be able to put 3 months in there somehow...
Thanks in Advance!
View 2 Replies
View Related
Jan 28, 2006
I am wondering how I would create a SELECT that will select the most recent date from one of two tables. For example, table1 has a field called LastUpdate and table 2 has a field called LastUpdate. I need to grab only the most recent date. I tried this using an inner join...but that didn't work because it only picks the lastupdate form one table only. talbe1 and table2 are tied by table2.table1id.
Can anyone help?
View 7 Replies
View Related
May 23, 2006
Hi All,
I have a database field (datestamp) which returns the date the records were inserted into the database. The datestamp was created with the now(); function in .net and is in the following format: 5/23/2006 2:27:45 AM
I basically want to return all records that were inputted more than 28 days ago. I have had alook though some other posts and below is the closest query that i could find but unfortunately it does not work for me.
SELECT id, datestampFROM table WHERE datestamp > DateAdd(d, 28, GetDate())
Thanks in advance,
Jake
View 2 Replies
View Related
Jul 10, 2001
How do i compare 2 dates without comparing their Time portions. I have a Datetime column in my table which for example has values :-
6/6/2001 7:23:42 PM
6/6/2001 8:01:08 PM and so on....
I have a Stored Procedure in which i am trying to fetch values from this table. The SP has 2 DateTime parameters @DateFrom and @Dateto. This SP is called from a Web page. On the Web Page the user just enters mm/dd/yyyy values for the 2 Date parameters. Therefore the Time portions defaults to 12:00 AM. So when the user passes 6/6/2001 as Date From and 6/6/2001 as Date To values, he doesn't get back the 2 records mentioned above since the Time portion makes the 2 records fall out of the date range.
Is there a way to just do a comparison on the Date portions i.e. give me all the records for 6/6/2001 irrespective of the time. I don't wan't to do individual comparisons for Day then Month and then Year.
Thanks
Sanjay.
View 5 Replies
View Related
Dec 10, 2004
i am using sql server ver 7
i am trying to do
select *
from independent_investment_certificate_temp
where independent_investment_certificate_temp.ticker_key = 31
and convert(varchar(40), cast(independent_investment_certificate_temp.maturity_date AS datetime),107) >
convert(varchar(40), cast('2006-12-10 10:29:50.733' AS datetime),107)
independent_investment_certificate_temp.maturity_date value is '2005-01-21 00:00:00.000'
the query above returns a value even though '2005-01-21 00:00:00.000' is clearly < '2006-12-10 10:29:50.733'
but why can anyone plz explain.. how can i compare dates stripping tyhem of the time so that all dates on the same day will be
equivalent regardless of the time
View 1 Replies
View Related
Aug 25, 2005
Hi, i got this query below, which I'd like to use to compare two sets of date...
select count(*) as rec, convert(char(6),dateEntered,13) as dateEntered from EmailTracker where chtype = '1' AND convert(char(12),dateEntered,13) >= '"& stdate &"' AND convert(char(12),dateEntered,13) <= '"& endate &"' group by convert(char(6),dateEntered,13)
the format of the stdate and dateEntered (a datetime datatype) looks like - 25 Aug 2005
right now, the query simply uses the first 2 letters of the date to fetch the record. That would not do the job as i want the thing to compare the whole lot! not just the 1st two letters...
Any help pls?
View 5 Replies
View Related
Dec 16, 2004
Does anyone know of a quick and easy to compare 2 datetime values based only on the month and year.
For example,
FromDate = 11/30/2004
ToDate = 12/30/2004
I just need to compare the 11/2004 to 12/2004 using function like <=, =, >=, etc.
Any suggestions?
Thanks
View 5 Replies
View Related
Apr 4, 2008
I will try to explain my problem as best as possible without going into too much detail.
Basically I have two tables,
AgentSchedule
AgentPunchData
These tables both contain agent names.
The schedule table has a column called "SHEND" which is a DATETIME datatype which identifies when an agent shift should end.
The entries all have a date attached which means nothing, for example "12/30/1899 9:00:00 PM"
The PunchData table has a column called "LOGOUT" which is also a DATETIME, the entries will have the real date and look something like this: "3/28/2008 9:23:00 PM"
I need to strip the date out of these functions so I can compare the times. I need to query the databases and return any instances where the LOGOUT time is > the shift end time (SHEND).
So far nothing I've tried has been successful. I'm completely stumped, I've tried so many different things and nothing compares the two times correctly. SQL DATETIME is the biggest pain in the ass, I wish we had SQL 2008 so I could just use TIME.
View 6 Replies
View Related
Apr 9, 2008
hi, i have a table with persons(babies) and their date of birth. I want to delete the persons above 6 months old can u plse tell me the query ?
delete
from babies
where ......
View 7 Replies
View Related
Jun 3, 2008
Hello,
I have some code that is trying to first find the first date of service that a person visited. Using the min function is not working. Is there another function? Right now I am still getting multiple rows for each person instead of just the first visit.
Then I want to be able to compare the first date of service to the date of birth and determine if the patient first visited before they were 2 years old. I can't figure out how to do this.
select distinct b.person_id,b.date_of_birth,min(a.enc_timestamp)
from patient_encounter a
join person b on a.person_id = b.person_id
where b.date_of_birth >= '20060301' and b.date_of_birth <= '20060531'
group by b.person_id,b.date_of_birth,a.enc_timestamp
View 4 Replies
View Related
Dec 1, 1998
hi,
i would like to create an SQL query, part of which involves comparing dates.
i have something like
Select ID, CONVERT(varchar(10), StartDate, 101), Name from Table WHERE StartDate .....
how can i compare the StartDate to something i have in the form of a string? for example, i have
Date = 12/12/1998. and i would like to select where the StartDate in the DB = '12/12/1998'.
i'd appreciate all the help i can get.
thanks,
sb
View 1 Replies
View Related
Feb 21, 2007
Hi Guys,
I am really stuck on exactly how I can do this. I can sudo it in my mind but can't figure out how I would code it.
What i want to do is select all records from a table that the log_date is older than 7 days.
If I was comparing a date i would imagine i would do something like
log_date < NOW()-7
I have looked at:
SELECT player_name, player_last_logon_date,DATEDIFF(day, player_last_logon_date, GETDATE()) AS NumberOfDays from player
select player_name, player_last_logon_date, datepart(dd,player_last_logon_date) from player
but I dont think either is really right. any and all advice would be great!
Thanks
View 2 Replies
View Related
Nov 16, 2005
Basically, Im trying to return some data from an Access library database into a Java app using an SQL statement. Heres the code:
PHP Code:
Statement st = conn.createStatement();
String sql = "SELECT b.BorrowerNo, b.FirstName, b.LastName, COUNT(*) " +
"FROM Borrowers b, Loans l WHERE l.BorrowerNo = b.BorrowerNo " +
"GROUP BY b.BorrowerNo, b.FirstName, b.LastName ORDER BY COUNT(*) DESC";
ResultSet rs = st.executeQuery(sql);
This works fine, returns the BorrowerNo, FirstName, LastName, and COUNT is how many books they have loaned.
What I need it to do though, is just return loans that are late. I have a column in the Loans table called DateDueBack, so I think I need to compare this date to the current date in the WHERE statement.
I tried add this to my WHERE after l.BorrowerNo = b.BorrowerNo: AND l.DateDueBack > 16/11/2005
But this didn't work, it returned all the loans, regardless of whether they were before or after the date in the query.
Can anyone please help with this statement? I've never tried to compare dates before.
View 2 Replies
View Related
Apr 9, 2008
Hi All,
Is there a way to compare two dates fields that are different in length? For example, I have table1.datefield with a date of '040808' and table2.datefield with a date of '04082008'. Just comparing the two fields is not coming up equal. Has anyone had this situation before?
Thanks.
Frank
View 1 Replies
View Related
Aug 4, 2015
I have a date comparison situation in which I will have a column with a date and will have another value containing a GETDATE() minus two weeks. I want to be able to compare both dates and get the MIN date between the two. I'm not sure how to use the MIN(Date) in this scenario since the comparison won't be between two different columns, but between one column and a random date generated by the GETDATE() minus two weeks.
View 6 Replies
View Related
Feb 8, 2008
Hi,
I have a table that temporarily stores information. Before I insert new data in the table, I want to delete the previous day's data.
This is the query I'm using
Code:
q="DELETE FROM SearchTemp WHERE theDate < "&Date()
set RS2 = DB.Execute(q, ra, adCmdText)
I should say I'm using ASP & VBScript.
Problem is, this isn't deleting the previous day's dates.
The dates are stored in this format: m/d/yyyy.
I've searched through old posts but none of the solutions offered have worked for me so far. Anyone have any suggestions?
Thanks!
View 4 Replies
View Related
Jan 30, 2006
guys - is this a decent query to pull all columns (dateCreate)
that have a timestamp less than five minutes?
i know its simple, but i've never done a date compare with minutes or hours
in sql server
thanks
rik:o
select top 10 * from ptpuritm
where datediff(MINUTE,dateCreate,getdate()) <=5
select top 10 * from ptpuritm
where datediff(MINUTE,dateCreate,current_timestamp) <=5
View 3 Replies
View Related
Sep 12, 2006
Hi,
I'm trying to write a function to return all notes with date. Sample data for 1 record=187189 as follows:
iincidentid,iWorkNoteId,iSeqnum, dtEntryDate, workNoteAll
1871893440 1 2006-04-24 note1
1871893545 1 2006-06-22 note2
1871893547 1 2006-06-22 note3
1871893653 1 2006-08-10 note4
1871893653 2 2006-08-10 note5
funtion will return = 2006_08-10 note4 note5 for iincidentid=187189
-----------------------------------------------------
CREATE FUNCTION dbo.getIncidentNotesRev(@iIncidentID int)
RETURNS varchar(8000)
AS
BEGIN
declare @incidentId int
declare @worknoteid int
declare @worknotesaveid int
declare @seqnum int
declare @dtEntryDate smalldatetime
declare @worknoteall varchar(8000)
declare@allnotes varchar(8000)
declare @currentWEDate smalldatetime
declare @beginWEDate smalldatetime
select @allnotes=''
select @currentWEDate=currentweekEndDate from csCurrentweekEndDate --get the current week end date
select @beginWEDate = DATEADD(d, - 28, @currentWEDate)--get the last 4 weeks
declare CursorIncident CURSOR
LOCAL FOR SELECT iIncidentId, iWorkNoteID, iSeqNum, dtEntryDate,worknoteall FROM dbo.rpt_weekly_prospect_status_vw
where iIncidentId=@iIncidentID order by iWorkNoteId
OPEN CursorIncident
FETCH NEXT FROM CursorIncident INTO @incidentId,@worknoteid,@seqnum,@dtEntryDate,@work noteall
--store 1st record of cursor
select @worknotesaveid =@worknoteid
WHILE (@@FETCH_STATUS=0)
BEGIN
if @dtEntryDate >=@beginWEDate AND @dtEntryDate <= @currentWEDate
Begin
if @worknotesaveid <> @worknoteid
Begin
Select @allnotes = @allnotes + @dtEntryDate + @worknoteall
End
else
BEgin
select @allnotes = @allnotes + @worknoteall
End
select @worknotesaveid = @worknoteid --save next worknoteId
End
else
Begin
select @allnotes=''
End
FETCH NEXT FROM CursorIncident INTO @incidentId,@worknoteid,@seqnum,@dtEntryDate,@work noteall
END --WHILE (@@FETCH_STATUS=0)
CLOSE CursorIncident
DEALLOCATE CursorIncident
return @allnotes
END
----------
Function not working right. I appreciate any help.
Thanks in advance.
View 3 Replies
View Related
Mar 17, 2004
Hi,
Below is my store procedure. I am trying to capture all orders between a startdate and a enddate. When I use the Where #1(see below- I have 2 where clauses) it allows me to go from one year to another. For example, startdate of 2/18/2003 - enddate of 2/19/2004 will return all orders in my table except for 2/19/2004. Probably because of the time portion.
When I use the Where #2, it doesn't go back a full year. The startdate is reacting like 2/18/2004 and not 2003. The records I get are only for dates 2/18/2004-2/19/2004.
How do I create a where clause which will go back a year or more and include the enddate orders? Any help will be very much appreciated.
Thanks,
poc1010
alter Procedure GetOrdersBetweenDates
@startdatesmalldatetime=null,
@enddatesmalldatetime=null
as
select o.orderid,(convert(varchar(12),o.orderdate,101)) as Date
,o.orderamt,o.shipamt,o.njtaxamt,o.totalamt
,c.bfirstname+' '+c.blastname as BName,c.baddress1,c.baddress2
,c.bcity,c.bstate,c.bzip
,c.sfirstname+' '+c.slastname as SName,c.saddress1,c.saddress2
,c.scity,c.sstate,c.szip
from orders o inner join customers c on o.orderid=c.orderid
1.)where o.orderdate between @startdate and @enddate
2.)where convert(varchar(12),o.orderdate,101) between
convert(varchar(12),@startdate,101) and convert(varchar(12),@enddate,101)
order by o.orderid
exec getordersbetweendates '2/18/2003','02/19/2004'
View 1 Replies
View Related
Aug 21, 2007
To the experts in the field:
There is probably a very simple solution that is avoiding my grasp.
I have a For Loop which I want to execute as long as a variable called BeforeRunDt = CurrentDate. Both are DateTime data types and I am using the following expression:
@BeforeRunDt==@CurrenDate
I get an error stating "Cannot convert expression value to propeerty type"
I understand that the result of the expression should be a boolean value but am just struggling on how to create it.
Thanks!
View 6 Replies
View Related
Jan 31, 2006
This is a bit similar to the topic at
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56058
but with a bit of a twist...
I have
Batch Sample Value
0 0 A
0 1 A
1 0 A
1 1 B
2 0 B
2 1 B
...
I'm looking to get something that would provide a row comparison - something that looks like
Batch Different
0 FALSE
1 TRUE
2 FALSE
...
Any suggestions for a query that would do this? Thanks!
View 3 Replies
View Related
Feb 26, 2013
Code:
Room ID Hours StartTime EndTime
GR41908101700
GR42409101300
GR43317102000
LR111108100900
LR112109101000
LR113210101200
LR114112101300
LR115313101600
LR116216101800
LR117118101900
LR118119102000
LR119320102300
As you can see each room is assigned and id and once the room changes the id starts from 1 again. I don't want the 2nd row to appear because 9am - 1pm is in 8am - 5pm. How do I remove this row?
View 3 Replies
View Related
Sep 25, 2013
create table #prints(id int IDENTITY(1,1) NOT NULL,
Printermarkersupplyid varchar(36),PrinterID varchar(10),Description varchar(10),SupplyLevel int,ModifiedDate datetime)
insert into #prints(Printermarkersupplyid,PrinterID,Description,SupplyLevel,ModifiedDate)
select newid(),'P1','D1',100,'2013-08-1 03:28:38.203'
union all
[code]....
my requirement is to get the difference between adjacent rows.ie difference between 2nd and 3rd or 6th and 7th but not 6th and 8th.if difference between 2nd and 3rd is less than zero and 3rd modified date > 2nd modified date,then i should get count as 1 against 3rd row.
View 2 Replies
View Related
Jun 29, 2012
I've been working with T-SQL in a MSSQL Server Management Studio (2005) for about a week now. I've been trying to convert some horribly written VB code from a MS Access DB over to SQL so it can be automated on a SQL backend.
Most of the learning process and coding has gone surprisingly well. The problem is with comparing some data to determine which one needs to be flagged.
Three tables to note in bold, with notable fields in italics below them:
EmployeeData
HRID (identity)
ResourceAllocation
ID (identity)
[Last Name] (linked to HRID)
Project
[Resource Start Date]
[Resource End Date]
[Percent Utilization]
tblHCvalues
RAID (linked to ResourceAllocation.ID)
a monthyear and quarteryear for every month and quarter from 2012-2014. IE january12, february12, 1q12, 2q13, etc...
And yes, there are probably a thousand ways to optimize that tblHCvalues, but I'll ask about that later. Just work with the structure I have
Here's how it works: Each employee's data and unique HRID is in the EmployeeData tableAn employee can be on one or multiple projects at any timeThose projects are stored per project in the ResourceAllocation table with a link to the Employee's HRID, and all the other information listed aboveEven though an employee might be on two projects, they can only count for headcount on one project.
We use rules that compare the percent of work being done on a project, and the start and end dates of the employee (resource) on that project to determine which project should be counted for Headcount. The code uses a cursor to go through each HRID, and then pull up all the ResourceAllocation records associated with it.Run the rules to determine which ResourceAllocation record counts toward headcountA stored procedure then runs that fills out the tblHCvalues in the way we want for the project we want
All of it works, except for the rules that compare the things, so that's what I want to focus on in this thread. How do I write these rules:
Here are the rules, and they should work for any number of multiple resource allocations for one employee:
Choose the ResourceAllocation with the greatest [Percent Utilization]If the top ResourceAllocations have equal [Percent Utilization], choose the ResourceAllocation with the earliest [Resource Start Date]If the [Percent Utilization] and the [Resource Start Date] are equal, choose the latest [Resource End Date]If all three fields are equal, choose the first ResourceAllocation (aka, screw it and pick one at random)
I'm sure I could use a bunch of IF statements to compare it all, but even that is complicated to think about. There has to be an easier way, right?
View 6 Replies
View Related
Jun 15, 2007
How do I only select rows with duplicate dates for each person (id)? (The actual table has approximately 13000 rows with approximately 3000 unique ids)
p_key id date pulse
--------------------------------------
1 32 5/25/2006 80
2 32 5/25/2006 85
3 32 4/26/2006 81
4 32 6/15/2006 82
5 15 1/20/2006 75
6 15 3/25/2006 80
7 15 3/25/2006 83
Result table I am looking for:
p_key id date pulse
------------------------------------
1 32 5/25/2006 80
2 32 5/25/2006 85
6 15 3/25/2006 80
7 15 3/25/2006 83
Thanks.
View 8 Replies
View Related
Sep 22, 2006
I want to do a login in vb.net, when the user enters username and password, it looks into ms sql to find user then compare password with database then select user in database and closes form to open another form with selected database. please help, i am stranded with my project and i am getting nowhere.
View 6 Replies
View Related
Sep 22, 2006
I want to do a login in vb.net, when the user enters username and password, it looks into ms sql to find user then compare password with database then select user in database and closes form to open another form with selected database. please help, i am stranded with my project and i am getting nowhere.
View 1 Replies
View Related
Oct 30, 2015
I need to compare two consecutive rows (if BEGDA of second row is 1 day greater than ENDDA of first row then I need to pick First row BEGDA and 2nd row ENDDA)
Input Table Data:
PERNRSUBTYBEGDAENDDA
1010429001/1/20081/15/2015
10104210001/1/200812/31/2008
10104210001/1/200912/31/2009
10104220001/1/20081/15/2008
10104220001/16/200812/31/2008
10104220001/1/200912/31/2009
10104230001/1/200812/31/2008
10104230001/1/200912/31/2009
10104230001/5/201012/31/9999
101042DDPP5/16/200712/31/2007
101042DDPP2/16/20075/15/2007
101042MAPP2/1/200712/31/2007
101042VISI3/1/200712/31/2007
Output should be like this:
PERNRSUBTYBEGDAENDDA
1010429001/1/20081/15/2015
10104210001/1/200812/31/2009
10104220001/1/200812/31/2009
10104230001/1/200812/31/2009
10104230001/5/201012/31/9999
101042DDPP2/16/200712/31/2007
101042MAPP2/1/200712/31/2007
101042VISI3/1/200712/31/2007
View 3 Replies
View Related
Jul 31, 2007
Hi,
I've a bunch of records that may contain data that I'm after. For example:
This is a fake title [electronic resource]. 1997.
I have a very small table (~10 rows) of things like '[electronic resource]'
Is there any way to see if my record contains any of the 'target' items in the other table?
View 1 Replies
View Related
Jul 20, 2005
I'm trying to come up with an elegant, simple way to compare twoconsecutive values from the same table.For instance:SELECT TOP 2 datavalues FROM myTable ORDER BY timestamp DESCThat gives me the two latest values. I want to test the rate ofchange of these values. If the top row is a 50% increase over the rowbelow it, I'll execute some special logic.What are my options? The only ways I can think of doing this arepretty ugly. Any help is very much appreciated. Thanks!B.
View 22 Replies
View Related
Jun 27, 2007
Hello,I currently have Table1 and View1.View1 is a query from 2 or 3 tables that works fine on its own.However in my current query if I try to use it...something like...SELECT a.col1, a.col2, a.col3, b.col1, b.col2, b.col3FROM View1 a JOIN Table1 b on a.col1 = b.col1WHERE a.col2 <b.col2 OR a.col3 <b.col3It throws an error "Server: Msg 446, Level 16, State 9, Line 1 Cannotresolve collation conflict for not equal to operation."Clearly I need to use collation between Table1 and View1, But I dontknow where I need to use "COLLATE SQL_Latin1_General_CP850_CI_AI" andhow? this is the collation set on Table1.Thank you!Yas
View 1 Replies
View Related
