January 13, 2016 at 9:52 am
Hi experts,
Can anyone help me to create the logic that I need??
Logic --->If a month changes in week, then week volume should be attributed to month with more no of days in that week.
Now the table structure is like below
Table Columns :ClientProjectName, ID,week1(volume),week2,week3....week35
ABC 1 20 30 15 54
BCD 2 35 22 10 18
Need to change like this with the logic that I mentioned before:
ClientProjectName, ID, week, month ,Volume
ABC 1 1 1 20
ABC 1 2 1 30
So my question is how can I get month number based on the week numbers and how to implement the logic that I mentioned above.
Thanks a lot
Ausitn
January 13, 2016 at 10:49 am
caojunhe24 (1/13/2016)
Hi experts,Can anyone help me to create the logic that I need??
Logic --->If a month changes in week, then week volume should be attributed to month with more no of days in that week.
Now the table structure is like below
Table Columns :ClientProjectName, ID,week1(volume),week2,week3....week35
ABC 1 20 30 15 54
BCD 2 35 22 10 18
Need to change like this with the logic that I mentioned before:
ClientProjectName, ID, week, month ,Volume
ABC 1 1 1 20
ABC 1 2 1 30
So my question is how can I get month number based on the week numbers and how to implement the logic that I mentioned above.
Thanks a lot
Ausitn
I would suggest that you build yourself a calendar table (there are lots of good ones available on the web if you search) and also add a child table for your requirement that tracks attributes at the week-level. Here is a basic working example of what I mean:
create table dbo.calendar
(
date_full date not null,
date_year smallint not null,
date_day tinyint not null,
date_month tinyint not null,
day_in_year tinyint not null,
week_number_in_year tinyint not null -- e.g. 1-52
); -- unique constraint on date_year & week_number_in_year
create table dbo.calendar_week
(
date_year smallint not null,
week_number_in_year tinyint not null, -- e.g. 1-52
date_month tinyint not null -- e.g. 1-12 -- this is where you would attribute the week to a month based on how many days of that week were in each month
); -- foreign key constraint on date_year & week_number_in_year referencing dbo.calendar
Most folks providing calendar table online will provide the code to pre-populate them for any number of years into the past and future. You will need to write the logic to pre-populate the week-table (dbo.calendar_week above) based on your business logic on how to assign each week to a given month. Once you have these table populated you can refer to them again and again and never have to calculate the data on the fly.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 14, 2016 at 8:00 am
That's my question.
how many days of that week were in each month? How to write a code to implement this code
January 14, 2016 at 8:01 am
implement this logic*
January 14, 2016 at 8:25 am
DECLARE @days TABLE (dt date not null primary key);
DECLARE @dt date;
SET @dt = '20160101';
WHILE @dt < '20170101'
BEGIN
INSERT into @days VALUES (@dt);
SET @dt = DATEADD(DAY,1,@dt);
END
SELECT DATEPART(MONTH,dt) MonthNbr, DATEPART(week,dt) WeekNbr, MIN(dt) MinDT, MAX(dt) MaxDT, COUNT(*) Days
FROM @days
GROUP BY DATEPART(MONTH,dt), DATEPART(week,dt)
;
January 14, 2016 at 8:54 am
Bill Talada (1/14/2016)
DECLARE @days TABLE (dt date not null primary key);
DECLARE @dt date;
SET @dt = '20160101';
WHILE @dt < '20170101'
BEGIN
INSERT into @days VALUES (@dt);
SET @dt = DATEADD(DAY,1,@dt);
END
SELECT DATEPART(MONTH,dt) MonthNbr, DATEPART(week,dt) WeekNbr, MIN(dt) MinDT, MAX(dt) MaxDT, COUNT(*) Days
FROM @days
GROUP BY DATEPART(MONTH,dt), DATEPART(week,dt)
;
I think the OP needs each week only represented once in the results, attributed to a single month.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 14, 2016 at 9:05 am
I think the OP should go with a calendar table. I did take it 90% of the way to show him how to manipulate dates. His implementation will likely vary greatly. I prefer to teach principles rather than code up a whole solution. In any case here is the final summary he might want:
DECLARE @days TABLE (dt date not null primary key);
DECLARE @dt date;
SET @dt = '20160101';
WHILE @dt < '20170101'
BEGIN
INSERT into @days VALUES (@dt);
SET @dt = DATEADD(DAY,1,@dt);
END
SELECT DATEPART(MONTH,dt) MonthNbr, DATEPART(week,dt) WeekNbr, MIN(dt) MinDT, MAX(dt) MaxDT, COUNT(*) Days
FROM @days
GROUP BY DATEPART(MONTH,dt), DATEPART(week,dt)
;
SELECT *
from
(
SELECT DATEPART(MONTH,dt) MonthNbr, DATEPART(week,dt) WeekNbr, MIN(dt) MinDT, MAX(dt) MaxDT, COUNT(*) Days
FROM @days
GROUP BY DATEPART(MONTH,dt), DATEPART(week,dt)
) x
WHERE x.Days > 3
;
January 14, 2016 at 9:09 am
Bill Talada (1/14/2016)
I think the OP should go with a calendar table. I did take it 90% of the way to show him how to manipulate dates. His implementation will likely vary greatly. I prefer to teach principles rather than code up a whole solution. In any case here is the final summary he might want:
DECLARE @days TABLE (dt date not null primary key);
DECLARE @dt date;
SET @dt = '20160101';
WHILE @dt < '20170101'
BEGIN
INSERT into @days VALUES (@dt);
SET @dt = DATEADD(DAY,1,@dt);
END
SELECT DATEPART(MONTH,dt) MonthNbr, DATEPART(week,dt) WeekNbr, MIN(dt) MinDT, MAX(dt) MaxDT, COUNT(*) Days
FROM @days
GROUP BY DATEPART(MONTH,dt), DATEPART(week,dt)
;
SELECT *
from
(
SELECT DATEPART(MONTH,dt) MonthNbr, DATEPART(week,dt) WeekNbr, MIN(dt) MinDT, MAX(dt) MaxDT, COUNT(*) Days
FROM @days
GROUP BY DATEPART(MONTH,dt), DATEPART(week,dt)
) x
WHERE x.Days > 3
;
I do as well which is why I recommended the calendar table. I think your second attempt is still missing something. Week 10 start and end dates are 2016-03-01 and 2016-03-05 and I think they should be 2016-02-28 and 2016-03-05.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply