September 23, 2010 at 1:59 pm
I have a table diagramed as follows:
UserName ColumnA ColumnB Date/Time
Tom 1000 10 2010-09-20
Tom 1050 11 2010-09-21
Dick 19 2 2010-09-20
Dick 19 2 2010-09-21
Harry 11018 119 2010-09-20
Harry 10832 107 2010-09-21
What I would like is a way to compare the difference of columnA between the two records with the same name. My thoughts are that I am going to need to look at the date column in my query but I am not certain.
Any help is greatly appreciated.
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
September 23, 2010 at 2:18 pm
chris.s.powell (9/23/2010)
I have a table diagramed as follows:UserName ColumnA ColumnB Date/Time
Tom 1000 10 2010-09-20
Tom 1050 11 2010-09-21
Dick 19 2 2010-09-20
Dick 19 2 2010-09-21
Harry 11018 119 2010-09-20
Harry 10832 107 2010-09-21
What I would like is a way to compare the difference of columnA between the two records with the same name. My thoughts are that I am going to need to look at the date column in my query but I am not certain.
Assuming there are always two rows for each UserName and also assuming each row always have a different DateTime value I would do something like pseudo-code below...
select a.UserName,
a.ColumnA as 0920,
b.ColumnA as 0921,
(a.ColumnA - b.ColumnA) as DIFF
from MyTable a,
MyTable b
where a.UserName = b.UserName
and a.DateTime = '2010-09-20'
and b.DateTime = '2010-09-21';
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 23, 2010 at 2:28 pm
Thank you Paul, so I am using a join on the same table (is that what I am reading). I was thinking I would have to use a group.
Thank you
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
September 23, 2010 at 8:39 pm
Here is another way of doing what I think you want to do
CREATE TABLE #T(UserName VARCHAR(10), ColumnA INT, ColumnB INT, DT DATETIME)
INSERT INTO #T
SELECT 'Tom',1000, 10,'2010-09-20' UNION ALL
SELECT 'Tom', 1050, 11,'2010-09-21' UNION ALL
SELECT 'Dick', 19, 2, '2010-09-20' UNION ALL
SELECT 'Dick', 19, 2, '2010-09-21' UNION ALL
SELECT 'Harry', 11018, 119,'2010-09-20' UNION ALL
SELECT 'Harry', 10832, 107,'2010-09-21'
Using:
;WITH
cteDupeName AS
(SELECT UserName,ColumnA FROM #T GROUP BY UserName,ColumnA
HAVING COUNT(*) = 1)
SELECT source.UserName, source.ColumnA,ColumnB,DT
FROM #T source
--INNER
RIGHT JOIN cteDupeName dupe --understand this is the key making it all work
ON source.UserName = dupe.UserName AND Source.ColumnA < > dupe.ColumnA
ORDER BY source.UserName;
Result: Notice "Dick" is not in the list, although entered twice, each entry for "Dick" has the same value for ColumnA
UserName ColumnAColumnB DT
Harry 110181192010-09-20 00:00:00.000
Harry 108321072010-09-21 00:00:00.000
Tom 1050 112010-09-21 00:00:00.000
Tom 1000 102010-09-20 00:00:00.000
Now if you want to find multiple entries for a UserName with the same value in ColumnA this code should do it.
;with
numbered as(SELECT rowno=row_number() over
(partition by UserName,ColumnA order by UserName,DT DESC),
UserName,ColumnA,ColumnB,DT FROM #T)
select * from numbered
Result: Note "Dick" has 2 rows in the results
rownoUserNameColumnAColumnB DT
1Dick 19 22010-09-21 00:00:00.000
2Dick 19 22010-09-20 00:00:00.000
1Harry 108321072010-09-21 00:00:00.000
1Harry 110181192010-09-20 00:00:00.000
1Tom 1000 102010-09-20 00:00:00.000
1Tom 1050 112010-09-21 00:00:00.000
Now for something even more powerful - say your boss wants the duplicate entries deleted. Then this will do it.
;with
numbered as(SELECT rowno=row_number() over
(partition by UserName,ColumnA order by UserName,DT
DESC),UserName,ColumnA,ColumnB,DT FROM #T)
DELETE FROM numbered WHERE rowno > 1
Result of above:
SELECT * FROM #T
UserNameColumnAColumnB DT
Tom 1000102010-09-20 00:00:00.000
Tom 1050112010-09-21 00:00:00.000
Dick 19 22010-09-21 00:00:00.000
Harry 110181192010-09-20 00:00:00.000
Harry 108321072010-09-21 00:00:00.000
September 24, 2010 at 4:11 pm
If there can only be two rows per name, and you just want the difference between ColumnA's, you can do it very simply:
SELECT UserName,
MAX(ColumnA) - MIN(ColumA) AS Difference
FROM tablename
GROUP BY UserName
HAVING COUNT(*) = 2 AND MAX(ColumA) - MIN(ColumnA) > 0
This does only one pass of the input table.
Technically don't need the "COUNT(*) = 2" but it may add some clarity.
Scott Pletcher, SQL Server MVP 2008-2010
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply