Hi there I am in a new job post just out of university and I am after some help demystifying this SQL view.
Like I said I am very new to this so I apologise if this seems like a trivial task
But what I am after is to limit the results to show the current week and the next week.
here is a snippet of code
Posting the actual SQL code would be vastly more useful than a picture of the code; I can't modify the picture like I could the code :-).
Touche sir,
USE [ProSolutionReports]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SELECT
sd.RefNo AS [username],
LEFT(ro.OfferingCode, 9) AS [course_shortname],
r.Title AS [description],
CONVERT(varchar(10), st.Date,103) AS [start_date(ddmmyyyy)],
CONVERT(varchar(10), st.Date,103) AS [end_date(ddmmyyy)],
(Select Replace (CONVERT(varchar(5),st.StartTime, 108),':','')) AS [start_time(hhmm)],
(Select Replace (CONVERT(varchar(5),st.EndTime, 108),':','')) AS [end_time(hhmm)],
DATEPART(dw, st.Date) AS [day_number],
st.StaffName AS [staff],
st.RoomNo AS [room]
FROM [ProSolution].[dbo].[vStudentTimetable] as [st]
INNER JOIN [ProSolution].[dbo].[StudentDetail] as [sd] ON st.StudentDetailID = sd.StudentDetailID
INNER JOIN [ProSolution].[dbo].[Register] as [r] ON st.RegisterNo = r.RegisterNo
INNER JOIN [ProSolution].[dbo].[vRegisterOfferingGroups] as [ro] ON ro.RegisterNo = st.RegisterNo AND ro.RegisterNo = r.RegisterNo
INNER JOIN [ProSolution].[dbo].[Enrolment] as [e] ON e.OfferingID=ro.OfferingID and e.StudentDetailID=sd.StudentDetailID
WHERE sd.AcademicYearID =
(SELECT AcademicYearID
FROM ProSolution.dbo.AcademicYear as [AY]
WHERE GETDATE() BETWEEN AY.StartDate AND AY.EndDate)
AND sd.RefNo = 'BLA16000460'
GONot sure how you define "a week". I'll assume it's Monday thru Sunday; we can adjust that later if needed.
WHERE sd.AcademicYearID =
(SELECT AcademicYearID
FROM ProSolution.dbo.AcademicYear as [AY]
WHERE GETDATE() BETWEEN AY.StartDate AND AY.EndDate)
AND sd.RefNo = 'BLA16000460'
AND st.Date >= DATEADD(DAY, -DATEDIFF(DAY, 0, GETDATE()) % 7, GETDATE()) /*Monday of current week*/
AND st.Date < DATEADD(DAY, -DATEDIFF(DAY, 0, GETDATE()) % 7 + 14, GETDATE()) /*Monday after next*/Yeah sorry I did mean Monday to Sunday, that looks great scott, thanks a billion.
I will give this a try tomorrow and let you know.
Just Checked this morning and it works like a charm, thank you so much.
If you have time would you mind going over how that query works?
Sorry but I have just re-checked the results and for some reason it is only displaying next Monday and the Monday after that. Do you have any idea why this is happening? Apologies for the constant questions.
See picture below for what I mean :).
You might need to tweak/adjust the date calculations, they may need slight adjustments to get the exact date range you need. You can run this stand-alone:
SELECT DATEADD(DAY, -DATEDIFF(DAY, 0, GETDATE()) % 7, GETDATE()) /Monday of current week/,
DATEADD(DAY, -DATEDIFF(DAY, 0, GETDATE()) % 7 + 14, GETDATE()) /Monday after next/
to determine the values being calculated now and to see what corrections might be needed.
But the general structure should be what you need.
Awesome thanks so much, I used this structure in the end:
SELECT DATEADD(DAY, -DATEDIFF(DAY, 0, GETDATE()) % 9, GETDATE()) /*Monday of current week*/,
DATEADD(DAY, -DATEDIFF(DAY, 0, GETDATE()) % 9 + 14, GETDATE()) /*Monday after next*/
and It works great.
If you have time would you mind explaining how this piece of code works?
I have no idea! "% 9" is not valid with a 7-day week.
The original code determined how many days past Monday the current day was, then subtracted that number of days. Which, if you think about it, will always force the date back to the most recent Monday.
Haha it remains a mystery to me as well, but it is working nonetheless.
Ahh okay I see how it works now, yeah "%9" makes zero sense......
But thanks for you patience and guidance pal.
It might coincidentally work for this year, but probably not for next year, since Mondays will fall into a different pattern.