SQL Reporting - Calculate Week number of Year


I am working on an sql report that should show the scheduled count date for each part.

1. This gives me the next count date in days:  DATEDIFF(day, C.LAST_COUNT_DATE, GETDATE()) - C.COUNT_FREQ

2. I have another table that will give me the actual date that the part was counted for the current year.

3.  If a part is scheduled to be counted every four years, I need to be able to display the  dates  that it was counted then the next three scheduled count dates.

The schedule should look like:

Part_ID                 ABC_CODE        Count Date            Next Count Date              NextCountDate         NextCountDate

888*FRP*66*U                    D                   3/20/2015                  3/20/2019                          3/20/2023                  3/20/2027
                                                                           C                            X                                               X                                   X

777*568*22                        A                    1/2/2015                    6/2/2015                            1/2/2016                      6/2/2016
                                                                          C                                 X                                         X                                     X

Open in new window

The 'X'  and 'C'   stand for Counted and not yet counted.  If the item has been counted according to the appropriate count frequency, I want to show  a 'C'.
Annette Wilson, MSISSr. Programmer AnalystAsked:
Who is Participating?
Mark ElySenior Coldfusion DeveloperCommented:
You should use DatePart to get the week of the year instead.  DATEPART ( datepart , date )

DATEPART(week, CountDate) AS WeekOfTheYear

Vitor MontalvãoMSSQL Senior EngineerCommented:
technette, do you still need help with this question?
Annette Wilson, MSISSr. Programmer AnalystAuthor Commented:
I need help in a different way.  I will post another question.  Thank you Vitor.
Annette Wilson, MSISSr. Programmer AnalystAuthor Commented:
Thank you.  This was a quick and workable solution.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.