August 25, 2015 at 10:59 pm
I have a scenario as below for one ID -
+------+--------+----------------------------+-------+
| id | amount | date | descr|
+------+--------+-----------------------------+------+
| 5689 | 10.00 | 2015-08-25 12:10:57.107 | 4 |
| 5689 | 10.00 | 2015-08-24 12:07:57.107 | 3 |
| 5689 | 10.00 | 2015-08-25 12:05:57.107 | 3 |
| 5689 | 130.00 | 2015-08-24 12:07:57.107 | 4 |
| 5689 | 130.00 | 2015-08-25 12:07:57.107 | 3 |
+------+--------+-----------------------------+-----+
I want to fetch below 3 records from the above scenario i.e. latest record of each amount (Latest is determined using "descr" column i.e. 4 is greater then 3 -
+------+--------+----------------------------+-------+
| id | amount | date | descr|
+------+--------+-----------------------------+------+
| 5689 | 10.00 | 2015-08-25 12:10:57.107 | 4 |
| 5689 | 10.00 | 2015-08-24 12:07:57.107 | 3 |
| 5689 | 130.00 | 2015-08-24 12:07:57.107 | 4 |
+------+--------+-----------------------------+-----+
But in case of same amounts I am unable to fetch the latest status as even using partitioning will treat them as one.
Please help.
CREATE TABLE #TMP
(
ID INT,
AMOUNT DECIMAL,
[DATE] DATETIME,
DESCR VARCHAR(10)
)
INSERT INTO #TMP VALUES
(5689,10.00,'2015-08-25 12:10:57.107','4')
,(5689,10.00,'2015-08-24 12:07:57.107','3')
,(5689,10.00,'2015-08-25 12:05:57.107','3')
,(5689,130.00,'2015-08-24 12:07:57.107','4')
,(5689,130.00,'2015-08-25 12:07:57.107','3')
____________________________________________________________
APAugust 26, 2015 at 1:44 am
Quick thought, the description of the logic does not fit the expected results, can you please amend either or both?
😎
August 26, 2015 at 1:54 am
Eirikur Eiriksson (8/26/2015)
Quick thought, the description of the logic does not fit the expected results, can you please amend either or both?😎
Actually the data comes in sets.
1 set contains two descr - 3 and 4
i.e.
amount | descr
10 | 4
10 | 3
First there's an entry for descr-3 then a new entry comes for descr-4. It's like a process and these are the stages of the process.
So, what I need is - for a single amount I need the latest stage.
But, the problem arises if the amount is same, I don't have anything to differentiate, so randomly I need to fetch the latest stages of all amounts.
____________________________________________________________
APAugust 26, 2015 at 3:57 am
+------+--------+----------------------------+-------+
| id | amount | date | descr|
+------+--------+-----------------------------+------+
| 5689 | 10.00 | 2015-08-25 12:10:57.107 | 4 |
| 5689 | 10.00 | 2015-08-24 12:07:57.107 | 3 |
| 5689 | 130.00 | 2015-08-24 12:07:57.107 | 4 |
+------+--------+-----------------------------+-----+
can you please explain in more detail, why you wish to return two rows for amount 10.00 but only one row for amount 130.00 ?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 26, 2015 at 5:13 am
This is because.
One process for amount 10 was started @ 3 and ended @ 4.
But, for another amount 10 process started @3 and has not ended yet. So the latest status would be 3 for one record of 10.
____________________________________________________________
APAugust 26, 2015 at 8:07 am
So how do we know that the 10 amount with a 4 is the end of which of the 2 10 transaction with a 3?
I don't think there's enough info, unless it doesn't matter in which case just arbitrarily pick the latest one?
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
August 26, 2015 at 8:23 am
This might work if you can arbitrarily pick the matching sets... what happens when the other 4 comes in for the 10 amount? Do you show 2 10 Descr 4?
declare @Tmp as table(
ID INT,
AMOUNT DECIMAL,
[DATE] DATETIME,
DESCR VARCHAR(10)
)
INSERT INTO @Tmp VALUES
(5689,10.00,'2015-08-25 12:10:57.107','4')
,(5689,10.00,'2015-08-24 12:07:57.107','3')
,(5689,10.00,'2015-08-25 12:05:57.107','3')
,(5689,130.00,'2015-08-24 12:07:57.107','4')
,(5689,130.00,'2015-08-25 12:07:57.107','3')
;with Desc4 as(select ID
,AMOUNT
,[DATE]
,[DESCR]
from @Tmp t1
where [DESCR] = '4'),
Desc3 as(select ID
,AMOUNT
,DESCR
,MAX([DATE]) MDate
from @Tmp t2
where [DESCR] = '3'
group by ID, AMOUNT,DESCR
having COUNT(*) > 1)
select ID, AMOUNT, [DATE], DESCR
from Desc4
Union ALL
select t.ID, t.AMOUNT, t.[DATE], t.DESCR
from @Tmp t
inner join Desc3 d3
on t.ID = d3.ID
and t.AMOUNT = d3.AMOUNT
and t.DATE = d3.MDate
order by ID, AMOUNT, DESCR DESC
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
August 26, 2015 at 9:30 pm
Keeping amount as individual sets
AMOUNT | STAGE
10 | 4 } 1st SET
10 | 3 } 1st SET
10 | 4 } 2nd SET
10 | 3 } 2nd SET
130 | 4 } 3rd SET
130 | 3 } 3rd SET
And, yes if another entry for 10 comes with stage 4 then I want to show the two latest values. i.e. two 10's at stage 4.
____________________________________________________________
APAugust 26, 2015 at 9:41 pm
I've created this query, it is giving me desired results, but I am not sure if it is correct.
;WITH CTE AS
(
SELECT *
,NTILE(2) OVER (PARTITION BY id,rn ORDER BY descr DESC) rn4
FROM (SELECT *
, DENSE_RANK() OVER (PARTITION BY id ORDER BY amount DESC) rn
FROM #tmp
)a
)
SELECT *
FROM CTE
WHERE rn4 = 1
____________________________________________________________
APViewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply