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 w
s 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 w
s 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 w
num, 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 udfCustomW
Format (@.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.udfCustomW
Format(OrderDate), SUM(Freight) AS sumFreight
FROM Orders
GROUP BY dbo.udfCustomW
Format(OrderDate)
ORDER BY dbo.udfCustomW
Format(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 w
s 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 w
s 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 w
num, 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 w
ly sums are correct. 2004 should only have 52
w
s.
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 udfW
Format (@.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.udfW
Format(prod_date), SUM(log_count) AS SumOflog_count
FROM dbo.test_prod
GROUP BY dbo.udfW
Format(prod_date)
ORDER BY dbo.udfW
Format(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 udfCustomW
Format (@.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.udfCustomW
Format(OrderDate), SUM(Freight) AS sumFreight
> FROM Orders
> GROUP BY dbo.udfCustomW
Format(OrderDate)
> ORDER BY dbo.udfCustomW
Format(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 udfCustomW
Format
GO
CREATE FUNCTION udfCustomW
Format (@.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.udfCustomW
Format(OrderDate), SUM(Freight) AS sumFreight
FROM Orders
GROUP BY dbo.udfCustomW
Format(OrderDate)
ORDER BY dbo.udfCustomW
Format(OrderDate)
The function should now pick up the dates in late December correctly. For
example,
select dbo.udfCustomW
Format('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 w
ly sums are correct. 2004 should only have 52
> w
s.
> 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 udfW
Format (@.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.udfW
Format(prod_date), SUM(log_count) AS SumOflog_count
> FROM dbo.test_prod
> GROUP BY dbo.udfW
Format(prod_date)
> ORDER BY dbo.udfW
Format(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 udfCustomW
Format
> GO
> CREATE FUNCTION udfCustomW
Format (@.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.udfCustomW
Format(OrderDate), SUM(Freight) AS sumFreight
> FROM Orders
> GROUP BY dbo.udfCustomW
Format(OrderDate)
> ORDER BY dbo.udfCustomW
Format(OrderDate)
> The function should now pick up the dates in late December correctly. For
> example,
> select dbo.udfCustomW
Format('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 w
s 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 w
s 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 w
s 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 w
num, 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 w
s 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 w
s. However, in a "normal" year, my math shows the last day of
the year being in w
53. It needs some tw
ing. 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 udfCustomW
Format (@.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 w
s 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 w
s 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 w
s 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 w
num, 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 udfCustomW
Format (@.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:
>
vanatage is that it requires dynamic SQL and all of its issues. Thus, y