May 12, 2018 at 10:09 am
--ATTENDANCE BY MONTH AND Week For 4.30 M/W/F Class
SELECT MONTH(Date) AS MONTH, Week (Date) As 'Week of the Year', count(*) AS attendance
FROM Logs
Where notes like '%Attended - %4:30pm%'
and Year(Date) = '2017' --And Month(CancelledDate) = '12'
GROUP BY MONTH(Date), WeeK (Date)
Order by Month, 'Week of the Year'
BUT IT IS....
Proof of concept...
SELECT DATEPART( WK, SYSDATETIME()) 'Week of the Year'
Gets me
Week of the Year
19
And if I strip out the week function, so:
--ATTENDANCE BY MONTH AND Week For 4.30 M/W/F Class
SELECT MONTH(Date) AS MONTH, Replace(notes, 'Attended - ','' ) As 'Class Name', --datepart( WW(Date)) AS 'Week',
count(*) AS Attendance
FROM Logs
Where notes like '%Attended - %4:30pm%'
and Year(Date) = '2018' --And Month(CancelledDate) = '12'
GROUP BY MONTH(Date), Notes--, Week(Date)
Order by Month--, 'Week'
I get
MONTH Class Name Attendance
1 4:30pm Youth Intermediate 15
1 Friday LE/Beginners 4:30pm 37
1 Monday LE/Beginners 4:30pm 27
TIA
May 12, 2018 at 10:31 am
Amazed you weren't getting other errors. WEEK, is not a function. Try the following:
/*
Help for DATEPART function:
https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-2017
*/
SELECT
MONTH([Date]) AS MONTH
, Replace([notes], 'Attended - ','' ) As 'Class Name'
, datepart(WK,[Date]) AS 'Week',
, count(*) AS Attendance
FROM
dbo.Logs
Where
[notes] like '%Attended - %4:30pm%'
and Year([Date]) = '2018'
-- and Month([CancelledDate]) = '12'
GROUP BY
MONTH([Date])
, Notes
--, datepart(WK,[Date])
ORDER BY
MONTH([Date])
--, datepart(WK,[Date])
;
May 12, 2018 at 10:53 am
Cool, thank you!!
I see what you did with adding the square brackets, pretty cool. This accidental developer learned something today 🙂
While we're at it, any way to make the Month of January appear not as '1', but 'January' ??
May 12, 2018 at 12:00 pm
JaybeeSQL - Saturday, May 12, 2018 10:53 AMCool, thank you!!I see what you did with adding the square brackets, pretty cool. This accidental developer learned something today 🙂
While we're at it, any way to make the Month of January appear not as '1', but 'January' ??
Check out this:
https://docs.microsoft.com/en-us/sql/t-sql/functions/datename-transact-sql?view=sql-server-2017
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply