varun varun - 11 days ago 5
SQL Question

Calculate 'n' weeks from the week number & year

Anyone know how to calculate 'n' weeks (may be a week number or date) from the week number . I know the year and the week number? I'm going to do this in SQL server.

Eg- i have week number i.e. 3 and year i.e. 2016 and I have to calculate last 13 weeks avg profit.

I have added script & expected results-




CREATE TABLE [dbo].[Sales](
[SalesID] [int] IDENTITY(1,1) NOT NULL,
[SalesPersonID] [int] NOT NULL,
[CustomerID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[profit] [int] NOT NULL,
Year int Not Null,
Quarter int Not Null,
Week int Not Null,
CONSTRAINT [SalesPK] PRIMARY KEY CLUSTERED
(
[SalesID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO [Sales] ([SalesPersonID],[CustomerID],[ProductID],[profit],[Year],[Quarter],[week]) VALUES (17, 10482, 500, 500,2016,1,3)
INSERT INTO [Sales] ([SalesPersonID],[CustomerID],[ProductID],[profit],[Year],[Quarter],[week]) VALUES (5, 1964, 500 , 810,2016,1,2)
INSERT INTO [Sales] ([SalesPersonID],[CustomerID],[ProductID],[profit],[Year],[Quarter],[week]) VALUES (8, 12300, 500, 123,2016,1,1)
INSERT INTO [Sales] ([SalesPersonID],[CustomerID],[ProductID],[profit],[Year],[Quarter],[week]) VALUES (1, 4182, 500, 437,2015,4,52)
INSERT INTO [Sales] ([SalesPersonID],[CustomerID],[ProductID],[profit],[Year],[Quarter],[week]) VALUES (14, 15723, 500, 750,2015,4,51)
INSERT INTO [Sales] ([SalesPersonID],[CustomerID],[ProductID],[profit],[Year],[Quarter],[week]) VALUES (14, 6000, 500, 60, 2015,4,50)
INSERT INTO [Sales] ([SalesPersonID],[CustomerID],[ProductID],[profit],[Year],[Quarter],[week]) VALUES (5, 17541, 500, 373,2015,4,49)
INSERT INTO [Sales] ([SalesPersonID],[CustomerID],[ProductID],[profit],[Year],[Quarter],[week]) VALUES (20, 9423, 500, 687,2015,4,48)
INSERT INTO [Sales] ([SalesPersonID],[CustomerID],[ProductID],[profit],[Year],[Quarter],[week]) VALUES (1, 1, 500, 10, 2015,4,47)
INSERT INTO [Sales] ([SalesPersonID],[CustomerID],[ProductID],[profit],[Year],[Quarter],[week]) VALUES (11, 11241, 500, 310,2015,4,46)
INSERT INTO [Sales] ([SalesPersonID],[CustomerID],[ProductID],[profit],[Year],[Quarter],[week]) VALUES (2, 3023, 500, 623,2015,4,45)
INSERT INTO [Sales] ([SalesPersonID],[CustomerID],[ProductID],[profit],[Year],[Quarter],[week]) VALUES (17, 14664, 500, 937,2015,4,44)
INSERT INTO [Sales] ([SalesPersonID],[CustomerID],[ProductID],[profit],[Year],[Quarter],[week]) VALUES (20, 5241, 500, 250,2015,4,43)
INSERT INTO [Sales] ([SalesPersonID],[CustomerID],[ProductID],[profit],[Year],[Quarter],[week]) VALUES (8, 16482, 500, 560,2015,4,42)
INSERT INTO [Sales] ([SalesPersonID],[CustomerID],[ProductID],[profit],[Year],[Quarter],[week]) VALUES (22, 8264, 500, 873,2015,4,41)


select * from sales


---Expected Results

So if calculating last 13 week avg profit from 3rd week of 2016
then it should take the profit till week number 43 of 2015 and do the average of profit.

So the results will look like-

ProductID| Avg Profit
500|451.53


Thanks in advance.

Answer

For Example Lets take the Current Year and Current week taken into account.

So the Year will be 2016, and week will be 48. Pass these values the statement to get the result.

DECLARE @YEAR VARCHAR(10)='2016', @WEEK INT = 48

--For the given week(48) of year(2016) Start datetime of week value: (Assumed Sunday is week start Day)

SELECT DATEADD(wk,DATEDIFF(wk,cast( 6 as DATETIME), @YEAR+'-01-01' ) + @WEEK-1,CAST( 6 AS DATETIME));

--For the given week(48) of year(2016) End datetime of week value:
SELECT DATEADD(wk,DATEDIFF(wk,CAST( 5 AS DATETIME), @YEAR+'-01-01' ) + @WEEK-1,CAST( 5 AS DATETIME));

And the Result will be

'2016-11-20 00:00:00.000'  --48th week start day (Sun Day)
'2016-11-26 00:00:00.000'  --48th week end day (Sat Day)

For your requirement Just do substract how many weeks you need to go back.

DECLARE @YEAR VARCHAR(10)='2016', @WEEK INT = 3 , @WEEKFROM INT 
SET @WEEKFROM = @WEEK-13
--13 weeks back week start date of (2016 year, 3rd week)
SELECT DATEADD(wk,DATEDIFF(wk,CAST(6 AS DATETIME), @YEAR+'-01-01' )+ @WEEKFROM-1,CAST(6 AS DATETIME));

--end datetime of given week (2016 year, 3rd week)
SELECT DATEADD(wk,DATEDIFF(wk,CAST(5 AS DATETIME), @YEAR+'-01-01' )+ @WEEK-1,CAST(5 AS DATETIME));

The Output of above is (With these datetime you can query your table for Profit of these weeks).

'2015-10-11 00:00:00.000' --from date
'2016-01-16 00:00:00.000' --to date

How it is working?

Try casting the Numbers into Datetime

SELECT CAST(0 AS datetime)  --1900-01-01 00:00:00.000
SELECT CAST(1 AS datetime)  --1900-01-02 00:00:00.000
.
.
SELECT CAST(5 AS datetime)  --1900-01-06 00:00:00.000 Sat Day
SELECT CAST(6 AS datetime)  --1900-01-07 00:00:00.000 Sun Day

enter image description here

Now calculate the No of weeks between '1900-01-07 00:00:00.000' to '2016-01-01' with DateDiff

select DATEDIFF(wk,CAST(6 AS DATETIME), @YEAR+'-01-01' )+ @WEEKFROM-1  ---> 6040

Now add it to 1900-01-07 with DATEADD

SELECT DATEADD(wk,DATEDIFF(wk,CAST(6 AS DATETIME), @YEAR+'-01-01' )+ @WEEKFROM-1,CAST(6 AS DATETIME));

Result: 2015-10-11 00:00:00.000
Comments