Link to home
Start Free TrialLog in
Avatar of Annette Wilson, MSIS
Annette Wilson, MSISFlag for United States of America

asked on

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'.
Avatar of Mark Ely
Mark Ely
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
technette, do you still need help with this question?
Avatar of Annette Wilson, MSIS


I need help in a different way.  I will post another question.  Thank you Vitor.
Thank you.  This was a quick and workable solution.