CODEWITHSUNDEEP
sqlsql-serversql-server-2008
UpwardD
UpwardD

Reputation: 767

Get Rolling Weeks Data in SQL Server

I want to get rolling 70-week, 65-week and 60-week (separate) data using SQL query. I know how to do this in years and months but how do I get a rolling 65-week data etc?

Upvotes: 0

Views: 1273

Answers (2)

Thorgeir
Thorgeir

Reputation: 4433

If I understand you correctly you want to know how many week are in a date span, say from 2016-01-01 to 2017-01-14.

In sql server you convert dates to int (days from 1900-01-01) like so convert(int,DateColumn)

So you can simply convert the dates to int, subtract, then divide by 7

For example

2016-01-01 = 42368

2017-01-14 = 42747

(42747 - 42368) / 7 = 54

A sql query would look something like this:

select (convert(int,GETDATE()) - convert(int,DateColumn)) / 7  as WeeksAgo  from Table

Used in a where statement to get last 65 weeks:

select * from Table where ((convert(int,GETDATE()) - convert(int,DateColumn)) / 7) < 65

Upvotes: 2

Mazhar
Mazhar

Reputation: 3837

Not much to go on from the question but you can use a tally table

DECLARE @StartDateTime DATE = GETDATE() --Pick a starting Point

--tally tables http://www.sqlservercentral.com/articles/T-SQL/62867/
--===== Conditionally drop 
IF OBJECT_ID('dbo.Tally') IS NOT NULL 
    DROP TABLE dbo.Tally

--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
    IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
     Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
    ADD CONSTRAINT PK_Tally_N 
        PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100


SELECT
    DATEADD(WEEK, T.N, @StartDateTime) 'Week70'
FROM dbo.Tally T
WHERE
    T.N <= 70

SELECT
    DATEADD(WEEK, T.N, @StartDateTime) 'Week65'
FROM dbo.Tally T
WHERE
    T.N <= 65

SELECT
    DATEADD(WEEK, T.N, @StartDateTime) 'Week60'
FROM dbo.Tally T
WHERE
    T.N <= 60

Upvotes: 0

Related Questions