Adding a Week number in a query.

• Richard.Westhaver

  Mr or Mrs. 500

  Points: 542

  More actions

  February 17, 2012 at 2:01 pm

  #251752

  Hi,

  We have a database that shows specific information on employee stats. What I'm doing right now is writing a query to see if their stats have improved over a 3 month period. What they want to see is stats based on their first, second, third and so fourth weeks based on their start of their hiredate.

  So, in other words, if an employee started on April 1st, every day they worked for that first week would have a field that said, either 1, or week 1, then the following week, it would show week 2 or 2, all the way up to week 12, or 12.

  Any ideas on the format I need to use?

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  February 17, 2012 at 2:51 pm

  #1448820

  Looking for something like this:

  DECLARE @HireDate DATETIME

  DECLARE @CurrentDate DATETIME

  SET @HireDate = '01-06-2012'

  SET @CurrentDate = GETDATE()

  SELECT DATEPART(wk, @CurrentDate) - DATEPART(wk,@HireDate) AS 'Weeks Worked'

  --Result:

  Weeks Worked

  6

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• drew.allen

  SSC Guru

  Points: 76996

  More actions

  February 22, 2012 at 7:33 am

  #1450285

  bitbucket-25253 (2/17/2012)

  ---

  Looking for something like this:

  DECLARE @HireDate DATETIME

  DECLARE @CurrentDate DATETIME

  SET @HireDate = '01-06-2012'

  SET @CurrentDate = GETDATE()

  SELECT DATEPART(wk, @CurrentDate) - DATEPART(wk,@HireDate) AS 'Weeks Worked'

  --Result:

  Weeks Worked

  6

  This only works if the hire date is in the same calendar year as the current date. Changing the hire date to '12-06-2011' gives -42. Use DATEDIFF() instead.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Narud

  SSCrazy

  Points: 2837

  More actions

  March 1, 2012 at 5:11 pm

  #1453951

  A little bit late, but I expect that still could be of any kind of help.

  -- employee

  declare @e table

  (employeeId int

  , hireDate datetime

  , employeeName nvarchar(30))

  insert into @e

  values (1, '2012-01-02 07:52', 'Joe')

  -- stats

  declare @s-2 table

  (employeId int

  , evaluationDate datetime

  , rate numeric(6, 2))

  insert into @s-2

  values (1, '2012-01-02', 5),

  (1, '2012-01-03', 4),

  (1, '2012-01-04', 5),

  (1, '2012-01-05', 4),

  (1, '2012-01-06', 4.5),

  (1, '2012-01-09', 5),

  (1, '2012-01-10', 4.5),

  (1, '2012-01-11', 5),

  (1, '2012-01-12', 4),

  (1, '2012-01-13', 5),

  (1, '2012-01-16', 4.5),

  (1, '2012-01-17', 4),

  (1, '2012-01-18', 5),

  (1, '2012-01-19', 5),

  (1, '2012-01-20', 4.5),

  (1, '2012-01-21', 5),

  (1, '2012-01-22', 4),

  (1, '2012-01-23', 5),

  (1, '2012-01-25', 4),

  (1, '2012-01-26', 5),

  (1, '2012-01-27', 5),

  (1, '2012-01-28', 4.5),

  (1, '2012-01-29', 5),

  (1, '2012-02-01', 4),

  (1, '2012-02-02', 5)

  -- Stats with week number

  select e.employeeId

  , e.employeeName

  , e.hireDate

  , s.evaluationDate

  , s.rate

  , DATEDIFF(ww, e.hireDate, s.evaluationDate) +1 as weekNumber

  from @e e

  inner join @s-2 s on s.employeId = e.employeeId

  order by s.evaluationDate

  Best regards.

• Richard.Westhaver

  Mr or Mrs. 500

  Points: 542

  More actions

  March 1, 2012 at 5:44 pm

  #1453955

  Thanks Drew. That worked perfectly.