Good Evening i wonder if someone could yet again help me.
I have been at it a few hours without success using datepart
Thank you in anticipation
I have a table that has a PressbrakeID and a DateTime fields
I have a sql query (below) and would like to add just before the group by " And" the week no of the datetime field is = to The current week number
--Bends This Week--
SELECT dbo.PBNames.PPN, COUNT(dbo.Pressbrakes.Pressbrake_ID) AS Bends
FROM dbo.PBNames LEFT OUTER JOIN
dbo.Pressbrakes ON dbo.PBNames.PPN = dbo.Pressbrakes.Pressbrake_ID AND CAST(dbo.Pressbrakes.DateTime AS DATE) = CAST(GETDATE() AS DATE) AND
This is actually pretty easy and I have a couple of useful functions that we could pick from.
The trouble is in picking the right one. That's based on what you're calling a "week". And, remember, there's a ISO Week (which is the best, IMHO and custom weeks (weeks that start on some day other than Monday) and "train wrecks" that allow a partial week at the beginning and/or end of the year because a year can start on any one of the 7 days.
With that being said, what is a "week" to you, especially but not limited to the first and last week of the year?
You should alias your table name as what I have shown you in your last question. It makes the query easier to read.
As for the week no, use WEEK or ISO_WEEK depends on your requirement. Refer to datepart() documentation for further details.
SELECT
n.PPN,
DATEPART(WEEK, b.DateTime) AS WeekNo, -- add here
COUNT(b.Pressbrake_ID) AS Bends
FROM
dbo.PBNames AS n
LEFT OUTER JOIN
dbo.Pressbrakes AS b
ON n.PPN = b.Pressbrake_ID
AND CAST(b.DateTime AS DATE) = CAST(GETDATE() AS DATE)
GROUP BY n.PPN,
DATEPART(WEEK, b.DateTime) -- add here
Good morning
Many thanks for the concise reply, I am all a bit new to this sql and found your reply most helpful.
My definition of a week for the purposes of this project is to display the number of Bends on a Monday to Sunday basis.
Good Morning, Many thanks for your reply it has been most helpful, It is a bit of a steep learning curve but none the less enjoyable when I manage to get the results i would have inspected.
In that case, @khtan 's answer would do for you IF YOU USE THE ISOWK datepart. Just make sure that you understand that the first week of a year may or may not contain days from the previous year and might not even contain the first day of the calendar year. Similarly, the last week of the year may or may not contain days for the next year and might not contain the last calendar day of the year.
{edit} Clarified that it must be with the ISO_WEEK datepart to be what I'm talking about.
You cannot order by a field not mentioned in the query. You can use ORDER BY n.PPN and even the number of the column will work, so ORDER BY 1 will also work. If you really want to ORDER BY b.Pressbrake_ID then you should add it to your SELECT statement.