April 17, 2012 at 1:46 pm
Hello experts,
I need some direction with a simple select statement.
What I would like to get is * records in a group of DCODs where DDATE has a different value.
DCODDNAMEDREFDDATEDVALUE
F5665FRANK200022012-01-01564846161
B6548BOB200122012-01-01465413141
J6598JOHN200162012-01-13151181351
J6598JOHN200172012-01-13564311651
J6598JOHN200262012-01-26456116516
G5661GEORGE200562012-01-13546465454
F5652FRED200752012-01-01456541615
F5652FRED200822012-01-01456464654
So the result should give me all of the records for 'J6598 JOHN' because one of the DDATE values is different.
Don't yell at me.
April 17, 2012 at 1:55 pm
sqlservant (4/17/2012)
So the result should give me all of the records for 'J6598 JOHN' because one of the DDATE values is different.Don't yell at me.
Relax, we save the yelling for the third post. First we lure you in with honey.
If you want tested code you'll want to provide this data in a consumable format, see the first link in my signature for help on that if you're not sure what I mean.
However, I believe a simple statement like the following will do what you need:
SELECT DCOD, DName
FROM
(SELECT DISTINCT DCOD, DName, DDate FROM SomeTable) AS drv
GROUP BY
DCOD, DName
HAVING
COUNT(*) > 1
EDIT: Missed the Group By piece, heh, whoops.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 17, 2012 at 1:58 pm
Evil Kraig F (4/17/2012)
sqlservant (4/17/2012)
So the result should give me all of the records for 'J6598 JOHN' because one of the DDATE values is different.Don't yell at me.
Relax, we save the yelling for the third post. First we lure you in with honey.
If you want tested code you'll want to provide this data in a consumable format, see the first link in my signature for help on that if you're not sure what I mean.
However, I believe a simple statement like the following will do what you need:
SELECT DCOD, DName
FROM
(SELECT DISTINCT DCOD, DName, DDate FROM SomeTable) AS drv
HAVING
COUNT(*) > 1
Actually, he might want the count to be equal to 1. However, that would return 3 records, not just the one he mentioned.
Never mind.
April 17, 2012 at 2:09 pm
Craig,
Thank you for your time. I do not understand your "AS drv" ?
And in response to the second post, I do want all records for that code where one of the DDATEs is different.
I would want to not return the other records.
April 17, 2012 at 2:13 pm
SELECT DCOD, DName
FROM
(SELECT DISTINCT DCOD, DName, DDate FROM SomeTable) AS drv
GROUP BY
DCOD, DName
HAVING
COUNT(*) > 1
The drv above is the alias for the derived table in the outer FROM clause.
April 17, 2012 at 6:55 pm
Lynn's query returns only one row:
DCODDName
J6598JOHN
Which may be what you want. However if you want all of JOHN's records, you can use this:
DECLARE @t TABLE
(DCOD CHAR(5), DNAME VARCHAR(10), DREF INT, DDATE DATETIME, DVALUE VARCHAR(10))
INSERT INTO @t (DCOD, DNAME, DREF, DDATE, DVALUE)
SELECT 'F5665','FRANK',20002,'2012-01-01','564846161'
UNION ALL SELECT 'B6548','BOB',20012,'2012-01-01','465413141'
UNION ALL SELECT 'J6598','JOHN',20016,'2012-01-13','151181351'
UNION ALL SELECT 'J6598','JOHN',20017,'2012-01-13','564311651'
UNION ALL SELECT 'J6598','JOHN',20026,'2012-01-26','456116516'
UNION ALL SELECT 'G5661','GEORGE',20056,'2012-01-13','546465454'
UNION ALL SELECT 'F5652','FRED',20075,'2012-01-01','456541615'
UNION ALL SELECT 'F5652','FRED',20082,'2012-01-01','456464654'
;WITH Rank AS (
SELECT DCOD, DNAME, DREF, DDATE, DVALUE
,RANK() OVER (PARTITION BY DCOD, DName ORDER BY DCOD, DName, DDate) As Rank
FROM @t),
Dups AS (
SELECT * FROM Rank
WHERE Rank > 1)
SELECT * FROM @t t
INNER JOIN Dups p ON t.DCOD = p.DCOD and t.DNAME = p.DNAME
Edited to include my DDL.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 17, 2012 at 6:59 pm
dwain.c (4/17/2012)
Lynn's query returns only one row:
DCODDNameJ6598JOHN
Which may be what you want. However if you want all of JOHN's records, you can use this:
DECLARE @t TABLE
(DCOD CHAR(5), DNAME VARCHAR(10), DREF INT, DDATE DATETIME, DVALUE VARCHAR(10))
INSERT INTO @t (DCOD, DNAME, DREF, DDATE, DVALUE)
SELECT 'F5665','FRANK',20002,'2012-01-01','564846161'
UNION ALL SELECT 'B6548','BOB',20012,'2012-01-01','465413141'
UNION ALL SELECT 'J6598','JOHN',20016,'2012-01-13','151181351'
UNION ALL SELECT 'J6598','JOHN',20017,'2012-01-13','564311651'
UNION ALL SELECT 'J6598','JOHN',20026,'2012-01-26','456116516'
UNION ALL SELECT 'G5661','GEORGE',20056,'2012-01-13','546465454'
UNION ALL SELECT 'F5652','FRED',20075,'2012-01-01','456541615'
UNION ALL SELECT 'F5652','FRED',20082,'2012-01-01','456464654'
;WITH Rank AS (
SELECT DCOD, DNAME, DREF, DDATE, DVALUE
,RANK() OVER (PARTITION BY DCOD, DName ORDER BY DCOD, DName, DDate) As Rank
FROM @t),
Dups AS (
SELECT * FROM Rank
WHERE Rank > 1)
SELECT * FROM @t t
INNER JOIN Dups p ON t.DCOD = p.DCOD and t.DNAME = p.DNAME
Edited to include my DDL.
FYI, not my code. Copied from Evil Kraig F to comment on what drv in code meant.
April 17, 2012 at 7:15 pm
Lynn - Oops! You are right.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 18, 2012 at 11:53 am
Thank you all for your time and suggestions.
In the end I didn't want to go this far so I used your suggestions and simplified it to just return values as a report.
SELECT DCOD, DNAME, DREF, DDATE, DVALUE
FROM SomeTable WHERE DCOD IN (
SELECT DCOD
FROM SomeTable
GROUP BY DCOD
HAVING COUNT (DISTINCT DDATE) >1)
You guys are the best at what you do.
Thanks again! I was really stuck on this.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply