Hi
How I can get below result by removing the duplicates.
|
Price
|
Code
|
PriceNow
|
|
10
|
AA
|
3
|
|
15
|
BB
|
7
|
|
4
|
CC
|
1
|
DECLARE @Person AS TABLE(Id INT,Name VARCHAR(10))
INSERT INTO @Person VALUES(1,'Rustam')
INSERT INTO @Person VALUES(2,'Asror')
DECLARE @mainTable AS TABLE(Id INT,PersonId int,Price INT, Code VARCHAR(2))
INSERT INTO @mainTable VALUES(1,1,10,'AA')
INSERT INTO @mainTable VALUES(2,1,15,'BB')
INSERT INTO @mainTable VALUES(3,1,4,'CC')
INSERT INTO @mainTable VALUES(4,2,10,'AA')
INSERT INTO @mainTable VALUES(5,2,17,'BB')
INSERT INTO @mainTable VALUES(6,2,4,'CC')
DECLARE @Detail AS TABLE(Id INT, PersonId int,Price INT, PriceNow FLOAT,Code VARCHAR(2))
INSERT INTO @Detail VALUES(1,1,9, 4,'AA')
INSERT INTO @Detail VALUES(2,1,8, 0,'AA')
INSERT INTO @Detail VALUES(3,1,7, 0,'AA')
INSERT INTO @Detail VALUES(4,1,6, 3,'AA')
INSERT INTO @Detail VALUES(6,1,15, 0,'BB')
INSERT INTO @Detail VALUES(7,1,14, 0,'BB')
INSERT INTO @Detail VALUES(8,1,13, 7,'BB')
INSERT INTO @Detail VALUES(9,2,4, 0,'CC')
INSERT INTO @Detail VALUES(10,2,5, 2,'CC')
INSERT INTO @Detail VALUES(11,1,7, 1,'CC')
select m.Price, d.Code, (select top 1 CASE WHEN x.Price <> 0 AND x.PriceNow = 0 THEN x.Price
WHEN x.Price <> 0 AND x.PriceNow <> 0 THEN x.PriceNow
END FROM @Detail x WHERE m.Code = x.Code ORDER BY x.Id DESC) as PriceNow from @mainTable m
inner join @Detail d on m.Code = d.Code
join @Person p on p.Id = d.PersonId where m.PersonId = 1
