May 21, 2015 at 9:16 am
Hello,
I'm having some issues with a complex query in SQL.
I have a date table(A) for every day of the year from 1990-2016 including the week number.
There is another table(B) that has a number, date and week number.
I would like to join these 2 tables together, in order to populate the number from table B on every day of the week (of the week number in table B).
What makes it a little more complex is that the dates in table B not always correspond with the week number in the same row. Adding to the problem are some conditions:
- Every day of the week should have a number, and it should never be 0.
- If the 1st date of all rows in table B with the corresponding week number is not the monday of the week (but for example wednesday) it should start with this number on monday.
- Thousands of rows are in table B and for some of them the date corresponds with the week and for some of them they don't
- The rows can be grouped together using the week number.
Take the following example of table B:
B:
ID Date Number WeekNo
1 21-5-2015 25 21
2 23-5-2015 30 21
In this example the dates correspond with the weeknumber, because the 21st and the 23rd of may are week 21. By joining this with the date table (A), by using for example cross apply, I would hope to get the following result;
Date Number WeekNo
18-5 25 21
19-5 25 21
20-5 25 21
21-5 25 21
22-5 25 21
23-5 30 21
24-5 30 21
The same should work if the same example had the week number 22 in every row.
Take the following example of table B:
B:
ID Date Number WeekNo
1 21-5-2015 25 22
2 23-5-2015 30 22
In this example the dates do not correspond with the weeknumber, because the 21st and the 23rd of may are week 21 and not 22. By joining this with the date table (A), I would hope to get the following result;
Date Number WeekNo
25-5 30 22
26-5 30 22
27-5 30 22
28-5 30 22
29-5 30 22
30-5 30 22
31-5 30 22
Because the last result (ordered by date) of the week number 22 is 30, the whole week shows 30 on every day.
I have been busting my brain on this for 2 days, and I can't seem to get around the problem. It would be much appreciated if someone could lend me a hand.
May 21, 2015 at 9:42 am
Why would the rows in TableB not have the correct week number as per the date?
I've put together some DDL based on the example given
DECLARE @TableA TABLE
(
DayDT DATETIME NOT NULL,
WeekNum INT NOT NULL
);
DECLARE @TableB TABLE
(
ID INT IDENTITY(1,1) NOT NULL,
DT DATETIME NOT NULL,
Number INT NOT NULL,
WeekNum INT NOT NULL
);
WITH Dates (DayDT)
AS
(
SELECTDATEADD(DAY,N,CAST('20150501' AS DATETIME))
FROMdbo.GetNums(0,31)
)
INSERT INTO @TableA (DayDT,WeekNum)
SELECTA.DayDT,
DATEPART(WEEK,A.DayDT)
FROMDates AS A;
INSERT INTO @TableB (DT,Number,WeekNum)
VALUES('20150521',25,21),
('20150523',30,21);
SELECT * FROM @TableA;
SELECT * FROM @TableB;
May 22, 2015 at 12:58 am
Hello,
Thanks for putting together the example.
The reason the dates differ from the weeknumber is because the date is a change date, the date upon which the corresponding number has been changed.
The weeknumber however, is used to decide over which week analysis should take place. It is possible for people to change the weeknumber and therefor change which numbers are included in the analysis.
I should probably also mention that I would like to use this query in a view.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply