Custom Weeks

In CODE section below, I'm grouping the Sum of Freight by the w/year. We
have a custom year begin and end year table that the DDL section will
create. Thus, I use the t_w table to calculate our custom ws with w
1 of any year starting with the date in the w_beginyeardate field and the
last w of the year ending on the w_endyeardate date value.
Is there a way I could return the same results created by my CODE section in
Northwind except use custom ws derived from my t_w table? Also, I also
need a fix to properly sort the w/year field from start to end w/year
field.
CODE:
SELECT CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
CAST(DATEPART(yyyy, OrderDate) AS char(4)) AS wnum, SUM(Freight) AS
sumFreight
FROM Orders
GROUP BY CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
CAST(DATEPART(yyyy, OrderDate) AS char(4))
ORDER BY CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
CAST(DATEPART(yyyy, OrderDate) AS char(4))
DDL ****************************************
*****
USE [Northwind]
GO
CREATE TABLE [t_w] (
[w_id] [int] IDENTITY(1,1) NOT NULL,
[w_beginyeardate] [datetime] NULL,
[w_endyeardate] [datetime] NULL,
[w_year] [int] NULL
) ON [PRIMARY]
GO
insert into t_w (w_beginyeardate, w_endyeardate, w_year) values
('19951226', '19961230', '1996')
insert into t_w (w_beginyeardate, w_endyeardate, w_year) values
('19961231', '19971229', '1997')
insert into t_w (w_beginyeardate, w_endyeardate, w_year) values
('19971230', '19981228', '1998')
insert into t_w (w_beginyeardate, w_endyeardate, w_year) values
('19981229', '19981227', '1999')
insert into t_w (w_beginyeardate, w_endyeardate, w_year) values
('19991228', '20001225', '2000')A sub-query, like
SELECT CAST(DATEDIFF(dd, (SELECT w_beginyeardate FROM t_w WHERE
w_year=DATEPART(yyyy, o.OrderDate)), o.OrderDate)/7 +1 AS varchar(2))
would get you the custom w number for a given date, but you can't use
sub-queries in a GROUP BY clause. The solution here is to create a function.
The function below formats OrderDate into yyyy-ww
CREATE FUNCTION udfCustomWFormat (@.date datetime)
RETURNS varchar(7)
AS
BEGIN
DECLARE @.output varchar(7)
SELECT @.output = CAST(DATEPART(yyyy, @.date) AS char(4)) + '-'
+ RIGHT('0' + CAST(DATEDIFF(dd, (SELECT w_beginyeardate FROM t_w
WHERE w_year=DATEPART(yyyy, @.date)), @.date) / 7 + 1 AS varchar(3)), 2)
RETURN @.output
END
You can then use the function to both group and order by
SELECT dbo.udfCustomWFormat(OrderDate), SUM(Freight) AS sumFreight
FROM Orders
GROUP BY dbo.udfCustomWFormat(OrderDate)
ORDER BY dbo.udfCustomWFormat(OrderDate)
"Scott" wrote:

