April 28, 2015 at 8:39 am
Hello experts,
I want to show a resultset from the table mentioned below that only shows the longest range. In this example we talk about archery, an olympic event that has been won by the Korean ladies since the introduction in 1988. This means that the socalled streak is 7 (1988, 1992, 1996, 2000, 2004, 2008 and 2012).
The result should show the streak, sport, event, gender, team/country, first year of victory and last year of victory. When there is one year with no victory the streak should be set to 0 and the counting starts all over again.
The column 'number' says that if the year before that country won it is 1, otherwise it is NULL. A solution with and without the column 'Number' would be appreciated.
The data:
IF OBJECT_ID('TempDB..#mySeries','U') IS NOT NULL
DROP TABLE #mySeries
CREATE TABLE #mySeries
(
ID INT PRIMARY KEY CLUSTERED,
Number nvarchar(5),
Sport nvarchar(max),
Event nvarchar(max),
Gender nvarchar(10),
Country nvarchar(max),
Year_From INT,
Year_To INT
)
INSERT INTO #mySeries (ID, Number, Sport, Event, Gender, Country, Year_From, Year_To)
SELECT '1', '1', 'Archery', 'Individual', 'Male','United States', '1972','1972' UNION ALL
SELECT '2', '1', 'Archery','Individual','Male', 'United States', '1976','1976' UNION ALL
SELECT '3', '1', 'Archery','Individual','Male', 'United States', '1988', '1988' UNION ALL
SELECT '4', '1', 'Archery','Individual','Female', 'South Korea', '1988', '1988' UNION ALL
SELECT '5', '1', 'Archery','Individual','Female', 'South Korea', '1992','1992' UNION ALL
SELECT '6', '1', 'Archery','Individual','Female', 'South Korea', '1996','1996' UNION ALL
SELECT '7', '1', 'Archery','Individual','Female', 'South Korea', '2000','2000' UNION ALL
SELECT '8', '1', 'Archery','Individual','Female', 'South Korea', '2004','2004' UNION ALL
SELECT '9', '1', 'Archery','Individual','Female', 'United States', '1972','1972' UNION ALL
SELECT '10', '1', 'Archery', 'Individual','Female', 'United States', '1976','1976' UNION ALL
SELECT '11', 'NULL', 'Archery','Individual','Female', 'Soviet Union','1980','1980' UNION ALL
SELECT '12', 'NULL', 'Archery','Individual','Female', 'South Korea', '1984','1984' UNION ALL
SELECT '13', 'NULL', 'Archery','Individual','Female', 'China', '2008','2008' UNION ALL
SELECT '14', 'NULL', 'Archery','Individual','Female', 'South Korea', '2012','2012' UNION ALL
SELECT '15', 'NULL', 'Archery','Individual','Male', 'France','1992','1992' UNION ALL
SELECT '16', 'NULL', 'Archery','Individual','Male', 'United States', '1996','1996' UNION ALL
SELECT '17', 'NULL', 'Archery','Individual','Male', 'Australia', '2000','2000' UNION ALL
SELECT '18','NULL', 'Archery','Individual','Male', 'Italy','2004','2004' UNION ALL
SELECT '19', 'NULL', 'Archery','Individual','Male', 'Ukraine','2008','2008' UNION ALL
SELECT '20', 'NULL', 'Archery','Individual','Male', 'South Korea', '2012','2012' UNION ALL
SELECT '21', 'NULL', 'Archery','Individual','Male', 'Finland','1980','1980' UNION ALL
SELECT '22', 'NULL', 'Archery','Individual','Male', 'United States', '1984','1984'UNION ALL
SELECT '23', '1','Archery','Teams', 'Male', 'South Korea', '1988', '1988' UNION ALL
SELECT '24', '1','Archery','Teams', 'Female', 'South Korea', '1988', '1988' UNION ALL
SELECT '25', '1','Archery','Teams', 'Female', 'South Korea', '1992','1992' UNION ALL
SELECT '26', '1','Archery','Teams', 'Female', 'South Korea', '1996','1996' UNION ALL
SELECT '27', '1','Archery','Teams', 'Female', 'South Korea', '2000','2000' UNION ALL
SELECT '28', '1','Archery','Teams', 'Female', 'South Korea', '2004','2004' UNION ALL
SELECT '29', '1','Archery', 'Teams', 'Female', 'South Korea', '2008','2008' UNION ALL
SELECT '30', '1','Archery', 'Teams', 'Female', 'South Korea', '2012','2012' UNION ALL
SELECT '31', '1','Archery', 'Teams', 'Male', 'South Korea', '2004','2004' UNION ALL
SELECT '32', '1','Archery', 'Teams', 'Male', 'South Korea', '2008','2008' UNION ALL
SELECT '33', 'NULL','Archery', 'Teams', 'Male', 'Italy','2012','2012' UNION ALL
SELECT '34', 'NULL','Archery', 'Teams', 'Male', 'Spain','1992','1992' UNION ALL
SELECT '35', 'NULL','Archery', 'Teams', 'Male', 'United States', '1996','1996' UNION ALL
SELECT '36', 'NULL','Archery', 'Teams', 'Male', 'South Korea', '2000','2000'
GO
Any help is very much appreciated.
Kind regards,
Robert
April 28, 2015 at 10:28 am
r_slot (4/28/2015)
Hello experts,I want to show a resultset from the table mentioned below that only shows the longest range. In this example we talk about archery, an olympic event that has been won by the Korean ladies since the introduction in 1988. This means that the socalled streak is 7 (1988, 1992, 1996, 2000, 2004, 2008 and 2012).
The result should show the streak, sport, event, gender, team/country, first year of victory and last year of victory. When there is one year with no victory the streak should be set to 0 and the counting starts all over again.
The column 'number' says that if the year before that country won it is 1, otherwise it is NULL. A solution with and without the column 'Number' would be appreciated.
The data:
IF OBJECT_ID('TempDB..#mySeries','U') IS NOT NULL
DROP TABLE #mySeries
CREATE TABLE #mySeries
(
ID INT PRIMARY KEY CLUSTERED,
Number nvarchar(5),
Sport nvarchar(max),
Event nvarchar(max),
Gender nvarchar(10),
Country nvarchar(max),
Year_From INT,
Year_To INT
)
INSERT INTO #mySeries (ID, Number, Sport, Event, Gender, Country, Year_From, Year_To)
SELECT '1', '1', 'Archery', 'Individual', 'Male','United States', '1972','1972' UNION ALL
SELECT '2', '1', 'Archery','Individual','Male', 'United States', '1976','1976' UNION ALL
SELECT '3', '1', 'Archery','Individual','Male', 'United States', '1988', '1988' UNION ALL
SELECT '4', '1', 'Archery','Individual','Female', 'South Korea', '1988', '1988' UNION ALL
SELECT '5', '1', 'Archery','Individual','Female', 'South Korea', '1992','1992' UNION ALL
SELECT '6', '1', 'Archery','Individual','Female', 'South Korea', '1996','1996' UNION ALL
SELECT '7', '1', 'Archery','Individual','Female', 'South Korea', '2000','2000' UNION ALL
SELECT '8', '1', 'Archery','Individual','Female', 'South Korea', '2004','2004' UNION ALL
SELECT '9', '1', 'Archery','Individual','Female', 'United States', '1972','1972' UNION ALL
SELECT '10', '1', 'Archery', 'Individual','Female', 'United States', '1976','1976' UNION ALL
SELECT '11', 'NULL', 'Archery','Individual','Female', 'Soviet Union','1980','1980' UNION ALL
SELECT '12', 'NULL', 'Archery','Individual','Female', 'South Korea', '1984','1984' UNION ALL
SELECT '13', 'NULL', 'Archery','Individual','Female', 'China', '2008','2008' UNION ALL
SELECT '14', 'NULL', 'Archery','Individual','Female', 'South Korea', '2012','2012' UNION ALL
SELECT '15', 'NULL', 'Archery','Individual','Male', 'France','1992','1992' UNION ALL
SELECT '16', 'NULL', 'Archery','Individual','Male', 'United States', '1996','1996' UNION ALL
SELECT '17', 'NULL', 'Archery','Individual','Male', 'Australia', '2000','2000' UNION ALL
SELECT '18','NULL', 'Archery','Individual','Male', 'Italy','2004','2004' UNION ALL
SELECT '19', 'NULL', 'Archery','Individual','Male', 'Ukraine','2008','2008' UNION ALL
SELECT '20', 'NULL', 'Archery','Individual','Male', 'South Korea', '2012','2012' UNION ALL
SELECT '21', 'NULL', 'Archery','Individual','Male', 'Finland','1980','1980' UNION ALL
SELECT '22', 'NULL', 'Archery','Individual','Male', 'United States', '1984','1984'UNION ALL
SELECT '23', '1','Archery','Teams', 'Male', 'South Korea', '1988', '1988' UNION ALL
SELECT '24', '1','Archery','Teams', 'Female', 'South Korea', '1988', '1988' UNION ALL
SELECT '25', '1','Archery','Teams', 'Female', 'South Korea', '1992','1992' UNION ALL
SELECT '26', '1','Archery','Teams', 'Female', 'South Korea', '1996','1996' UNION ALL
SELECT '27', '1','Archery','Teams', 'Female', 'South Korea', '2000','2000' UNION ALL
SELECT '28', '1','Archery','Teams', 'Female', 'South Korea', '2004','2004' UNION ALL
SELECT '29', '1','Archery', 'Teams', 'Female', 'South Korea', '2008','2008' UNION ALL
SELECT '30', '1','Archery', 'Teams', 'Female', 'South Korea', '2012','2012' UNION ALL
SELECT '31', '1','Archery', 'Teams', 'Male', 'South Korea', '2004','2004' UNION ALL
SELECT '32', '1','Archery', 'Teams', 'Male', 'South Korea', '2008','2008' UNION ALL
SELECT '33', 'NULL','Archery', 'Teams', 'Male', 'Italy','2012','2012' UNION ALL
SELECT '34', 'NULL','Archery', 'Teams', 'Male', 'Spain','1992','1992' UNION ALL
SELECT '35', 'NULL','Archery', 'Teams', 'Male', 'United States', '1996','1996' UNION ALL
SELECT '36', 'NULL','Archery', 'Teams', 'Male', 'South Korea', '2000','2000'
GO
Any help is very much appreciated.
Kind regards,
Robert
Sounds like a good place for a CTE where you first find the range by sport. I would suggest using a date table with the years defined.
What have you tried and has not worked?
April 28, 2015 at 10:44 am
I tried almost everything (including a cte) but nothing worked. The problem is that a range that is discontinued (eg Korea female individual) I cannot get the code right to start all over again with counting.
This is a bit of a vague answer but I have not get any examples ready.
Regards,
Robert
April 28, 2015 at 11:20 am
Based on the sample data what should the expected results look like?
April 28, 2015 at 11:27 am
The expected results (in this case) would look like:
Streak Sport Event Gender Country From To
7 Archery Team Female South Korea 1988 2012
This supposes a streak > 5.
Robert
April 28, 2015 at 11:29 am
Here is something to look at. I have to get back to work so cannot work on it today.
WITH cte1 AS (
SELECT DISTINCT Year_From FROM #mySeries
)
, cte2 AS (
SELECT DISTINCT Sport, [Event], Gender FROM #mySeries
)
SELECT cte1.Year_From, cte2.Sport, cte2.[Event], cte2.Gender, m.ID
FROM cte2 cross apply cte1
LEFT JOIN #mySeries m
ON m.Year_From = cte1.Year_From and m.Sport = cte2.Sport and m.[Event] = cte2.[Event] and m.Gender = cte2.Gender
;
April 28, 2015 at 11:40 am
I have the following code:
with Years as (
select
min(Year_From) YearBegin,
max(Year_From) YearEnd,
(max(Year_From) - min(Year_From))/4 YearsSpan
from
#mySeries
), eFastTally(n) as (
select row_number() over (order by (select null)) from sys.all_columns
), BaseData as (
select
*,
Seq = Year_From - (row_number() over (partition by Sport, SportEvent, Gender, Country order by Year_From) * 4)
from
#mySeries ms
)
select
Sport,
SportEvent,
Gender,
Country,
min(Year_From) FirstYear,
max(Year_From) LastYear,
count(Seq) Streak
from
BaseData
group by
Sport,
SportEvent,
Gender,
Country,
Seq
order by
Sport,
SportEvent,
Gender,
FirstYear;
with Years as (
select
min(Year_From) YearBegin,
max(Year_From) YearEnd,
(max(Year_From) - min(Year_From))/4 YearsSpan
from
#mySeries
), eFastTally(n) as (
select row_number() over (order by (select null)) from sys.all_columns
), BaseData as (
select
*,
Seq = Year_From - (row_number() over (partition by Sport, SportEvent, Gender, Country order by Year_From) * 4)
from
#mySeries ms
)
select
Sport,
SportEvent,
Gender,
Country,
min(Year_From) FirstYear,
max(Year_From) LastYear,
count(Seq) Streak
from
BaseData
group by
Sport,
SportEvent,
Gender,
Country,
Seq
having
count(Seq) > 5
order by
Sport,
SportEvent,
Gender,
FirstYear;
April 28, 2015 at 12:04 pm
Hello Lynn,
I tried your solution and it is working perfectly! I don´t quite understand it yet, but I´ll give it a try. The only difference between part 1 and part 2 is the having clause I presume?
Thank you very much for helping me out!
Kind regards,
Robert
April 28, 2015 at 12:24 pm
Lynn, I'm curious.
What where you expecting to do with the Years and Tally CTEs? I'm sure that you included them for your tests, but I'd love to know what where you trying.
By the way, this is my code which returns something different which doesn't matter if the streak is longer than 5.
WITH cteStreaks AS(
SELECT *,
Year_From -
ROW_NUMBER() OVER(PARTITION BY Sport, SportEvent, Gender, Country ORDER BY Year_From) * 4 grouper
FROM #mySeries
),
cteMaxStreaks AS(
SELECT Sport,
SportEvent,
Gender,
Country,
MIN(Year_From) Year_From,
MAX(Year_To) Year_To,
COUNT(*) Streak,
ROW_NUMBER() OVER(PARTITION BY Sport, SportEvent, Gender ORDER BY COUNT(*) DESC) rn
FROM cteStreaks
GROUP BY Sport, SportEvent, Gender, Country, grouper
)
SELECT Sport,
SportEvent,
Gender,
Country,
Year_From,
Year_To,
Streak
FROM cteMaxStreaks
WHERE rn = 1
ORDER BY Sport, SportEvent, Gender;
And before I forget, your table has a horrible design. Nvarchar uses twice the space of a varchar so you should use it only when needed. Sport, Event and Country are defined as MAX data types which seems unnecessary when a normal length should be enough (or even an identifier to join to the correct catalogs). Number uses 12 bytes and doesn't even give you the range of an int which only uses 4.
I'm not saying any of this just to make you feel offended, I just want you to realize that this could give you problems in the future. Just try to use the correct data types and lengths according to the data you're storing.
April 28, 2015 at 12:44 pm
Hello Luis,
I am not feeling offended, just appreciating your remarks. What you are saying is that I should use varchar(40) instead of nvarchar(max). Normally I do that but in this case I was distracted by the problem described.
Also thanks for your solution: I will try it out. One remark: is something going terribly wrong when the streak is 4 eg?
Thank you for your advice and help.
Kind regards,
Robert
April 28, 2015 at 12:46 pm
Luis Cazares (4/28/2015)
Lynn, I'm curious.What where you expecting to do with the Years and Tally CTEs? I'm sure that you included them for your tests, but I'd love to know what where you trying.
By the way, this is my code which returns something different which doesn't matter if the streak is longer than 5.
WITH cteStreaks AS(
SELECT *,
Year_From -
ROW_NUMBER() OVER(PARTITION BY Sport, SportEvent, Gender, Country ORDER BY Year_From) * 4 grouper
FROM #mySeries
),
cteMaxStreaks AS(
SELECT Sport,
SportEvent,
Gender,
Country,
MIN(Year_From) Year_From,
MAX(Year_To) Year_To,
COUNT(*) Streak,
ROW_NUMBER() OVER(PARTITION BY Sport, SportEvent, Gender ORDER BY COUNT(*) DESC) rn
FROM cteStreaks
GROUP BY Sport, SportEvent, Gender, Country, grouper
)
SELECT Sport,
SportEvent,
Gender,
Country,
Year_From,
Year_To,
Streak
FROM cteMaxStreaks
WHERE rn = 1
ORDER BY Sport, SportEvent, Gender;
And before I forget, your table has a horrible design. Nvarchar uses twice the space of a varchar so you should use it only when needed. Sport, Event and Country are defined as MAX data types which seems unnecessary when a normal length should be enough (or even an identifier to join to the correct catalogs). Number uses 12 bytes and doesn't even give you the range of an int which only uses 4.
I'm not saying any of this just to make you feel offended, I just want you to realize that this could give you problems in the future. Just try to use the correct data types and lengths according to the data you're storing.
I was originally thinking I would need all the years for the time span of the data, and it turns out I didn't. The code needs refactoring to pull out the parts that aren't needed any more.
April 28, 2015 at 12:52 pm
r_slot (4/28/2015)
Hello Luis,I am not feeling offended, just appreciating your remarks. What you are saying is that I should use varchar(40) instead of nvarchar(max). Normally I do that but in this case I was distracted by the problem described.
Also thanks for your solution: I will try it out. One remark: is something going terribly wrong when the streak is 4 eg?
Thank you for your advice and help.
Kind regards,
Robert
Yes, basically that's what I meant.
My solution shows the maximum streaks for males as well as females. Even if the individual male maximum streak is 2 and for teams is 3. Basically, nothing goes terribly wrong, you just need to be sure which is your expected result.
April 28, 2015 at 12:57 pm
Luis,
Your code is also working perfectly! No problems with a smaller streak.
Thank you again.
Kind regards,
Robert
April 28, 2015 at 1:12 pm
Here is my refactored code:
with BaseData as (
select
*,
Seq = Year_From - (row_number() over (partition by Sport, SportEvent, Gender, Country order by Year_From) * 4)
from
#mySeries ms
)
select
Sport,
SportEvent,
Gender,
Country,
min(Year_From) FirstYear,
max(Year_From) LastYear,
count(Seq) Streak
from
BaseData
group by
Sport,
SportEvent,
Gender,
Country,
Seq
--having
-- count(Seq) > 5
order by
Sport,
SportEvent,
Gender,
FirstYear;
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply