May 14, 2008 at 3:18 am
I have table with Two columns. i.e TIME and ACTION. IT looks like this
TIME ACTION
12:40:01 BUY
12:40:31 BUY
12:40:51 BUY
12:41:01 SELL
12:41:21 SELL
12:41:31 SELL
12:41:41 SELL
12:41:51 BUY
12:42:01 BUY
12:42:29 SELL
12:42:31 SELL
12:42:41 SELL
12:42:51 BUY
I want to remove rows whenever ACTION column with Repetative values.
After that it should display like this
TIME ACTION
12:40:01 BUY
12:41:01 SELL
12:41:51 BUY
12:42:29 SELL
12:42:51 BUY
Please help me anyone
May 14, 2008 at 3:31 am
Already asked and answered here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102805
N 56°04'39.16"
E 12°55'05.25"
May 15, 2008 at 8:42 am
How about this:
DECLARE @Actions TABLE
(
ActionTime DATETIME
,ActionName VARCHAR(10)
)
INSERT INTO @Actions
SELECT '12:40:01' ,'BUY' UNION ALL
SELECT '12:40:31' ,'BUY' UNION ALL
SELECT '12:40:51' ,'BUY' UNION ALL
SELECT '12:41:01' ,'SELL' UNION ALL
SELECT '12:41:21' ,'SELL' UNION ALL
SELECT '12:41:31' ,'SELL' UNION ALL
SELECT '12:41:41' ,'SELL' UNION ALL
SELECT '12:41:51' ,'BUY' UNION ALL
SELECT '12:42:01' ,'BUY' UNION ALL
SELECT '12:42:29' ,'SELL' UNION ALL
SELECT '12:42:31' ,'SELL' UNION ALL
SELECT '12:42:41' ,'SELL' UNION ALL
SELECT '12:42:51' ,'BUY' UNION ALL
SELECT '01:42:51' ,'BUY'
SELECT SUBSTRING(CONVERT(VARCHAR(20), A.ActionTime, 108), 1, 5) AS ActionTime
,ActionName
FROM @Actions A
GROUP BY SUBSTRING(CONVERT(VARCHAR(20), A.ActionTime, 108), 1, 5)
, ActionName
If you need to take into account the date as well as the time try this:
DECLARE @Actions TABLE
(
ActionTime DATETIME
,ActionName VARCHAR(10)
)
INSERT INTO @Actions
SELECT '01/02/2008 12:40:01' ,'BUY' UNION ALL
SELECT '01/02/2008 12:40:31' ,'BUY' UNION ALL
SELECT '01/02/2008 12:40:51' ,'BUY' UNION ALL
SELECT '01/02/2008 12:41:01' ,'SELL' UNION ALL
SELECT '01/03/2008 12:41:21' ,'SELL' UNION ALL
SELECT '01/03/2008 12:41:31' ,'SELL' UNION ALL
SELECT '01/02/2008 12:41:41' ,'SELL' UNION ALL
SELECT '01/02/2008 12:41:51' ,'BUY' UNION ALL
SELECT '01/02/2008 12:42:01' ,'BUY' UNION ALL
SELECT '01/02/2008 12:42:29' ,'SELL' UNION ALL
SELECT '01/02/2008 12:42:31' ,'SELL' UNION ALL
SELECT '01/02/2008 12:42:41' ,'SELL' UNION ALL
SELECT '01/02/2008 12:42:51' ,'BUY' UNION ALL
SELECT '01/02/2008 01:42:51' ,'BUY'
SELECT CONVERT(VARCHAR(20), A.ActionTime, 112) AS ActionDate
,SUBSTRING(CONVERT(VARCHAR(20), A.ActionTime, 108), 1, 5) AS ActionTime
,ActionName
FROM @Actions A
GROUP BY CONVERT(VARCHAR(20), A.ActionTime, 112)
,SUBSTRING(CONVERT(VARCHAR(20), A.ActionTime, 108), 1, 5)
, ActionName
May 15, 2008 at 9:01 am
I'm hoping you screwed up your sample again, because if you didn't - you're SOL. (you'd be wanting us to rely on "physical order" to break these things, which isn't something that has any "meaning" in a table on SQL server).
On the other hand - if you're trying to find the changes in actions based on the time component, you could try this (reusing ggraber's test setup):
;with cte1 as (
select ROW_NUMBER() over (order by ActionTime) as rn,
ActionTime,
ActionName
from @actions)
select c1.ActionTime, c1.actionName
from cte1 c1
left outer join cte1 c2 on c1.rn=c2.rn-1
where c1.actionName <> isnull(c2.actionName,'')
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 15, 2008 at 9:21 am
Matt, your example misses a row.
My example returns one row for each minute, yours misses one minute.
Your Results:
ActionTimeactionName
1900-01-01 12:40:51.000BUY
1900-01-01 12:41:41.000SELL
1900-01-01 12:42:01.000BUY
1900-01-01 12:42:41.000SELL
1900-01-01 12:42:51.000BUY
My Results:
ActionTimeActionName
01:42BUY
12:40BUY
12:41BUY
12:41SELL
12:42BUY
12:42SELL
May 15, 2008 at 9:30 am
hmm - that's strange - because I get 6 rows, not 5.
ActionTimeactionName
2008-01-02 12:40:51.000BUY
2008-01-02 12:41:41.000SELL
2008-01-02 12:42:01.000BUY
2008-01-02 12:42:41.000SELL
2008-01-02 12:42:51.000BUY
2008-01-03 12:41:31.000SELL
It might make a difference that your SECOND definition of actions returns different results than the first.....
Also - sameer isn't doing us any favors here, since my understanding is that he only wants to know when the actions "switch" from buy to sell and back, and not have repeating BUY rows (like in your solution). That's not so much a "miss" as a "on purpose". I don't "care" about the minutes at all.
Again - without knowing more details, hard to tell which is supposed to be "right".
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply