CODEWITHSUNDEEP
sqlsql-server-2012
loveprogramming
loveprogramming

Reputation: 578

Create a weekCount column in SQL Server 2012

I have this data:

id    worked_date
-----------------    
1     2013-09-25
2     2013-09-26
3     2013-10-01
4     2013-10-04
5     2013-10-07

I want to add a column called weekCount. The based date is 2013-09-25. So all the data with worked_date from 2013-09-25 to 2013-10-01 will have weekCount as 1 and from 2013-10-02 to 2013-10-8 will have weekCount as 2 and so on. How can that be done?

Thanks.

Upvotes: 0

Views: 127

Answers (4)

loveprogramming
loveprogramming

Reputation: 578

Perhaps not the most elegant way but this works for me to get the top rank number:

WITH CTE AS (

                SELECT employee_id, DENSE_RANK() OVER (ORDER BY DATEDIFF(DAY, ''20130925'', worked_date )/7 DESC) AS weekRank
                FROM Timesheet
                )

                SELECT TOP (1) weekRank  
                FROM CTE
                WHERE employee_id=@employee_id  
                ORDER BY weekRank DESC

This is how I can create weekRank column and pass a parameter dynamically:

WITH rank_cte AS (
SELECT timesheet_id,employee_id, date_worked, 
        dateadd(week, datediff(day,'20000105',worked_date) / 7, '20000105')   AS WeekStart, 
        dateadd(week, datediff(day,'20000105',worked_date) / 7, '20000105')+6 AS WeekEnd,
        DENSE_RANK() OVER (ORDER BY 1 + DATEDIFF(DAY, '20130925', worked_date )/7 DESC) AS weekRank
FROM Timesheet

)

 SELECT timesheet_id, worked_date, WeekStart, WeekEnd, weekRank 
 FROM rank_cte rc 
 WHERE  employee_id=@employee_id 
 AND weekRank=@weekRank
 ORDER BY worked_date  DESC

Thanks

Upvotes: 1

billinkc
billinkc

Reputation: 61249

Perhaps an approach like this will solve your problem.

I compute an in-memory table that contains the week's boundaries along with a monotonically increasing number (BuildWeeks). I then compare my worked_date values to my date boundaries. Based on your comment to @sgeddes, you need the reverse week number so I then use a DENSE_RANK function to calculate the ReverseWeekNumber.

WITH BOT(StartDate) AS
(
    SELECT CAST('2013-09-25' AS date)
)
, BuildWeeks (WeekNumber, StartOfWeek, EndOfWeek) AS
(
    SELECT 
        N.number AS WeekNumber
    ,   DateAdd(week, N.number -1, B.StartDate) AS StartOfWeek
    ,   DateAdd(d, -1, DateAdd(week, N.number, B.StartDate)) AS EndOfWeek
    FROM
        dbo.Numbers AS N
        CROSS APPLY
          BOT AS B
)
SELECT
    M.*
,   BW.*
,   DENSE_RANK() OVER (ORDER BY BW.WeekNumber DESC) AS ReverseWeekNumber
FROM
    dbo.MyTable M
    INNER JOIN
        BuildWeeks AS BW
        ON M.worked_date BETWEEN BW.StartOfWeek ANd BW.EndOfWeek
;

SQLFiddle

Upvotes: 1

Cam
Cam

Reputation: 13

If you are looking for a Fiscal Week number, I would use a function that would calculate the week:

CREATE FUNCTION FiscalWeek(@FiscalStartDate datetime, @EvalDate datetime)  
RETURNS INT  
AS  
BEGIN  
DECLARE @weekNumber INT = (DATEDIFF(DAY, @FiscalStartDate, @EvalDate) / 7) + 1

RETURN (@weekNumber % 52)
END
GO

If you used a fiscal starting date of '2013-09-25' and an evaluation date of '2014-09-25' you would get a week number of 1.

Using a function gives you a little more flexibility to do whatever you need.

Upvotes: 1

sgeddes
sgeddes

Reputation: 62861

Here's one way using DATEDIFF:

select id, 
    worked_date, 
    1 + (datediff(day, '2013-09-25', worked_date) / 7) weekCount 
from yourtable

Upvotes: 3

Related Questions