> In CODE section below, I'm grouping the Sum of Freight by the w/year. W
e
> have a custom year begin and end year table that the DDL section will
> create. Thus, I use the t_w table to calculate our custom ws with we
ek
> 1 of any year starting with the date in the w_beginyeardate field and t
he
> last w of the year ending on the w_endyeardate date value.
> Is there a way I could return the same results created by my CODE section
in
> Northwind except use custom ws derived from my t_w table? Also, I al
so
> need a fix to properly sort the w/year field from start to end w/yea
r
> field.
>
> CODE:
> SELECT CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
> CAST(DATEPART(yyyy, OrderDate) AS char(4)) AS wnum, SUM(Freight) AS
> sumFreight
> FROM Orders
> GROUP BY CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
> CAST(DATEPART(yyyy, OrderDate) AS char(4))
> ORDER BY CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
> CAST(DATEPART(yyyy, OrderDate) AS char(4))
>
> DDL ****************************************
*****
> USE [Northwind]
> GO
> CREATE TABLE [t_w] (
> [w_id] [int] IDENTITY(1,1) NOT NULL,
> [w_beginyeardate] [datetime] NULL,
> [w_endyeardate] [datetime] NULL,
> [w_year] [int] NULL
> ) ON [PRIMARY]
> GO
> insert into t_w (w_beginyeardate, w_endyeardate, w_year) value
s
> ('19951226', '19961230', '1996')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year) value
s
> ('19961231', '19971229', '1997')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year) value
s
> ('19971230', '19981228', '1998')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year) value
s
> ('19981229', '19981227', '1999')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year) value
s
> ('19991228', '20001225', '2000')
>
>
>|||You almost have it. Please run my DDL below (I simulated my production db).
The only thing wrong is for example at the end of 2004, your w 53 2004
should be w 1 2005. If you run my example below, you'll see no w 1
2005. otherwise, the wly sums are correct. 2004 should only have 52
ws.
I think this is happening because you aren't taking the year ending dates
into consideration.
DDL *********************************
CREATE TABLE [dbo].[test_prod](
[prod_id] [int] IDENTITY(1,1) NOT NULL,
[prod_date] [datetime] NULL,
[log_count] [int] NULL
) ON [PRIMARY]
CREATE TABLE [t_w] (
[w_id] [int] IDENTITY(1,1) NOT NULL,
[w_beginyeardate] [datetime] NULL,
[w_endyeardate] [datetime] NULL,
[w_year] [int] NULL
) ON [PRIMARY]
GO
insert into t_w (w_beginyeardate, w_endyeardate, w_year) values
('19951226', '19961230', '1996')
insert into t_w (w_beginyeardate, w_endyeardate, w_year) values
('19961231', '19971229', '1997')
insert into t_w (w_beginyeardate, w_endyeardate, w_year) values
('19971230', '19981228', '1998')
insert into t_w (w_beginyeardate, w_endyeardate, w_year) values
('19981229', '19981227', '1999')
insert into t_w (w_beginyeardate, w_endyeardate, w_year) values
('19991228', '20001225', '2000')
CREATE FUNCTION udfWFormat (@.date datetime)
RETURNS varchar(7)
AS
BEGIN
DECLARE @.output varchar(7)
SELECT @.output = CAST(DATEPART(yyyy, @.date) AS char(4)) + '-'
+ RIGHT('0' + CAST(DATEDIFF(dd, (SELECT w_beginyeardate FROM t_w
WHERE w_year=DATEPART(yyyy, @.date)), @.date) / 7 + 1 AS varchar(3)), 2)
RETURN @.output
END
insert into test_prod (prod_date, log_count) values
('20041220', '6456')
insert into test_prod (prod_date, log_count) values
('20041221', '5830')
insert into test_prod (prod_date, log_count) values
('20041222', '3480')
insert into test_prod (prod_date, log_count) values
('20041227', '6542')
insert into test_prod (prod_date, log_count) values
('20041228', '6669')
insert into test_prod (prod_date, log_count) values
('20041229', '6524')
insert into test_prod (prod_date, log_count) values
('20041230', '6541')
insert into test_prod (prod_date, log_count) values
('20050104', '6370')
insert into test_prod (prod_date, log_count) values
('20050105', '6926')
insert into test_prod (prod_date, log_count) values
('20050106', '5002')
insert into test_prod (prod_date, log_count) values
('20050107', '6736')
insert into test_prod (prod_date, log_count) values
('20050108', '5822')
-- execute to see results
SELECT dbo.udfWFormat(prod_date), SUM(log_count) AS SumOflog_count
FROM dbo.test_prod
GROUP BY dbo.udfWFormat(prod_date)
ORDER BY dbo.udfWFormat(prod_date)
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:EBE692C6-1006-4E89-8ED9-985ED5F58F7B@.microsoft.com...
>A sub-query, like
> SELECT CAST(DATEDIFF(dd, (SELECT w_beginyeardate FROM t_w WHERE
> w_year=DATEPART(yyyy, o.OrderDate)), o.OrderDate)/7 +1 AS varchar(2))
> would get you the custom w number for a given date, but you can't use
> sub-queries in a GROUP BY clause. The solution here is to create a
> function.
> The function below formats OrderDate into yyyy-ww
> CREATE FUNCTION udfCustomWFormat (@.date datetime)
> RETURNS varchar(7)
> AS
> BEGIN
> DECLARE @.output varchar(7)
> SELECT @.output = CAST(DATEPART(yyyy, @.date) AS char(4)) + '-'
> + RIGHT('0' + CAST(DATEDIFF(dd, (SELECT w_beginyeardate FROM t_w
> WHERE w_year=DATEPART(yyyy, @.date)), @.date) / 7 + 1 AS varchar(3)), 2)
> RETURN @.output
> END
> You can then use the function to both group and order by
> SELECT dbo.udfCustomWFormat(OrderDate), SUM(Freight) AS sumFreight
> FROM Orders
> GROUP BY dbo.udfCustomWFormat(OrderDate)
> ORDER BY dbo.udfCustomWFormat(OrderDate)
> "Scott" wrote:
>|||Yep, I realized my mistake about 15 minutes after my post, but couldn't get
back to it until after a few hours. This should do it:
DROP FUNCTION udfCustomWFormat
GO
CREATE FUNCTION udfCustomWFormat (@.date datetime)
RETURNS varchar(7)
AS
BEGIN
DECLARE @.output varchar(7)
SELECT @.output = CAST(w_year AS char(4)) + '-'
+ RIGHT('0' + CAST(DATEDIFF(dd, w_beginyeardate, @.date) / 7 + 1 AS
varchar(3)), 2)
FROM t_w WHERE @.date BETWEEN w_beginyeardate AND w_endyeardate
RETURN @.output
END
SELECT dbo.udfCustomWFormat(OrderDate), SUM(Freight) AS sumFreight
FROM Orders
GROUP BY dbo.udfCustomWFormat(OrderDate)
ORDER BY dbo.udfCustomWFormat(OrderDate)
The function should now pick up the dates in late December correctly. For
example,
select dbo.udfCustomWFormat('19951228') results in 1996-01
Your example t_w table only goes to 2000, but you are running it against
a table that has dates in 2005; is the table cyclic?
"Scott" wrote:

> You almost have it. Please run my DDL below (I simulated my production db)
.
> The only thing wrong is for example at the end of 2004, your w 53 2004
> should be w 1 2005. If you run my example below, you'll see no w 1
> 2005. otherwise, the wly sums are correct. 2004 should only have 52
> ws.
> I think this is happening because you aren't taking the year ending dates
> into consideration.
> DDL *********************************
> CREATE TABLE [dbo].[test_prod](
> [prod_id] [int] IDENTITY(1,1) NOT NULL,
> [prod_date] [datetime] NULL,
> [log_count] [int] NULL
> ) ON [PRIMARY]
>
> CREATE TABLE [t_w] (
> [w_id] [int] IDENTITY(1,1) NOT NULL,
> [w_beginyeardate] [datetime] NULL,
> [w_endyeardate] [datetime] NULL,
> [w_year] [int] NULL
> ) ON [PRIMARY]
> GO
> insert into t_w (w_beginyeardate, w_endyeardate, w_year) value
s
> ('19951226', '19961230', '1996')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year) value
s
> ('19961231', '19971229', '1997')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year) value
s
> ('19971230', '19981228', '1998')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year) value
s
> ('19981229', '19981227', '1999')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year) value
s
> ('19991228', '20001225', '2000')
> CREATE FUNCTION udfWFormat (@.date datetime)
> RETURNS varchar(7)
> AS
> BEGIN
> DECLARE @.output varchar(7)
> SELECT @.output = CAST(DATEPART(yyyy, @.date) AS char(4)) + '-'
> + RIGHT('0' + CAST(DATEDIFF(dd, (SELECT w_beginyeardate FROM t_w
> WHERE w_year=DATEPART(yyyy, @.date)), @.date) / 7 + 1 AS varchar(3)), 2)
> RETURN @.output
> END
> insert into test_prod (prod_date, log_count) values
> ('20041220', '6456')
> insert into test_prod (prod_date, log_count) values
> ('20041221', '5830')
> insert into test_prod (prod_date, log_count) values
> ('20041222', '3480')
> insert into test_prod (prod_date, log_count) values
> ('20041227', '6542')
> insert into test_prod (prod_date, log_count) values
> ('20041228', '6669')
> insert into test_prod (prod_date, log_count) values
> ('20041229', '6524')
> insert into test_prod (prod_date, log_count) values
> ('20041230', '6541')
> insert into test_prod (prod_date, log_count) values
> ('20050104', '6370')
> insert into test_prod (prod_date, log_count) values
> ('20050105', '6926')
> insert into test_prod (prod_date, log_count) values
> ('20050106', '5002')
> insert into test_prod (prod_date, log_count) values
> ('20050107', '6736')
> insert into test_prod (prod_date, log_count) values
> ('20050108', '5822')
>
> -- execute to see results
> SELECT dbo.udfWFormat(prod_date), SUM(log_count) AS SumOflog_count
> FROM dbo.test_prod
> GROUP BY dbo.udfWFormat(prod_date)
> ORDER BY dbo.udfWFormat(prod_date)
>
> "Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
> news:EBE692C6-1006-4E89-8ED9-985ED5F58F7B@.microsoft.com...
>
>|||First, thanks big for solving this head-hurter. My t_w table does have
the last 5 years plus always 1 year in advance. I had the old years so it'd
work in northwind.
what did you mean by the table being "cyclic"?
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:602D1125-A792-40A5-B505-7B31B7524A94@.microsoft.com...
> Yep, I realized my mistake about 15 minutes after my post, but couldn't
> get
> back to it until after a few hours. This should do it:
> DROP FUNCTION udfCustomWFormat
> GO
> CREATE FUNCTION udfCustomWFormat (@.date datetime)
> RETURNS varchar(7)
> AS
> BEGIN
> DECLARE @.output varchar(7)
> SELECT @.output = CAST(w_year AS char(4)) + '-'
> + RIGHT('0' + CAST(DATEDIFF(dd, w_beginyeardate, @.date) / 7 + 1 AS
> varchar(3)), 2)
> FROM t_w WHERE @.date BETWEEN w_beginyeardate AND w_endyeardate
> RETURN @.output
> END
> SELECT dbo.udfCustomWFormat(OrderDate), SUM(Freight) AS sumFreight
> FROM Orders
> GROUP BY dbo.udfCustomWFormat(OrderDate)
> ORDER BY dbo.udfCustomWFormat(OrderDate)
> The function should now pick up the dates in late December correctly. For
> example,
> select dbo.udfCustomWFormat('19951228') results in 1996-01
> Your example t_w table only goes to 2000, but you are running it
> against
> a table that has dates in 2005; is the table cyclic?
> "Scott" wrote:
>|||Just curious - what kind of ws start on TUES?
William Stacey [MVP]
"Scott" <sbailey@.mileslumber.com> wrote in message
news:%23UoMFlnCGHA.3876@.tk2msftngp13.phx.gbl...
> In CODE section below, I'm grouping the Sum of Freight by the w/year.
> We have a custom year begin and end year table that the DDL section will
> create. Thus, I use the t_w table to calculate our custom ws with
> w 1 of any year starting with the date in the w_beginyeardate field
> and the last w of the year ending on the w_endyeardate date value.
> Is there a way I could return the same results created by my CODE section
> in Northwind except use custom ws derived from my t_w table? Also, I
> also need a fix to properly sort the w/year field from start to end
> w/year field.
>
> CODE:
> SELECT CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
> CAST(DATEPART(yyyy, OrderDate) AS char(4)) AS wnum, SUM(Freight) AS
> sumFreight
> FROM Orders
> GROUP BY CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
> CAST(DATEPART(yyyy, OrderDate) AS char(4))
> ORDER BY CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
> CAST(DATEPART(yyyy, OrderDate) AS char(4))
>
> DDL ****************************************
*****
> USE [Northwind]
> GO
> CREATE TABLE [t_w] (
> [w_id] [int] IDENTITY(1,1) NOT NULL,
> [w_beginyeardate] [datetime] NULL,
> [w_endyeardate] [datetime] NULL,
> [w_year] [int] NULL
> ) ON [PRIMARY]
> GO
> insert into t_w (w_beginyeardate, w_endyeardate, w_year)
> values
> ('19951226', '19961230', '1996')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year)
> values
> ('19961231', '19971229', '1997')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year)
> values
> ('19971230', '19981228', '1998')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year)
> values
> ('19981229', '19981227', '1999')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year)
> values
> ('19991228', '20001225', '2000')
>
>
>|||That the start and end year dates repeat in a pattern, every 5 years or so.
I
got from your second post that you were adjusting your data to fit Northwind
so a solution could be tested (and that's appreciated!)
I noticed when I ran the query against Northwind, one of the years (1996 I
think) shows up as having 53 ws in it. The difference between the year
start date and end date for 1996 in your sample is 370 days, so that does go
beying 52 ws. However, in a "normal" year, my math shows the last day of
the year being in w 53. It needs some twing. If I have time, I'll post
an adjusted version later.
"scott" wrote:

> First, thanks big for solving this head-hurter. My t_w table does have
> the last 5 years plus always 1 year in advance. I had the old years so it'
d
> work in northwind.
> what did you mean by the table being "cyclic"?
>
> "Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
> news:602D1125-A792-40A5-B505-7B31B7524A94@.microsoft.com...
>
>|||Should have been this way from the start.
CREATE FUNCTION udfCustomWFormat (@.date datetime)
RETURNS varchar(7)
AS
BEGIN
DECLARE @.output varchar(7)
SELECT @.output = CAST(w_year AS char(4)) + '-'
+ RIGHT('0' + CAST(CEILING(DATEDIFF(dd, w_beginyeardate, @.date) / 7.00)
AS
varchar(3)), 2)
FROM t_w WHERE @.date BETWEEN w_beginyeardate AND w_endyeardate
RETURN @.output
END
Now the last (364th) day of the year shows up as being in w 52. For years
that go beyond 364 days, you will still end up with a w 53 out of this
function.
"scott" wrote:

> First, thanks big for solving this head-hurter. My t_w table does have
> the last 5 years plus always 1 year in advance. I had the old years so it'
d
> work in northwind.
> what did you mean by the table being "cyclic"?
>
> "Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
> news:602D1125-A792-40A5-B505-7B31B7524A94@.microsoft.com...
>
>|||Are these calendar ws starting with Sunday?
William Stacey [MVP]
"Scott" <sbailey@.mileslumber.com> wrote in message
news:%23UoMFlnCGHA.3876@.tk2msftngp13.phx.gbl...
> In CODE section below, I'm grouping the Sum of Freight by the w/year.
> We have a custom year begin and end year table that the DDL section will
> create. Thus, I use the t_w table to calculate our custom ws with
> w 1 of any year starting with the date in the w_beginyeardate field
> and the last w of the year ending on the w_endyeardate date value.
> Is there a way I could return the same results created by my CODE section
> in Northwind except use custom ws derived from my t_w table? Also, I
> also need a fix to properly sort the w/year field from start to end
> w/year field.
>
> CODE:
> SELECT CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
> CAST(DATEPART(yyyy, OrderDate) AS char(4)) AS wnum, SUM(Freight) AS
> sumFreight
> FROM Orders
> GROUP BY CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
> CAST(DATEPART(yyyy, OrderDate) AS char(4))
> ORDER BY CAST(DATEPART(w, OrderDate) AS varchar(2)) + '-' +
> CAST(DATEPART(yyyy, OrderDate) AS char(4))
>
> DDL ****************************************
*****
> USE [Northwind]
> GO
> CREATE TABLE [t_w] (
> [w_id] [int] IDENTITY(1,1) NOT NULL,
> [w_beginyeardate] [datetime] NULL,
> [w_endyeardate] [datetime] NULL,
> [w_year] [int] NULL
> ) ON [PRIMARY]
> GO
> insert into t_w (w_beginyeardate, w_endyeardate, w_year)
> values
> ('19951226', '19961230', '1996')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year)
> values
> ('19961231', '19971229', '1997')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year)
> values
> ('19971230', '19981228', '1998')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year)
> values
> ('19981229', '19981227', '1999')
> insert into t_w (w_beginyeardate, w_endyeardate, w_year)
> values
> ('19991228', '20001225', '2000')
>
>
>|||what's the difference in this 2nd correct function and the 1st or previous
correct function?
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:D4583358-03D7-4EAD-9B4C-F1E69B884861@.microsoft.com...
> Should have been this way from the start.
> CREATE FUNCTION udfCustomWFormat (@.date datetime)
> RETURNS varchar(7)
> AS
> BEGIN
> DECLARE @.output varchar(7)
> SELECT @.output = CAST(w_year AS char(4)) + '-'
> + RIGHT('0' + CAST(CEILING(DATEDIFF(dd, w_beginyeardate, @.date) / 7.00)
> AS
> varchar(3)), 2)
> FROM t_w WHERE @.date BETWEEN w_beginyeardate AND w_endyeardate
> RETURN @.output
> END
> Now the last (364th) day of the year shows up as being in w 52. For
> years
> that go beyond 364 days, you will still end up with a w 53 out of this
> function.
> "scott" wrote:
>