Help Needed For Datediff Function For SQL Query
Apr 9, 2007
Hi Experts,
I am working on SSRS 2005, and I am facing a problem in counting the
no of days.
My database has many fields but here I am using only two fields
They are Placement_Date and Discharge_Date
If child is not descharged then Discharge_Date field is empty.
I am writing below query to count the number of days but is is not
working it is showing the error
"The conversion of a char data type to a datetime data type resulted
in an out-of-range datetime value."
select case
when convert(datetime,Discharge_Date,103) = '' then
datediff(day,CONVERT(datetime,Placement_Date,103), GETDATE())
else
datediff(day,CONVERT(datetime,Placement_Date,
103),CONVERT(datetime,Discharge_Date,103))
end NoOfDays
from Placement_Details
So please tell me where I am wrong?
Any help will be appriciated.
Regards
Dinesh
View 3 Replies
ADVERTISEMENT
May 18, 2007
Hi,
I am trying to use DataDiff function and I have used the following queries:
1.
select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.320') as test
Expected Result: 0 milliseconds
Actual Result: 0 milliseconds
2.
select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.321') as test
Expected Result: 1 milliseconds
Actual Result: 0 milliseconds
3.
select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.322') as test
Expected Result: 2 milliseconds
Actual Result: 3 milliseconds
4.
select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.323') as test
Expected Result: 3 milliseconds
Actual Result: 3 milliseconds
5.
select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.324') as test
Expected Result: 4 milliseconds
Actual Result: 3 milliseconds
6.
select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.325') as test
Expected Result: 5 milliseconds
Actual Result: 6 milliseconds
7.
select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.326') as test
Expected Result: 6 milliseconds
Actual Result: 6 milliseconds
8.
select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.327') as test
Expected Result: 7 milliseconds
Actual Result: 6 milliseconds
9.
select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.328') as test
Expected Result: 8 milliseconds
Actual Result: 6 milliseconds
10.
select datediff(ms, '2007-05-18 19:35:07.320','2007-05-18 19:35:07.329') as test
Expected Result: 9 milliseconds
Actual Result: 10 milliseconds
Does any one know, why datediff does not return the Expected Result? There does not seem to be any consistency.
Thanks,
Tim
View 1 Replies
View Related
Aug 24, 2007
Hi, I have to compare every row in month column in xyz table with current month. If there is mismatch, then error message must be displayed. I have used DATEDIFF (MM, Select getdate(), select month from xyz ), but I am getting error at MM. Can anybody fix this issue.
View 2 Replies
View Related
Jan 5, 2007
I am trying to figure out how much coverage (supervision time) an employee has based on their managers schedule.
Example
Name: Employee, SundayStart: 9 AM, SundayEnd: 5 PM
Name: Manager, SundayStart: 8 AM, SundayEnd: 4 PM
############# 8 AM |Covered Time| 5 PM
Employee ##########|------------|-----
Manger ######------|------------|
Employee is covered for 6 hours on sunday.
Anyway to figure this out? This is what I have so far. Sorry, but im in access 03.
SELECT tblCAESchedule.Name, tblCAESchedule.Title, tblCAESchedule.SunStart, tblCAESchedule.SunEnd,
IIf(DateDiff("n",[tblCAESchedule].[sunstart],[tblCAESchedule].[sunend])>=1,DateDiff("n",[tblCAESchedule].[sunstart],[tblCAESchedule].[sunend]),DateDiff("n",[tblCAESchedule].[sunstart],[tblCAESchedule].[sunend])+1440)/60 AS SunHours,
tblSupSchedule.Name, tblSupSchedule.SunStart, tblSupSchedule.SunEnd,
Abs(DateDiff("n",[tblCAESchedule].[sunstart],[tblSupSchedule].[sunstart])/60) AS StartDif,
Abs(DateDiff("n",[tblCAESchedule].[sunend],[tblSupSchedule].[sunend])/60) AS EndDif,
Abs(DateDiff("n",[tblCAESchedule].[sunstart],[tblSupSchedule].[sunstart])/60)+Abs(DateDiff("n",[tblCAESchedule].[sunend],[tblSupSchedule].[sunend])/60) AS ShiftDif
FROM tblCAESchedule INNER JOIN tblSupSchedule ON tblCAESchedule.Skill = tblSupSchedule.Skill
WHERE tblCAESchedule.Skill = "Sales"
View 4 Replies
View Related
Nov 23, 2006
Hi, I am facing problem rite now.. I want to calculate the date different minutes between 23:00:00 and 01:00:00.
My code :
datediff(Minute,'01:00:00','23:00:00')
The result is 1320 minutes. (22 hours)... But, the result that I want is 120 minutes (2 hours)....
Can anybody help ???
Thanks in advance...
View 2 Replies
View Related
Nov 12, 2007
Hi,I'm using the datediff function to display the ages of the users in my database. However the age rounds up once they are 35.5 etc...I could create another function which works similar to the DateDiff function, but use math.floor to always round down, but I need to use this function in a SQL statement WHERE clause. Is there any way around this?Thanks,Curt.
View 3 Replies
View Related
Apr 25, 2007
hi
select datediff(m,'3/5/2003',getdate as experience
output is
Experience
----------
49
select (datediff(m,'3/5/2003',getdate()))/12 as experience
output is
Experience
----------
4
but 49/12 is 4.08333=4.1
how i can get this
Actually my task is to output as years.months
Thanks in advance
Malathi Rao
View 7 Replies
View Related
Oct 5, 2006
I am trying to break up the age into column from a dob field for a cross tab report. I can query the datediff with an alias but can not individually change the columns I need like a virtual or temp column, but can't figure out how to accomplish it.
use 'Database'
Select datediff(Year, dob, getdate()) As "Under 22", datediff(Year, dob, getdate()) As "22-45",
datediff(Year, dob, getdate()) As "46-65", datediff(Year, dob, getdate()) As "Over 65"
from 'Table'
WHERE (DATEDIFF(yy, dob, GETDATE()) < 22 OR
DATEDIFF(yy, dob, GETDATE()) BETWEEN 22 AND 45 OR
DATEDIFF(yy, dob, GETDATE()) BETWEEN 46 AND 65 OR
DATEDIFF(yy, dob, GETDATE()) > 66) AND (status = 'Current' OR
status = 'Previous' OR
status = 'non-billable')
View 5 Replies
View Related
Feb 7, 2007
This will give you the time information about how apart two dates are.CREATE FUNCTION dbo.fnTimeApart
(
@FromTime DATETIME,
@ToTime DATETIME
)
RETURNS @Time TABLE ([year] SMALLINT, [month] TINYINT, [day] TINYINT, [hour] TINYINT, [minute] TINYINT, [second] TINYINT, [millisecond] SMALLINT)
AS
BEGIN
DECLARE@Temp DATETIME,
@Mts INT,
@year SMALLINT,
@month TINYINT,
@day TINYINT,
@hour TINYINT,
@minute TINYINT,
@second TINYINT,
@millisecond SMALLINT
IF @FromTime > @ToTime
SELECT@Temp = @FromTime,
@FromTime = @ToTime,
@ToTime = @Temp
SET@Mts =CASE
WHEN DATEPART(day, @FromTime) <= DATEPART(day, @ToTime) THEN 0
ELSE -1
END + DATEDIFF(month, @FromTime, @ToTime)
SELECT@year = @Mts / 12,
@month = @Mts % 12,
@Temp = DATEADD(month, @Mts, @FromTime)
SELECT@day = datediff(hour, @Temp, @ToTime) / 24,
@Temp = DATEADD(day, @day, @Temp)
SELECT@hour = DATEDIFF(minute, @Temp, @ToTime) / 60,
@Temp = DATEADD(hour, @hour, @Temp)
SELECT@minute = DATEDIFF(second, @Temp, @ToTime) / 60,
@Temp = DATEADD(minute, @minute, @Temp)
SELECT@second = DATEDIFF(millisecond, @Temp, @ToTime) / 1000,
@Temp = DATEADD(second, @second, @Temp),
@millisecond = DATEDIFF(millisecond, @Temp, @ToTime)
INSERT@Time (year, month, day, hour, minute, second, millisecond)
SELECT@year,
@month,
@day,
@hour,
@minute,
@second,
@millisecond
RETURN
ENDAnd to test the functionSELECTd.FromDate,
d.ToDate,
x.*
FROM(
SELECT'19690906' AS FromDate, '19760608' AS ToDate UNION ALL
SELECT'19991231', '20000101' UNION ALL
SELECT'20070207', '20070208' UNION ALL
SELECT'20000131', '20000228' UNION ALL
SELECT'20070202', '20070201' UNION ALL
SELECT'20070207', '20070307' UNION ALL
SELECT'20000131', '20000301' UNION ALL
SELECT'20011231 15:24:13.080', '20020101 17:15:56.343' UNION ALL
SELECT'20011231 17:15:56.343', '20020101 15:24:13.080' UNION ALL
SELECT'20020101 15:24:13.080', '20011231 17:15:56.343' UNION ALL
SELECT'20000131', '20000229'
) AS d
CROSS APPLYdbo.fnTimeApart(d.FromDate, d.ToDate) AS x
ORDER BYd.FromDate,
d.ToDateAnd the output isFromDateToDateyearmonthdayhourminutesecondmillisecond
19690906197606086920000
19991231200001010010000
200001312000022800280000
200001312000022900290000
20000131200003010110000
20011231 15:24:13.08020020101 17:15:56.34300115143263
20011231 17:15:56.34320020101 15:24:13.08000022816736
20020101 15:24:13.08020011231 17:15:56.34300022816736
20070202200702010010000
20070207200702080010000
20070207200703070100000
Peter Larsson
Helsingborg, Sweden
View 14 Replies
View Related
Feb 9, 2007
I have two date columns one is sent_date and other is approved_date
my requirment is to find the difference between the two dates
which can be minutes/hrs/days.
using datediff function iam able to get it in minusts or hrs but my
output should be of the format hh:mm
23:10 (ie 23 hrs and 10 min) or say
48:00 (for 2 days)
sample date
sent_date approved_date
2/28/06 11:06 2/28/06 11:39
2/2/06 17:42 2/2/06 18:03
2/8/06 16:55 2/8/06 17:38
1/27/06 17:00 1/27/06 17:54
1/26/06 12:08 1/26/06 12:09
2/28/06 15:46 2/28/06 16:26
1/23/06 10:01 1/23/06 10:43
1/26/06 13:46 1/26/06 13:59
1/13/06 13:51 1/13/06 14:47
View 4 Replies
View Related
Nov 2, 2007
recently i created a system using php and mysql to record faults that the ict technicians could get reported faults out of. so far it does everything i want but some senior members of staff now want the front end to give a report of status, one of the things they wish to see is how many jobs have been completed this week month and year
within my tables is a field date. naturally it has the date submited contained within it. the code i have been using to try and get an output is
mysql_query("SELECT date, datediff('day', date, CURRENTDATE) FROM softwarerepairtable WHERE datediff<= '7' and complete='yes' ");
the complete feild is another contained within the datebase. i have also tried SELECT * datediff('day', date, CURRENTDATE) FROM softwarerepairtable WHERE datediff<= '7' and complete='yes' ");
unfortunatly my limited knowledge of php and sql is holding me back on this. any help guys would be appriacted
View 2 Replies
View Related
Jan 25, 2008
I want to create and expression that basically says if a datetime value is today minus 2 days then do another formula.
Right now it looks like this...
Code Snippet
=iif(Fields!Channel_Id.Value="SFE1" and Fields!Report_Date.Value=Today-2,0,(Fields!Total_Apps.Value-Fields!total_apps_null_status.Value))
Basically, it spits an error saying [BC30452] Operator '-' is not defined for types 'Date' and 'Integer'.
Should this be done with DateDiff? if so, how can you specify two days ago?
Thanks much
C-
View 5 Replies
View Related
Feb 3, 2015
I'm trying to use the DateDiff function within my select statement, but I'd like to add the parameter of greater than 30 days. This will have the query only return records where my bill stop date is greater than 30 days from the completion date. I currently have the datediff function within my select statement as
DATEDIFF (d,A.StopBillDate, a.CompletionDate) as [DIFFERENCE]
I would prefer to keep the datediff function within the select statement so as to have difference in days appear as a column within my output.I have been unable to add the parameter of > 30 days to the query without getting an error.
View 2 Replies
View Related
May 15, 2007
Hi
I regularly use the T-SQL date functions to return the 1st of a particualr month.
e.g.
SELECT DATEADD(m,DATEDIFF(m,0,getdate()),0)
returns 2007-05-01 00:00:00.000
i.e the first of the current month at midnight.
However, when I try to use a similar expression as a derived column in SSIS it returns a completely different date.
DATEADD("month",DATEDIFF("month",(DT_DATE)0,GETDATE()),(DT_DATE)0)
returns 30/05/2007 00:00:00
Any ideas why and how I can obtain the first of a particualr month using SSIS derived column?
View 3 Replies
View Related
Sep 12, 2015
When running a query such as this on a database view:
select count(*) from WWALMDB.dbo.v_AlarmEventHistory2
SQL throws the following error:
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
Oddly on a Table the count(*) function works.
Is there a limit on views or the count(*) function that I am not aware of?
View 3 Replies
View Related
Feb 12, 2008
Dear all, I have the Following Query:SELECT DATEDIFF(day, GETDATE(), RECENT_RESERVATION)AS Expr1,RECENT_RESERVATION FROM EMP WHERE SUN=empName;when i run it inside the query analyzer, it returns two columns. but if run it inside The ASPX page it retuns only one column wich is RECENT_RESERVATION date.Note: i am using one methode that takes care of reading from SQL and assigning the result into an array, it works fine everywhere, but with this query it dosen't work. Any suggestions??
View 6 Replies
View Related
May 2, 2015
I have added one webpage designed in ASP.Net with C# and sql server 2005 as database. There is table for user registration in which there is a column for ProfileCreationDate the data type of that column is date time .
I would like to fetch data of those user who have created profile within 7 days. For getting desired result I am trying this query.
select Name ,Profession,ProfileCreationDate from tblRegistration where DATEDIFF ( Day , '" + System.DateTime.Now + "',ProfileCreationDate)<7 order by ProfileCreationDate DESC
System.DateTime.Now is a function for getting current date time in C#
The query is neither giving error nor giving desired result.
View 4 Replies
View Related
Aug 7, 2015
I have a requirement to use DateDiff(Months,DateTime1,DateTime2) and this must return positive integer values.
Currently negative numbers are being returned because DateTime1 < DateTime2 or DateTime1 > DateTime2 .
The DateTime1 and DateTime2 values are dynamic and either of them can be bigger than the other.
Any query solution so that always positive value is returned.
View 3 Replies
View Related
Sep 26, 2006
I have a table with a column called Userdef. I is a user defined field. It looks like this ;;Polk;D-0002;;;;As you can see it is delimited by semicolons. I need to separate the semicolons into separate files like this Field1Field2Field2Field4Field5Field6<null><null>Polk<null>D-0002<null>How do I write this query in SQL Server?
View 1 Replies
View Related
Jun 6, 2008
Hi,
I have Table called performance where i what to get the AsOfDate(char 10) from that table... and i want to get the most recent based on this function.
ALTER FUNCTION [dbo].[udf_Quarter] (@Date datetime)
RETURNS char(10)
AS
BEGIN
DECLARE @month int
SET @month = Month(@Date)
RETURN
CASE
WHEN @month BETWEEN 4 AND 6 THEN 5
WHEN @month BETWEEN 7 AND 10 THEN 6
WHEN @month BETWEEN 11 AND 12 THEN 7
WHEN @month BETWEEN 1 AND 3 THEN 8
END
END
and i want this to return 3/31/2008 but i want to make it dynamic like if it is "3/31' and year can be depending on the year it passes
Any help will be appreciated.
Thanks
Karen
View 2 Replies
View Related
Jul 19, 2006
Hello all. I have an Access table with EmpName, JobTask, and Hours. Multiple lines can contain the same Employee with the same JobTask. What I need to do is to list the Employee, JobTasks (grouped), and summed hours for each JobTask.
DB Ex:
John Doe Welding 8
John Doe Cleaning 4
Bubba Smith Fork Lift 3
John Doe Welding 7
Steve Johnson Welding 5
Bubba Smith Fork Lift 6
Page output:
John Doe
Welding 15 hrs.
Cleaning 4 hrs.
Bubba Smith
Fork Lift 9 hrs.
Steve Johnson
Welding 5 hrs.
This is how I figured it should be, but it's not working for me:
SELECT EmpName, SUM (Hours), JobTask
FROM tblEmpTime
Group By JobTask
Any suggestions would be greatly appreciated.
Thanks,
Parallon
View 4 Replies
View Related
Mar 26, 2014
I have two dates '2014-25-03 01:02:03' & '2014-26-03 01:02:03' (yyyyddmm).
I need the difference between two dates in hh:mm:ss format.
I tried following query
declare @mindate datetime='2014-03-25 01:02:03'
declare @maxdate datetime='2014-03-26 02:03:04'
select cast(
(cast(cast(@maxDate as float) - cast(@minDate as float) as int) * 24) /* hours over 24 */
+ datepart(hh, @maxDate - @minDate) /* hours */
as varchar(10))
+ ':' + right('0' + cast(datepart(mi, @maxDate - @minDate) as varchar(2)), 2) /* minutes */
+ ':' + right('0' + cast(datepart(ss, @maxDate - @minDate) as varchar(2)), 2) /* seconds */
This works fine but as you can see the date I have used in the query is in yyyymmdd format.
The query does not work with yyyyddmm format
The expected answer is =25:01:01.
View 5 Replies
View Related
Apr 30, 2007
I'm working on Application that requires me to check and display status of reports running on report server. My application calling ListJobs() function of Job class part of Reporting Services Web Service API. When i run my application i'm getting insufficient previleges error. So i need to find out what type of permission i need to excute ListJobs().
This is very important part of my app. Please help me out.
Thanks,
Viral Patel
View 1 Replies
View Related
Jun 29, 2006
I debug SPS on a daily basis and I use SQL profiler to help me trace where the problem is.
Once I have established which SP is the main problem I need to debug the line of code.
What I do is Cut and Paste the SQL Profiler details and populate all the parameters,sometimes that can be 30 and more..
Now what i thought is to write an SP or Function where I pass :
SP name and Parameters that profiler genererates
and returns me Declare Statements and Set Statements with parameters filled.
EG
Profiler Returns
Customer_INSERT,20,'JO',BLOGG','5 LONDON ROAD'
I would call my new SP =PopulateSPParams and cut and paste the profiler's string
PopulateSPParams 'Customer_Insert,20,'JO',BLOGG','5 LONDON ROAD'
this will RETURN THE FOLLOWING THAT WILL IMMENSILY HELP MY DAILY PROGRAMMING.
DECLARE
@CustomerID int,
@CustomerName varchar(50),
@CustomerSurname varchar(50),
@CustomerAddress varchar(100)
SET @CustomerID =1
SET @CustomerName='JO'
SET @CustomerSurname='BLOGG'
SET @CustomerAddress='5 London Road'
Can you help in writing something that generates and populate parameters?
View 6 Replies
View Related
Jul 23, 2007
i have marked in bold the query in question ..whch gives me the the runtime error mentioned at the below , i double checked everything all the table names and the field names are correct so whats the problem , please anyone??im really stumped! if (Radio_Btn_Book.Checked == true) { string book_query = "update Issue_Book_Reserve I set I.Issue_Book_Reserve_state = 1 where I.Book_Id = Books.Book_Id and Books.Book_Name = '" + Session["Book_name"].ToString()+"'"; SqlCommand Cmd_book = new SqlCommand(book_query, con); con.Open(); Cmd_book.ExecuteNonQuery(); con.Close(); } ERROR: The column prefix 'Books' does not match with a table name or alias name used in the query.The column prefix 'Books' does not match with a table name or alias name used in the query.
View 3 Replies
View Related
Jan 14, 2008
Hi,
TABLES
(Association CONTAINS COLUMNS AssociationID),
(Group CONTAINS COLUMNS GroupID, GroupName, AssociationID),
(GroupMembers CONTAINS COLUMNS GroupID, GroupMemberID, UserprofileID, DateCreated, DateRemoved),
(UserProfiles CONTAINS COLUMNS UserProfileID, UserID),
(AllUsers CONTAINS COLUMNS UserID, FirstName, LastName)
I am trying to write a query to collect information about from the tables. I need to collect all the Users who are not members of Group A in Association I.
Note that Users can belong to more than one group and have more than one profile.
I would appreciate it if you could help me figure out how to deal with this logic. Thanks in Advance
View 2 Replies
View Related
Mar 20, 2008
Hi
I have a function which returns the periodId from the period table and the function is as follows:
ALTER FUNCTION [dbo].[udf_Quarter] (@Date datetime)
RETURNS int
AS
BEGIN
DECLARE @month int
SET @month = Month(@Date)
RETURN
CASE
WHEN @month BETWEEN 1 AND 3 THEN 5
WHEN @month BETWEEN 4 AND 6 THEN 6
WHEN @month BETWEEN 7 AND 9 THEN 7
WHEN @month BETWEEN 10 AND 12 THEN 8
END
END
Which works fine but i want to do the following with it. We produce quarterly statements.. So the user comes in jan or the begining of Feb to upload and process and order Statements from 10/31 to 12/31 the the 4 quarter data.. So Is there a way i can check if the user orders the statement in between jan and feb it has to reture PeriodId 5.
Any help will be appreciated.
Regards,
Karen
View 5 Replies
View Related
Apr 10, 2008
I have three parameters called @rsLengthofservice, @rsLengthofServicematch and @rsLenghtofservicePS.
How do i check if all the three parameters have the same value..
Any help will be appreciated..
Regards,
Karen
View 5 Replies
View Related
Apr 25, 2008
Hi,
I have this query
SELECT
o.OrderId,
o.OrderDate,
o.CreatedByUserId,
c.LoginId,
o.Quantity,
o.RequiredDeliveryDate,
cp.PlanId,
cp.ClientPlanId
--cp.ClientId
FROM
[Order] o
Inner Join Plan cp on o.PlanId = cp.PlanId and o.CreatedByUserId = cp.UserId
Inner Join User c on o.CreatedByUserId = c.UserId
WHERE
--cp.ClientId = @ClientId
--AND
o.OrderDate BETWEEN @StartDate AND @EndDate
ORDER BY
o.OrderId DESC Which gives the list of order from the start date to the end date..but it doesnt List the orders which are some mins apart. For eg. 16524/24/2008 10:48:05 AM4252840
16534/25/2008 10:15:42 AM378550
16544/25/2008 10:51:24 AM3147110 16614/25/2008 1:35:54 PM425045
16624/25/2008 2:30:43 PM4174387130
16634/25/2008 2:47:14 PM154825
16644/25/2008 2:49:10 PM265085
it listed order no. 1652 and 1654 but not 1653 and in the next 4 it didnt list 1663.. why is this happening.. I am using a reports in the report server. any help will be appreciated. Regards,Karen
So it kist
View 3 Replies
View Related
Nov 17, 2003
Hi everyone,
I consider myself good at SQL when it comes to SELECTS, UPDATES, INSERTS etc - but now I'm actually wanting to delete a record.
How can I do this? Can I just use:
DELETE * FROM Table WHERE RowID = '23'
??
Can anyone help me out?
Cheers
Andrew
View 4 Replies
View Related
Jun 6, 2005
hi,
I have three tables with the following fields:
User:
UserID
Name
Test:
TestID
Name
Grade:
GradeID
TestID
UserID
Score
And I want to display the result something like this:
UserID Name Test1 Test2 Test3
1
Azam
23
34 45
2
Ali
34
45 56
How can this be accomplised thanks,
View 10 Replies
View Related
Aug 7, 2005
my app contains one form (aspx) and it has different controls to be filled by user (textbox,radiobutton ..etc)it has one button which i want to use to pass values entered in these controls to other page and do some queries to sql server there (2nd page)Now the thing is ...my controls can have NULL values ...like user could enter just one parameter and hit button or user can fill 2 parameter and hit enter so on the other hand (2nd page) how should i query the database accordingly ....
View 2 Replies
View Related
Dec 2, 2005
I have some data that looks like this:
field1(pk) field2 field3
563136 199535840A D2119562408 199615461C D2119562806 199748610A D2119547463 199748610A D2368562278 200255598A D2368562286 200255598A D2468
Field2 can have the same value.
What I need is to return all records where field3 is not 'D2468' ever for field2. So with the above data, I'd want the first 4 records returned, but not 5 and 6 because for those field2 is the same value and in 6, field3 is 'D2468'.
So, I can't simply say:
SELECT a.field2FROM table1 a inner join table2 b on a.field2 = b.field2 and a.field5 = b.field5where a.field3 not like 'D2468'
because it will still return record 5.
Can anyone help me with this? Thank you!
View 3 Replies
View Related