RLT RLT -4 years ago 53
SQL Question

Create DATE (first day of week) column using data from two existing columns

I have been trying to create a view of a current table where I create a date by using two columns of the original table. But I cannot get it working..

In the table I have columns 14-52, 1-13, which represent the weeks of a FiscalYear.
In the table the last column is FiscalYear which is set to 2017 for all of the data in this case.

I want to create a DATE column (by using the monday of the specific week) in the view which is based on the week number of the year (the column) and the fiscal year (the column)

So for example we have column "week 14" and column "2017" and I want to get back "20170403" (3th of april, first monday in april 2017).

So in pictures:
Currently I have this:

1

I want to create this:

2

Any help would be much appreciated!

Answer Source
CREATE TABLE SampleTable (EmployeeNumber int, 
[1] int, [2] int, [3] int, [4] int, 
[5] int, [6] int, [7] int, [8] int, 
[9] int, [10] int, [11] int, [12] int, [13] int,
[14] int, [15] int, [16] int, [17] int, 
[18] int, [19] int, [20] int, [21] int, 
[22] int, [23] int, [24] int, [25] int, FiscalYear int);
GO
INSERT INTO SampleTable VALUES (1,
1,2,3,4,
5,6,7,8,
9,10,11,12,13,
1000,2000,3000,4000,5000,6000,7000,8000,9000,10000,11000,12000, 2017);
GO
SET DATEFIRST 1

SELECT EmployeeNumber
 ,CONVERT(VARCHAR(8)
 ,dateadd (week, CONVERT(int, [Date])
 ,dateadd (year, CASE WHEN CONVERT(int, [Date]) < 14 THEN FiscalYear + 1 ELSE FiscalYear END - 1900 , 0)) - 4 - datepart(dw, dateadd (week, CONVERT(int,[Date])
 ,dateadd (year, CASE WHEN CONVERT(int, [Date]) < 14 THEN FiscalYear + 1 ELSE FiscalYear END - 1900 , 0)) - 4) + 1, 112) AS [DATE]
,FiscalYear
,Budget
FROM 
(
    SELECT EmployeeNumber, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], FiscalYear FROM SampleTable
) p
UNPIVOT
(
Budget FOR [Date] IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25])
)
AS unpvt

I hope that it will help you.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download