Post reply

Adding a Week number in a query.

  • 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?

  • 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]

  • 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

  • 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 table

    (employeId int

    , evaluationDate datetime

    , rate numeric(6, 2))

    insert into @S

    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 s on s.employeId = e.employeeId

    order by s.evaluationDate

    Best regards.

  • March 1, 2012 at 5:44 pm

    #1453955

    Thanks Drew. That worked perfectly.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply