RecipeDan
asked on
Show records based on prior records
Hello:
I have this table
PersonName DateA DateB
Bill 01-09-2014 01-29-2014
Bill 01-09-2014 01-16-2014
Bill 01-09-2014 02-24-2014
John 01-09-2014 02-16-2014
John 01-09-2014 01-29-2014
John 01-09-2014 03-08-2014
What I want to do is select all records by a date like the SQL statement below
SELECT PersonName, DateA, DateB where DateB = '01-29-2014'
However, if there is a record with an earlier date, I do not want that record to be shown. So using the table above only this record should show.
John 01-09-2014 01-29-2014
I have this table
PersonName DateA DateB
Bill 01-09-2014 01-29-2014
Bill 01-09-2014 01-16-2014
Bill 01-09-2014 02-24-2014
John 01-09-2014 02-16-2014
John 01-09-2014 01-29-2014
John 01-09-2014 03-08-2014
What I want to do is select all records by a date like the SQL statement below
SELECT PersonName, DateA, DateB where DateB = '01-29-2014'
However, if there is a record with an earlier date, I do not want that record to be shown. So using the table above only this record should show.
John 01-09-2014 01-29-2014
you can write it with a not exists clause as shown below
In the above code replace<your Table> with your table Name.
SELECT PersonName, DateA, DateB
FROM <Your Table> as T1
where DateB = '01-29-2014'
WHERE NOT EXISTS ( SELECT 1 FROM <your Table> as T2 where T2.DateB < T1.DateB)
In the above code replace<your Table> with your table Name.
I don't get it.
According to what you wrote, that query should give you 2 results:
Bill 01-09-2014 01-29-2014
John 01-09-2014 01-29-2014
Why is the "Bill" row not an acceptable result?
According to what you wrote, that query should give you 2 results:
Bill 01-09-2014 01-29-2014
John 01-09-2014 01-29-2014
Why is the "Bill" row not an acceptable result?
ASKER
Here is a code I came with. Are there any downsides or potential errors?
SELECT RowNumber, PersonName, DateA, DateB
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY PersonName ORDER BY DateB ASC) RowNumber, PersonName, DateA, DateB
FROM DataTest
)
table1
where DateB='01-29-2014' and RowNumber='1'
Try this. You'll need to substitute your table name for #PersonTable
WITH CTE_PersonTable AS (
SELECT
PersonName
FROM #PersonTable
WHERE DateB < CONVERT(DATE, '01-29-2014', 101)
GROUP BY PersonName
)
SELECT
*
FROM #PersonTable
LEFT OUTER JOIN CTE_PersonTable
ON CTE_PersonTable.PersonName = #PersonTable.PersonName
WHERE DateB = CONVERT(DATE, '01-29-2014', 101)
AND CTE_PersonTable.PersonName IS NULL
I suppose its just the way you phrased your question that was a bit confusing but your query should work ok
did you give this a try
as described here https://www.experts-exchange.com/questions/28355426/Show-records-based-on-prior-records.html?anchorAnswerId=39830974#a39830974
SELECT PersonName, DateA, DateB
FROM <Your Table> as T1
where DateB = '01-29-2014'
WHERE NOT EXISTS ( SELECT 1 FROM <your Table> as T2 where T2.DateB < T1.DateB)
as described here https://www.experts-exchange.com/questions/28355426/Show-records-based-on-prior-records.html?anchorAnswerId=39830974#a39830974
ASKER
Hi Surendra:
Yes I did try it. But it does not show any results.
Yes I did try it. But it does not show any results.
SELECT PersonName, DateA, DateB
FROM DataTest as T1
WHERE NOT EXISTS ( SELECT 1 FROM DataTest as T2 where T2.DateB < T1.DateB) AND DateB='2014-01-29'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes it works. However, I had to change T2.DateB < T1.DateB to T2.DateB > T1.DateB. Thank you for your assistance.
oops my mistake... actually it should be some thing like below
declare @t table
(
PersonName VARCHAR(20), dateA DATETIME, DateB DATETIME
)
INSERT INTO @T VALUES ('Bill', '01-09-2014', '01-29-2014')
INSERT INTO @T VALUES ('Bill', '01-09-2014', '01-16-2014')
INSERT INTO @T VALUES ('Bill', '01-09-2014', '02-24-2014')
INSERT INTO @T VALUES ('John', '01-09-2014', '02-16-2014')
INSERT INTO @T VALUES ('John', '01-09-2014', '01-29-2014')
INSERT INTO @T VALUES ('John', '01-09-2014', '03-08-2014')
SELECT T1.*
FROM @T as T1
where T1.DateB = '01-29-2014'
AND NOT EXISTS ( SELECT 1 FROM @T as T2 where T2.DateB < T1.DateB and T1.PersonName = T2.PersonName)
{edit ValentinoV: links to competing site removed}