July 7, 2015 at 4:40 pm
A little. What you provided is way above my knowledge base. I can mostly write the type of queries I need to pull data from a meet that I need, except this one. I have been stuck on this for the past year.
July 8, 2015 at 12:19 pm
coachmkavanaugh (7/5/2015)
Holy cow, thank you so much! This is very close!!!There seems to be some duplicates returned from the query, sometime 2 times, other times 3, some 4 times.
Times with B return one row, BB seems to return an extra row, A returns 3 rows, AA returns 4 rows, id imagine AAA may return 5?
I just realized where the problem is. I'm joining back to the source and not using any details, and given that I'm looking to take only ONE of the source records for a given meet, event, and individual, I'm ending up just regurgitating any repeat achievements in the same event. DUH !!!
Try this:
WITH SOURCE_ROWS AS (
SELECT b.last_Name
, b.first_name
,FLOOR(DATEDIFF(day, b.birthdate, getDate()) / 365.25) as 'Age'
, c.event_descr as 'Event'
, d.meet_descr as 'Meet'
, f.motivational_description
, e.time_standard
, e.min_age
, e.max_age
, a.end_time as 'Finished Time'
, convert(decimal(10,2), a.end_time_cvt)- convert(decimal(10,2), e.time_standard_cvt) as 'Cut Difference'
, CASE f.motivational_description
WHEN 'AAAA' THEN 1
WHEN 'AAA' THEN 2
WHEN 'AA' THEN 3
WHEN 'A' THEN 4
WHEN 'BB' THEN 5
WHEN 'B' THEN 6
END AS ORDER_BY_VAL
FROM swimming_registration AS a
INNER JOIN Swimmer AS b
ON a.fkey_swimmer = b.key_swimmer
INNER JOIN Swimming_events AS c
ON a.fkey_event = c.key_event
INNER JOIN Swim_meets AS d
ON a.fkey_swim_Meet = d.key_meet
INNER JOIN Motivational_Time_Standards AS e
ON a.fkey_event = e.fkey_event
AND c.key_event = e.fkey_event
AND a.fkey_course = e.fkey_course
AND b.gender = e.gender
AND FLOOR(DATEDIFF(day, b.birthdate, getDate()) / 365.25) >= e.min_age
AND FLOOR(DATEDIFF(day, b.birthdate, getDate()) / 365.25) <= e.max_age
AND convert(decimal(10,2), a.end_time_cvt)- convert(decimal(10,2), e.time_standard_cvt) <= 0
INNER JOIN Motivational_type AS f
ON e.fkey_motivational_type = f.key_motivational_type
WHERE a.fkey_dq = 83
AND fkey_swim_Meet IN (90)
AND a.end_time_cvt NOT IN ('DQ','NS', 'NT')
),
ORDERED_ROWS AS (
SELECT SR.*, ROW_NUMBER() OVER(PARTITION BY SR.last_Name, SR.first_name, SR.Age, SR.[Event], SR.Meet ORDER BY SR.ORDER_BY_VAL) AS RN
FROM SOURCE_ROWS AS SR
)
SELECT R.*, S.motivational_description, S.time_standard, S.min_age, S.max_age, S.[Finished Time], S.[Cut Difference]
FROM ORDERED_ROWS AS R
INNER JOIN SOURCE_ROWS AS S
ON R.last_Name = S.last_Name
AND R.first_name = S.first_name
AND R.Age = S.Age
AND R.[Event] = S.[Event]
AND R.Meet = S.Meet
AND R.[Finished Time] = S.[Finished Time]
AND R.[Cut Difference] = S.[Cut Difference]
WHERE R.RN = 1
ORDER BY R.Meet, R.[Event], R.Age, R.last_Name, R.first_name
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 8, 2015 at 12:32 pm
coachmkavanaugh (7/7/2015)
Thank you, this returns the one row for a test case! Is there a way to apply this so it would return the highest level row for a meet for an event that a swimmer does. A swimmer can do multiple events for the meet. For example:John Doe, male age 14, can swim 100 Backstroke and 50 freestyle. I'd like the highest level motivational time returned by event for the meet. I'd be happy to send you the data from the tables.
I believe you have filtered for one swimmer with id = 83 right? remove the filter in your where clause where it limits to only one swimmer.
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
July 8, 2015 at 1:33 pm
Oh my, it works!!!!!
I cannot thank you enough!!!! You have saved me a many man hours trying to filter and cut down a big list!!!!!
Thank you, thank you, thank you!
July 8, 2015 at 8:15 pm
coachmkavanaugh (7/8/2015)
Oh my, it works!!!!!I cannot thank you enough!!!! You have saved me a many man hours trying to filter and cut down a big list!!!!!
Thank you, thank you, thank you!
Glad to hear that coach. Mark the answer that helped you. Other people with similar problem can find a solution. Good Luck 🙂
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
July 8, 2015 at 8:18 pm
Thank you again, I really appreciate your willingness to help me out!
July 8, 2015 at 8:45 pm
Steve, Is there an easy way to show the differences of the motivational type if I wanted to compare two or more meets together? The only thing that would change in your query would be the fkey_meet. I would want to see what the "new" motivational time that would be achieved. Thoughts?
July 9, 2015 at 8:06 am
coachmkavanaugh (7/8/2015)
Steve, Is there an easy way to show the differences of the motivational type if I wanted to compare two or more meets together? The only thing that would change in your query would be the fkey_meet. I would want to see what the "new" motivational time that would be achieved. Thoughts?
The important words there are "an easy way". What's your definition of "easy" ? Now that you have a query that generates this data for one meet, you could certainly take the results and put them in a temp table, then generate the data for some other meet, and put those into a 2nd temp table, and then you'd have the basis for a different query that would compare those two temp tables in some meaningful way. However, what kind of comparison you do mght change how you generate the initial results. What, exactly, did you have in mind ?
As an example, a FULL OUTER JOIN between the two temp tables I referred to above might help illustrate your results, but exactly how you construct it would be heavily dependent on the objective, and you'd need to be sure there was a column in each of those temp tables that contains data that uniquely identifies the meet for each result, if it's not already part of the existing query.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 9, 2015 at 1:43 pm
How do you write to a temp table? I was thinking of having a column with the old meet for the first query and then another for the new meet. This column probably would serve as the unique identifier.
July 9, 2015 at 7:15 pm
(Pretending to be Steve, but not as smart)...
You write to a temp table the same way you write to any other table.
INSERT INTO #tempTable(col1,col2,col3)
VALUES ('col1value','col2value','col3value');
OR
INSERT INTO #tempTable(col1,col2,col3)
SELECT colA, colB, colC
FROM <table><join><othertable...>
WHERE...
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply