Help on a query
-
I've the following data
Name Date Trend Strength
A 01-Jul-2011 Buy Mid
A 05-Jul-2011 Buy Mid
A 08-Jul-2011 Buy Mid
A 10-Jul-2011 Sell Mid
A 12-Jul-2011 Sell Mid
A 15-Jul-2011 Sell High
B 01-Jul-2011 Sell Mid
B 02-Jul-2011 Sell Mid
B 05-Jul-2011 Sell High
I want to retrieve only those dates for a name where the "Trend" or the "Strength" changes
In the above example, for A the trend and strenght stays the same till the 08 of July. On the 10th the Trend changes and on the 15th the Strength changes. so the query should
give me an output of 01-Jul-2011, 10-Jul-2011 and 15-Jul-2011
so and so forth for B too..
ANy help will be appreciated
-
Check if below helps you, you can make use of CTE too, but in order to keep it simple I have used an inline query.
DECLARE @tbl TABLE (Name Varchar(10), Date Datetime, Trend varchar(10), Strength varchar(10))
insert into @tbl values('A', '01-Jul-2011', 'Buy', 'Mid')
insert into @tbl values('A', '05-Jul-2011', 'Buy', 'Mid')
insert into @tbl values('A', '08-Jul-2011', 'Buy', 'Mid')
insert into @tbl values('A', '10-Jul-2011', 'Sell', 'Mid')
insert into @tbl values('A', '12-Jul-2011', 'Sell', 'Mid')
insert into @tbl values('A', '15-Jul-2011', 'Sell', 'High')
insert into @tbl values('B', '01-Jul-2011', 'Sell', 'Mid')
insert into @tbl values('B', '02-Jul-2011', 'Sell', 'Mid')
insert into @tbl values('B', '05-Jul-2011', 'Sell', 'High')
SELECT
*
FROM
(
SELECT
*, ROW_NUMBER() OVER (PARTITION BY Trend, Strength, NAME ORDER BY NAME) AS Sequence
FROM
@tbl
) T
WHERE Sequence = 1
-
Thanks for the reply. However this fails with the following data
DECLARE @tbl TABLE (Name Varchar(10), Date Datetime, Trend varchar(10), Strength varchar(10))
insert into @tbl values('A', '01-Jul-2011', 'Buy', 'Mid')
insert into @tbl values('A', '02-Jul-2011', 'Buy', 'Mid')
insert into @tbl values('A', '03-Jul-2011', 'Buy', 'Mid')
insert into @tbl values('A', '04-Jul-2011', 'Sell', 'Mid')
insert into @tbl values('A', '05-Jul-2011', 'Sell', 'Mid')
insert into @tbl values('A', '06-Jul-2011', 'Sell', 'High')
insert into @tbl values('B', '07-Jul-2011', 'Sell', 'Mid')
insert into @tbl values('B', '08-Jul-2011', 'Sell', 'Mid')
insert into @tbl values('A', '09-Jul-2011', 'Buy', 'Mid')
SELECT
name, date, trend, strength
FROM
(
SELECT
*, ROW_NUMBER() OVER (PARTITION BY Trend, Strength, NAME ORDER BY NAME) AS Sequence
FROM
@tbl
) T
WHERE Sequence = 1
order by name, date
In the output the 9th of Jul for "A" should also appear. However this doesnt come.
-
Just a small correction to shaileshag2002’s code. The order by clause in the row_number function should be based on the column Date and not on the column Name. If you’ll use the column Name, you might get the wrong data.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Thanks. Even this does not give the correct output.
Because for "A", the Trend again changes on the 9th. and this is not reflected in the output
-
Ok, if your requirement is to get data as mentioned above then try below query.
DECLARE @tbl TABLE (Id int identity(1, 1), Name Varchar(100), Date Datetime, Trend varchar(10), Strength varchar(10))
insert into @tbl values('A', '01-Jul-2011', 'Buy', 'Mid')
insert into @tbl values('A', '02-Jul-2011', 'Buy', 'Mid')
insert into @tbl values('A', '03-Jul-2011', 'Buy', 'Mid')
insert into @tbl values('A', '04-Jul-2011', 'Sell', 'Mid')
insert into @tbl values('A', '05-Jul-2011', 'Sell', 'Mid')
insert into @tbl values('A', '06-Jul-2011', 'Sell', 'High')
insert into @tbl values('B', '07-Jul-2011', 'Sell', 'Mid')
insert into @tbl values('B', '08-Jul-2011', 'Sell', 'Mid')
insert into @tbl values('A', '09-Jul-2011', 'Buy', 'Mid')
insert into @tbl values('A', '09-Jul-2011', 'Sell', 'Mid')
insert into @tbl values('B', '09-Jul-2011', 'Sell', 'Mid')
;WITH Data AS
(
SELECT
ID, NAME AS ActualName, CONVERT(VARCHAR(100), Name + CAST(ID AS VARCHAR)) AS Name,
CONVERT(VARCHAR(100), Name + CAST(ID AS VARCHAR)) AS Name2, Date, Trend, Strength
FROM
@tbl
WHERE
ID = 1
UNION ALL
SELECT
T.id,T.NAME AS ActualName,
CONVERT(VARCHAR(100), CASE WHEN T.Name + CAST(D.ID AS VARCHAR) = D.Name2 THEN D.Name ELSE T.Name + CAST(T.id AS VARCHAR) END) AS NAME,
CONVERT(VARCHAR(100), CASE WHEN D.Name + CAST(D.ID AS VARCHAR) = T.Name + CAST(D.id AS VARCHAR) THEN D.Name + CAST(T.ID AS VARCHAR) ELSE T.Name + CAST(T.ID AS VARCHAR) END) AS Name2,
T.Date, T.Trend, T.Strength
FROM @tbl T
INNER JOIN Data D ON T.id = D.id + 1
)
,Operation AS
(
SELECT
ID, ActualName AS Name, Date, Trend, Strength, ROW_NUMBER() OVER (PARTITION BY Trend, Strength, NAME ORDER BY ID asc) AS Sequence
FROM
Data
)
SELECT
Name, Date, Trend, Strength, Sequence
FROM
Operation
WHERE
Sequence = 1
ORDER BY ID
-
Here's another way
WITH CTE AS (SELECT Name,Date,Trend,Strength,
ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Date) -
ROW_NUMBER() OVER(PARTITION BY Name,Trend,Strength ORDER BY Date) AS rnDiff
FROM @tbl)
SELECT Name,MIN(Date) AS Date,Trend,Strength
FROM CTE
GROUP BY Name,Trend,Strength,rnDiff
ORDER BY Name,Date;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
This is a good way to solve this problem but, it is not giving proper output in all the cases. Execute both the queries with the test data provided above we are not getting same number of records.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply