SQL Query Prob
-
Hi,
I am trying to write an SQL query to look like the pic below
i think i have it 90% done but there are some rows that appear with all cols displayed as null, this is because the query is a weekly summary and there are up to 7 different dates for each session which provides the duplicate rows that are displayed. So im just wondering if anyone could help me find the problem when joining the session table, if this is the problem...
Any help would be appreciated
Thanks in advance
Tim
Select IsNull(p.FirstName+ ' ','') + IsNull(p.MiddleName+ ' ','') + IsNull(p.LastName,'') as Name,
Cast (Case when DATENAME(WEEKDAY, rzd.Date) = 'Monday' then tft.HydrationScore end as nvarchar) as 'HS',
Cast (Case when DATENAME(WEEKDAY, rzd.Date) = 'Monday' then rzd.Mins end as nvarchar) as '>85%',
Cast (Case when DATENAME(WEEKDAY, rzd.Date) = 'Monday' then rzd.DailyTotal end as nvarchar) as 'Ex',
Cast (Case when DATENAME(WEEKDAY, rzd.Date) = 'Tuesday' then tft.HydrationScore end as nvarchar) as 'HS',
Cast (Case when DATENAME(WEEKDAY, rzd.Date) = 'Tuesday' then rzd.Mins end as nvarchar) as '>85%',
Cast (Case when DATENAME(WEEKDAY, rzd.Date) = 'Tuesday' then rzd.DailyTotal end as nvarchar) as 'Ex',
Cast (Case when DATENAME(WEEKDAY, rzd.Date) = 'Wednesday' then tft.HydrationScore end as nvarchar) as 'HS',
Cast (Case when DATENAME(WEEKDAY, rzd.Date) = 'Wednesday' then rzd.Mins end as nvarchar) as '>85%',
Cast (Case when DATENAME(WEEKDAY, rzd.Date) = 'Wednesday' then rzd.DailyTotal end as nvarchar) as 'Ex',
Cast (Case when DATENAME(WEEKDAY, rzd.Date) = 'Thursday' then tft.HydrationScore end as nvarchar) as 'HS',
Cast (Case when DATENAME(WEEKDAY, rzd.Date) = 'Thursday' then rzd.Mins end as nvarchar) as '>85%',
Cast (Case when DATENAME(WEEKDAY, rzd.Date) = 'Thursday' then rzd.DailyTotal end as nvarchar) as 'Ex',
Cast (Case when DATENAME(WEEKDAY, rzd.Date) = 'Firday' then tft.HydrationScore end as nvarchar) as 'HS',
Cast (Case when DATENAME(WEEKDAY, rzd.Date) = 'Firday' then rzd.Mins end as nvarchar) as '>85%',
Cast (Case when DATENAME(WEEKDAY, rzd.Date) = 'Firday' then rzd.DailyTotal end as nvarchar) as 'Ex',
Cast (Case when DATENAME(WEEKDAY, rzd.Date) = 'Saturday' then tft.HydrationScore end as nvarchar) as 'HS',
Cast (Case when DATENAME(WEEKDAY, rzd.Date) = 'Saturday' then rzd.Mins end as nvarchar) as '>85%',
Cast (Case when DATENAME(WEEKDAY, rzd.Date) = 'Saturday' then rzd.DailyTotal end as nvarchar) as 'Ex',
Cast (Case when DATENAME(WEEKDAY, rzd.Date) = 'Sunday' then tft.HydrationScore end as nvarchar) as 'HS',
Cast (Case when DATENAME(WEEKDAY, rzd.Date) = 'Sunday' then rzd.Mins end as nvarchar) as '>85%',
Cast (Case when DATENAME(WEEKDAY, rzd.Date) = 'Sunday' then rzd.DailyTotal end as nvarchar) as 'Ex',
Cast (Sum(tft.HydrationScore) as nvarchar),
Cast (Sum(rzd.Mins) as nvarchar),
Cast (Sum(rzd.DailyTotal) as nvarchar)
FROM [dbo].[SquadPlayerMapping] spm
Join Person p on p.Id = spm.PlayerID
Join RedZoneWeeklyMapping rzwm on rzwm.ID = 151
Left Join Session s on s.Date Between rzwm.StartDate And rzwm.EndDate And s.DailySessionTypeID = 8 -- Fitness tests
Left Join TrainingFitnessTesting tft on tft.SessionID = s.ID And tft.PersonId = p.ID and tft.HydrationScore <> null
Left Join RedZoneData rzd on rzd.Date Between rzwm.StartDate And rzwm.EndDate And rzd.PlayerID = p.ID --And s.Date = rzd.Date
Where spm.SquadID = 12
And ((rzwm.StartDate between spm.DateEntered and DateLeft) or (rzwm.StartDate > spm.DateEntered and DateLeft IS NULL))
Group By p.FirstName, p.MiddleName, p.LastName, tft.HydrationScore, rzd.Date, rzd.Mins, rzd.DailyTotal
-
Firday? You mean Friday?
N 56°04'39.16"
E 12°55'05.25" -
SELECTISNULL(p.FirstName, '') + ISNULL(',' + p.MiddleName, '') + ISNULL(',' + p.LastName, '') AS Name,
SUM(CASE WHEN DATENAME(WEEKDAY, rzd.Date) = 'Monday' THEN tft.HydrationScore ELSE 0.0 END) AS [HS],
SUM(CASE WHEN DATENAME(WEEKDAY, rzd.Date) = 'Monday' THEN rzd.Mins ELSE 0.0 END) AS [>85%],
SUM(CASE WHEN DATENAME(WEEKDAY, rzd.Date) = 'Monday' THEN rzd.DailyTotal ELSE 0.0 END) AS [Ex],
SUM(CASE WHEN DATENAME(WEEKDAY, rzd.Date) = 'Tuesday' THEN tft.HydrationScore ELSE 0.0 END) AS [HS],
SUM(CASE WHEN DATENAME(WEEKDAY, rzd.Date) = 'Tuesday' THEN rzd.Mins ELSE 0.0 END) AS [>85%],
SUM(CASE WHEN DATENAME(WEEKDAY, rzd.Date) = 'Tuesday' THEN rzd.DailyTotal ELSE 0.0 END) AS [Ex],
SUM(CASE WHEN DATENAME(WEEKDAY, rzd.Date) = 'Wednesday' THEN tft.HydrationScore ELSE 0.0 END) AS [HS],
SUM(CASE WHEN DATENAME(WEEKDAY, rzd.Date) = 'Wednesday' THEN rzd.Mins ELSE 0.0 END) AS [>85%],
SUM(CASE WHEN DATENAME(WEEKDAY, rzd.Date) = 'Wednesday' THEN rzd.DailyTotal ELSE 0.0 END) AS [Ex],
SUM(CASE WHEN DATENAME(WEEKDAY, rzd.Date) = 'Thursday' THEN tft.HydrationScore ELSE 0.0 END) AS [HS],
SUM(CASE WHEN DATENAME(WEEKDAY, rzd.Date) = 'Thursday' THEN rzd.Mins ELSE 0.0 END) AS [>85%],
SUM(CASE WHEN DATENAME(WEEKDAY, rzd.Date) = 'Thursday' THEN rzd.DailyTotal ELSE 0.0 END) AS [Ex],
SUM(CASE WHEN DATENAME(WEEKDAY, rzd.Date) = 'Friday' THEN tft.HydrationScore ELSE 0.0 END) AS [HS],
SUM(CASE WHEN DATENAME(WEEKDAY, rzd.Date) = 'Friday' THEN rzd.Mins ELSE 0.0 END) AS [>85%],
SUM(CASE WHEN DATENAME(WEEKDAY, rzd.Date) = 'Friday' THEN rzd.DailyTotal ELSE 0.0 END) AS [Ex],
SUM(CASE WHEN DATENAME(WEEKDAY, rzd.Date) = 'Saturday' THEN tft.HydrationScore END) AS [HS],
SUM(CASE WHEN DATENAME(WEEKDAY, rzd.Date) = 'Saturday' THEN rzd.Mins ELSE 0.0 END) AS [>85%],
SUM(CASE WHEN DATENAME(WEEKDAY, rzd.Date) = 'Saturday' THEN rzd.DailyTotal ELSE 0.0 END) AS [Ex],
SUM(CASE WHEN DATENAME(WEEKDAY, rzd.Date) = 'Sunday' THEN tft.HydrationScore ELSE 0.0 END) AS [HS],
SUM(CASE WHEN DATENAME(WEEKDAY, rzd.Date) = 'Sunday' THEN rzd.Mins ELSE 0.0 END) AS [>85%],
SUM(CASE WHEN DATENAME(WEEKDAY, rzd.Date) = 'Sunday' THEN rzd.DailyTotal ELSE 0.0 END) AS [Ex],
SUM(tft.HydrationScore),
SUM(rzd.Mins),
SUM(rzd.DailyTotal)
FROMdbo.SquadPlayerMapping AS spm
INNER JOINPerson AS p ON p.ID = spm.PlayerID
INNER JOINRedZoneWeeklyMapping AS rzwm ON rzwm.ID = 151
LEFT JOINSession AS s ON s.Date BETWEEN rzwm.StartDate AND rzwm.EndDate
AND s.DailySessionTypeID = 8
LEFT JOINTrainingFitnessTesting AS tft ON tft.SessionID = s.ID
AND tft.PersonId = p.ID
AND tft.HydrationScore IS NOT NULL
LEFT JOINRedZoneData AS rzd ON rzd.Date BETWEEN rzwm.StartDate AND rzwm.EndDate
AND rzd.PlayerID = p.ID
WHEREspm.SquadID = 12
AND (
rzwm.StartDate BETWEEN spm.DateEntered AND spm.DateLeft)
OR
rzwm.StartDate > spm.DateEntered
AND spm.DateLeft IS NULL
)
GROUP BYp.FirstName,
p.MiddleName,
p.LastName
N 56°04'39.16"
E 12°55'05.25"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply