Skip to content

Instantly share code, notes, and snippets.

@Spaider
Created May 7, 2015 13:57
Show Gist options
  • Save Spaider/dd52946ab4ca1c4ca4b3 to your computer and use it in GitHub Desktop.
Save Spaider/dd52946ab4ca1c4ca4b3 to your computer and use it in GitHub Desktop.
Courses with result count for Trail Running - 2013 vs 2014
select
sumTbl.ID,
cp.CP_Description as 'Description',
sumTbl.[Courses in 2013],
sumTbl.[Courses in 2014],
sumTbl.[Results in 2013],
sumTbl.[Results in 2014]
from
vr_l_CoursePattern cp
inner join
(
select
ISNULL(cp1.CP_OuterID, cp2.CP_OuterID) as 'ID',
ISNULL(cp1.CourseCount, 0) as 'Courses in 2013',
ISNULL(cp1.ResultCount, 0) as 'Results in 2013',
ISNULL(cp2.CourseCount, 0) as 'Courses in 2014',
ISNULL(cp2.ResultCount, 0) as 'Results in 2014'
from
(
select
t1.CP_OuterID,
sum(t1.CourseCount) as CourseCount,
sum(t1.ResultCount) as ResultCount
from
(
select
cp.CP_ID,
cp.CP_OuterID,
count(*) as CourseCount,
sum(ec.EVC_ResultCount) as 'ResultCount'
from
api_vw_Course cs
inner join vr_l_CoursePattern cp
on cs.CoursePatternID = cp.CP_ID
inner join vr_t_Event ev
on ev.EV_EventID = cs.RaceID
inner join vr_r_Event_Course ec
on ec.EVC_CourseID = cs.CourseID
where
datepart(year, ev.EV_StartDateTime) = 2013
and ev.EV_StateProvID like 'US_%'
and cs.RaceCatID = 15
and cs.CourseName != 'Deleted' -- not really useful as of now, AFAIK
group by
cp.CP_ID,
cp.CP_OuterID) as t1
group by
t1.CP_OuterID) cp1
full outer join
(
select
t1.CP_OuterID,
sum(t1.CourseCount) as CourseCount,
sum(t1.ResultCount) as ResultCount
from
(
select
cp.CP_ID,
cp.CP_OuterID,
count(*) as CourseCount,
sum(ec.EVC_ResultCount) as 'ResultCount'
from
api_vw_Course cs
inner join vr_l_CoursePattern cp
on cs.CoursePatternID = cp.CP_ID
inner join vr_t_Event ev
on ev.EV_EventID = cs.RaceID
inner join vr_r_Event_Course ec
on ec.EVC_CourseID = cs.CourseID
where
datepart(year, ev.EV_StartDateTime) = 2014
and ev.EV_StateProvID like 'US_%'
and cs.RaceCatID = 15
and cs.CourseName != 'Deleted' -- not really useful as of now, AFAIK
group by
cp.CP_ID,
cp.CP_OuterID) as t1
group by
t1.CP_OuterID) cp2
on cp1.CP_OuterID = cp2.CP_OuterID
) sumTbl on sumTbl.ID = cp.CP_ID
order by
(sumTbl.[Courses in 2013] + sumTbl.[Courses in 2014]) desc
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment