January 13, 2016 at 6:14 am
Hi,
I have a table with Date ranges for a Plan every year as shown below., I need to generate another table with a customized weekofyear and dayofyear between those date ranges.
could you please help me with this.
Actual data:
Plan Year StartDate EndDate
PlanA2014 2014020520150204
PlanB2014 2014021820150217
PlanA2015 2015021520150214
PlanB2015 2015021520150214
Expected sample Result:
PlanYearDateCustomised Dayof yearCustomised week of year
PlanA2014201402051W1
PlanA2014201402062W1
PlanA2014201402073W1
PlanA2014201402084W1
PlanA2014201402095W1
PlanA2014201402106W1
PlanA2014201402117W1
PlanA2014201402128W2
PlanA2014201402139W2
PlanA20142014021410W2
PlanA20142014021511W2
PlanA20142014021612W2
PlanA20142014021713W2
PlanA20142014021814W2
PlanA20142014021915W3
PlanA20142014022016W3
PlanA20142014022117W3
PlanA20142014022218W3
PlanA20142014022319W3
PlanA20142014022420W3
PlanA20142014022521W3
PlanB2014201402181W1
PlanB2014201402192W1
PlanB2014201402203W1
PlanB2014201402214W1
PlanB2014201402225W1
PlanB2014201402236W1
PlanB2014201402247W1
PlanB2014201402258W2
PlanB2014201402269W2
PlanB20142014022710W2
PlanB20142014022811W2
PlanB20142014030112W2
PlanB20142014030213W2
PlanB20142014030314W2
PlanB20142014030415W3
PlanB20142014030516W3
PlanB20142014030617W3
PlanB20142014030718W3
PlanB20142014030819W3
PlanB20142014030920W3
PlanB20142014031021W3
January 13, 2016 at 6:20 am
Please explain how the 'customized' figures are determined.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 13, 2016 at 7:21 am
possible solution ??
USE TEMPDB;
IF OBJECT_ID('TEMPDB..yourtable', 'U') IS NOT NULL DROP TABLE TEMPDB..yourtable ;
CREATE TABLE yourtable(
PlanName VARCHAR(5) NOT NULL
,YearID INTEGER NOT NULL
,StartDate DATEtime NOT NULL
,EndDate DATEtime NOT NULL
);
INSERT INTO yourtable(PlanName,YearID,StartDate,EndDate) VALUES ('PlanA',2014,'20140205','20150204');
INSERT INTO yourtable(PlanName,YearID,StartDate,EndDate) VALUES ('PlanB',2014,'20140218','20150217');
INSERT INTO yourtable(PlanName,YearID,StartDate,EndDate) VALUES ('PlanC',2015,'20150215','20160214');
INSERT INTO yourtable(PlanName,YearID,StartDate,EndDate) VALUES ('PlanD',2015,'20150215','20160214');
-- use a 'Tally' or 'Numbers' table...there ar many ways to do this (Google will help)
WITH
L0 AS(SELECT 1 AS C UNION ALL SELECT 1 AS O),
L1 AS(SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS(SELECT 1 AS C FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS(SELECT 1 AS C FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS(SELECT 1 AS C FROM L3 AS A CROSS JOIN L3 AS B),
Tally AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM L4)
-- one solution maybe?
SELECT yt.PlanName,
yt.YearId,
t.N - 1 + yt.StartDate as Plan_Date,
t.n as Plan_DOY,
'W'+ CAST(((t.n - 1) / 7 + 1) AS VARCHAR(2)) as Plan_WOY
FROM Tally t
CROSS JOIN yourtable yt
WHERE t.N - 1 + yt.StartDate <= yt.EndDate
ORDER BY yt.PlanName,t.N;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 13, 2016 at 7:59 am
Thank you Livingston for the answer.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply