June 29, 2015 at 8:14 pm
Hello,
I work in the world of swimming and use a SQL Server Database to help me work on reporting. I have the following query below.
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'
from swimming_registration a
, Swimmer b
, Swimming_events c
, Swim_meets d
, motivational_time_standards_test e
, Motivational_type f
where a.fkey_swimmer = b.key_swimmer
and a.fkey_event = c.key_event
and a.fkey_swim_Meet = d.key_meet
and e.fkey_event = c.key_event
and e.fkey_event = a.fkey_event
and e.fkey_course = a.fkey_course
and e.gender = b.gender
and FLOOR(DATEDIFF(day, b.birthdate, getDate()) / 365.25) <= e.max_age
and FLOOR(DATEDIFF(day, b.birthdate, getDate()) / 365.25) >= e.min_age
and a.fkey_dq = 83
and a.end_time_cvt NOT IN ('DQ','NS', 'NT')
and e.fkey_motivational_type = f.key_motivational_type
and convert(decimal(10,2), a.end_time_cvt)- convert(decimal(10,2), e.time_standard_cvt) <= 0
and a.key_registration = 9281
The query returns the following:
last_Namefirst_nameAgeEventMeetmotivational_descriptiontime_standardmin_agemax_ageFinished TimeCut Difference
StimesKelsey16100 Backstroke2015 WDST PentathlonA1:21.7916161:01.54 -20.25
StimesKelsey16100 Backstroke2015 WDST PentathlonAA1:17.8916161:01.54 -16.35
StimesKelsey16100 Backstroke2015 WDST PentathlonAAA1:13.9916161:01.54 -12.45
StimesKelsey16100 Backstroke2015 WDST PentathlonAAAA1:10.0916161:01.54 -8.55
StimesKelsey16100 Backstroke2015 WDST PentathlonB1:45.0916161:01.54 -43.55
StimesKelsey16100 Backstroke2015 WDST PentathlonBB1:33.4916161:01.54 -31.95
I am looking for it to return the highest level time standard achieved. The order is B,BB, A, AA, AAA, & AAAA. In the example below I would expect only the AAAA time to be returned. The query currently returns more than it needs to in case I have bad data in the motivation time standards table. Any help is greatly appreciated.
June 29, 2015 at 9:21 pm
Sounds like you need a table for this:
The order is B,BB, A, AA, AAA, & AAAA.... something like
CREATE TABLE LoookupTable(
stringValue CHAR(4) PRIMARY KEY,
Seq TINYINT NOT NULL UNIQUE
);
then you'd join to that and order by Seq.
June 30, 2015 at 7:52 am
coachmkavanaugh (6/29/2015)
Hello,I work in the world of swimming and use a SQL Server Database to help me work on reporting. I have the following query below.
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'
from swimming_registration a
, Swimmer b
, Swimming_events c
, Swim_meets d
, motivational_time_standards_test e
, Motivational_type f
where a.fkey_swimmer = b.key_swimmer
and a.fkey_event = c.key_event
and a.fkey_swim_Meet = d.key_meet
and e.fkey_event = c.key_event
and e.fkey_event = a.fkey_event
and e.fkey_course = a.fkey_course
and e.gender = b.gender
and FLOOR(DATEDIFF(day, b.birthdate, getDate()) / 365.25) <= e.max_age
and FLOOR(DATEDIFF(day, b.birthdate, getDate()) / 365.25) >= e.min_age
and a.fkey_dq = 83
and a.end_time_cvt NOT IN ('DQ','NS', 'NT')
and e.fkey_motivational_type = f.key_motivational_type
and convert(decimal(10,2), a.end_time_cvt)- convert(decimal(10,2), e.time_standard_cvt) <= 0
and a.key_registration = 9281
The query returns the following:
last_Namefirst_nameAgeEventMeetmotivational_descriptiontime_standardmin_agemax_ageFinished TimeCut Difference
StimesKelsey16100 Backstroke2015 WDST PentathlonA1:21.7916161:01.54 -20.25
StimesKelsey16100 Backstroke2015 WDST PentathlonAA1:17.8916161:01.54 -16.35
StimesKelsey16100 Backstroke2015 WDST PentathlonAAA1:13.9916161:01.54 -12.45
StimesKelsey16100 Backstroke2015 WDST PentathlonAAAA1:10.0916161:01.54 -8.55
StimesKelsey16100 Backstroke2015 WDST PentathlonB1:45.0916161:01.54 -43.55
StimesKelsey16100 Backstroke2015 WDST PentathlonBB1:33.4916161:01.54 -31.95
I am looking for it to return the highest level time standard achieved. The order is B,BB, A, AA, AAA, & AAAA. In the example below I would expect only the AAAA time to be returned. The query currently returns more than it needs to in case I have bad data in the motivation time standards table. Any help is greatly appreciated.
Add TOP (1) to your SELECT statement (just after the space after the word SELECT), then add:
ORDER BY
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
to the end of the query.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 30, 2015 at 10:59 am
Are you looking for the record with lowest cut difference everytime for each swimmer? I am guessing that will have the highest motivational_description according to your data, is that a correct statement?
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
July 2, 2015 at 5:14 pm
This sorts it by the motivational type, but if someone has a 'AAAA' value returned, I don't want any of the other times returned. I want to keep the highest rated one returning one row per event.
July 4, 2015 at 10:01 am
coachmkavanaugh (7/2/2015)
This sorts it by the motivational type, but if someone has a 'AAAA' value returned, I don't want any of the other times returned. I want to keep the highest rated one returning one row per event.
So my ORDER BY with the CASE statement works correctly then?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 4, 2015 at 10:09 am
It did on my test case momentarily and then when I applied to my larger population it didn't.
I am not sure what happened. I am not sure where to go from here.
July 4, 2015 at 10:21 am
coachmkavanaugh (7/4/2015)
It did on my test case momentarily and then when I applied to my larger population it didn't.I am not sure what happened. I am not sure where to go from here.
You'll need to say more than "it didn't work" if you're hoping to get useful results. What result did you get? What was returned that you didn't expect?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 4, 2015 at 10:23 am
On a test case with one person with one event it did work. I would like this to return the highest row. A person can do multiple events. When I apply this query to a competition vs one individual registration, one row is still returned.
select top(1) 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'
from swimming_registration a
, Swimmer b
, Swimming_events c
, Swim_meets d
, Motivational_Time_Standards e
, Motivational_type f
where a.fkey_swimmer = b.key_swimmer
and a.fkey_event = c.key_event
and a.fkey_swim_Meet = d.key_meet
and e.fkey_event = c.key_event
and e.fkey_event = a.fkey_event
and e.fkey_course = a.fkey_course
and e.gender = b.gender
and FLOOR(DATEDIFF(day, b.birthdate, getDate()) / 365.25) <= e.max_age
and FLOOR(DATEDIFF(day, b.birthdate, getDate()) / 365.25) >= e.min_age
and a.fkey_dq = 83
and fkey_swim_Meet IN (90)
and a.end_time_cvt NOT IN ('DQ','NS', 'NT')
and e.fkey_motivational_type = f.key_motivational_type
and convert(decimal(10,2), a.end_time_cvt)- convert(decimal(10,2), e.time_standard_cvt) <= 0
ORDER BY
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
July 4, 2015 at 10:30 am
This is what was returned when applied to the competition.
last_Namefirst_nameAgeEventMeetmotivational_descriptiontime_standardmin_agemax_ageFinished Time
StimesKelsey16200 I.M.DCST @ WDSTAA2:19.7916162:18.92
This logic sems to work when it applied looking for the highest total level motivation_description. I would like the highest motivational_description returned for the event for the person.
July 5, 2015 at 1:46 pm
coachmkavanaugh (7/4/2015)
This is what was returned when applied to the competition.last_Namefirst_nameAgeEventMeetmotivational_descriptiontime_standardmin_agemax_ageFinished Time
StimesKelsey16200 I.M.DCST @ WDSTAA2:19.7916162:18.92
This logic sems to work when it applied looking for the highest total level motivation_description. I would like the highest motivational_description returned for the event for the person.
Try this instead. I had thought you were looking for a single result. The only potential problem here is a uniqueness issue, so let me know if this does it:
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
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 5, 2015 at 1:58 pm
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?
July 5, 2015 at 2:07 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?
Okay... this is most likely an issue of what can be selected that is unique to the individual. I have some basic understanding of the concept of a meet, an event within that meet, and an individual competing in that event, but you have the data. You'll need to determine what fields are unique to that event, meet, and individual, and then limit the ORDERED ROWS query to only those fields. Finally, you'll need to be sure that the join in the final query is on all of those fields, so that only 1 record from the source matches the ordered rows record. Does that make sense?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 6, 2015 at 4:26 pm
How about this?
;WITH CTE AS
(
select b.id AS SwimmerID --(Assuming you have a swimmer ID)
, 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'
from swimming_registration a
, Swimmer b
, Swimming_events c
, Swim_meets d
, motivational_time_standards_test e
, Motivational_type f
where a.fkey_swimmer = b.key_swimmer
and a.fkey_event = c.key_event
and a.fkey_swim_Meet = d.key_meet
and e.fkey_event = c.key_event
and e.fkey_event = a.fkey_event
and e.fkey_course = a.fkey_course
and e.gender = b.gender
and FLOOR(DATEDIFF(day, b.birthdate, getDate()) / 365.25) <= e.max_age
and FLOOR(DATEDIFF(day, b.birthdate, getDate()) / 365.25) >= e.min_age
and a.fkey_dq = 83
and a.end_time_cvt NOT IN ('DQ','NS', 'NT')
and e.fkey_motivational_type = f.key_motivational_type
and convert(decimal(10,2), a.end_time_cvt)- convert(decimal(10,2), e.time_standard_cvt) <= 0
and a.key_registration = 9281
)
, CTE2 AS
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY SwimmerID ORDER BY [Cut Difference] DESC) AS Rnum
FROM CTE
)
SELECT *--Column names that you need.
FROM CTE2
WHERE Rnum = 1
Here I assumed you have a swimmer ID column in your swimmer table.
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
July 7, 2015 at 4:33 pm
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.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply