Defining week ending date.

Hello,
I have a w ending date question. Here is my table (just for demo
purposes)
CREATE TABLE [dbo].[TestTable] (
[userID] [varchar] (10) NULL ,
[u_key] [int] NULL ,
[TS] [datetime] NULL
) ON [PRIMARY]
GO
insert into testTable values ('a', 3, '7/9/2005 6:12:59 PM')
insert into testTable values ('b', 2, '7/9/2005 6:13:35 PM')
insert into testTable values ('d', 2, '7/9/2005 6:14:07 PM')
insert into testTable values ('d', 2, '7/22/2005 11:26:08 AM')
insert into testTable values ('d', 4, '7/22/2005 11:26:08 AM')
insert into testTable values ('e', 2, '7/27/2005 1:27:18 PM')
insert into testTable values ('f', 2, '7/27/2005 5:21:36 PM')
insert into testTable values ('a', 2, '8/1/2005 12:02:02 PM')
insert into testTable values ('b', 2, '8/1/2005 12:02:05 PM')
insert into testTable values ('c', 2, '8/1/2005 3:49:16 PM')
'// This is the query I run
Select a.u_key, DATEPART(ww,a.ts) as Period, count(*) as Counter
From testtable a
Group by a.u_key, DATEPART(ww,a.ts)
'// I get this Result set, which is exactly what I want.
u_key Period Counter
2 28 2
3 28 1
2 30 1
4 30 1
2 31 2
2 32 3
I am assuming that using the DatePart(ww..) automagically makes the
wending a Saturday. Now, my client wants to change the w ending to
Thursday (or whatever). I have no idea how I would change the query. I
most definitely need to have the period number returned as part of the
select clause.
Thanks for all your help.
-JackJack,
1. Don't use DATEPART to calculate w number if you want to calculate acco
rding to the ISO
standard (where this w is w 38). SQL Server DATEPART considers this we
ek to be w number 39.
If you want to calculate according to ISO, install the ISOWEEK function whic
h you find in Books
Online.
2. Use SET DATEFIRST con set first day of w. I think ISOWEEK respects thi
s setting, but test just
to be certain.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack" <jack@.jack.net> wrote in message news:9RWXe.39021$Cc5.3100@.lakeread06...ed">
> Hello,
> I have a w ending date question. Here is my table (just for demo purpo
ses)
> CREATE TABLE [dbo].[TestTable] (
> [userID] [varchar] (10) NULL ,
> [u_key] [int] NULL ,
> [TS] [datetime] NULL
> ) ON [PRIMARY]
> GO
> insert into testTable values ('a', 3, '7/9/2005 6:12:59 PM')
> insert into testTable values ('b', 2, '7/9/2005 6:13:35 PM')
> insert into testTable values ('d', 2, '7/9/2005 6:14:07 PM')
> insert into testTable values ('d', 2, '7/22/2005 11:26:08 AM')
> insert into testTable values ('d', 4, '7/22/2005 11:26:08 AM')
> insert into testTable values ('e', 2, '7/27/2005 1:27:18 PM')
> insert into testTable values ('f', 2, '7/27/2005 5:21:36 PM')
> insert into testTable values ('a', 2, '8/1/2005 12:02:02 PM')
> insert into testTable values ('b', 2, '8/1/2005 12:02:05 PM')
> insert into testTable values ('c', 2, '8/1/2005 3:49:16 PM')
> '// This is the query I run
> Select a.u_key, DATEPART(ww,a.ts) as Period, count(*) as Counter
> From testtable a
> Group by a.u_key, DATEPART(ww,a.ts)
> '// I get this Result set, which is exactly what I want.
> u_key Period Counter
> 2 28 2
> 3 28 1
> 2 30 1
> 4 30 1
> 2 31 2
> 2 32 3
> I am assuming that using the DatePart(ww..) automagically makes the wen
ding a Saturday. Now,
> my client wants to change the w ending to Thursday (or whatever). I ha
ve no idea how I would
> change the query. I most definitely need to have the period number return
ed as part of the select
> clause.
> Thanks for all your help.
> -Jack
>|||You could use a calendar table, you'd have to define the ws yourself, but
it gives you complete flexibility (and you only have to do it once).
http://www.aspfaq.com/2519
"Jack" <jack@.jack.net> wrote in message
news:9RWXe.39021$Cc5.3100@.lakeread06...
> Hello,
> I have a w ending date question. Here is my table (just for demo
> purposes)
> CREATE TABLE [dbo].[TestTable] (
> [userID] [varchar] (10) NULL ,
> [u_key] [int] NULL ,
> [TS] [datetime] NULL
> ) ON [PRIMARY]
> GO
> insert into testTable values ('a', 3, '7/9/2005 6:12:59 PM')
> insert into testTable values ('b', 2, '7/9/2005 6:13:35 PM')
> insert into testTable values ('d', 2, '7/9/2005 6:14:07 PM')
> insert into testTable values ('d', 2, '7/22/2005 11:26:08 AM')
> insert into testTable values ('d', 4, '7/22/2005 11:26:08 AM')
> insert into testTable values ('e', 2, '7/27/2005 1:27:18 PM')
> insert into testTable values ('f', 2, '7/27/2005 5:21:36 PM')
> insert into testTable values ('a', 2, '8/1/2005 12:02:02 PM')
> insert into testTable values ('b', 2, '8/1/2005 12:02:05 PM')
> insert into testTable values ('c', 2, '8/1/2005 3:49:16 PM')
> '// This is the query I run
> Select a.u_key, DATEPART(ww,a.ts) as Period, count(*) as Counter
> From testtable a
> Group by a.u_key, DATEPART(ww,a.ts)
> '// I get this Result set, which is exactly what I want.
> u_key Period Counter
> 2 28 2
> 3 28 1
> 2 30 1
> 4 30 1
> 2 31 2
> 2 32 3
> I am assuming that using the DatePart(ww..) automagically makes the
> wending a Saturday. Now, my client wants to change the w ending to
> Thursday (or whatever). I have no idea how I would change the query. I
> most definitely need to have the period number returned as part of the
> select clause.
> Thanks for all your help.
> -Jack
>|||The DATEFIRST setting specifies the first day of the w.
SET DATEFIRST sets the first day and @.@.DATEFIRST returns the current setting
So...
SELECT CASE @.@.DATEFIRST
WHEN 1 THEN 7
ELSE @.@.DATEFIRST -1
END AS last_day_of_w
"Jack" wrote:

> Hello,
> I have a w ending date question. Here is my table (just for demo
> purposes)
> CREATE TABLE [dbo].[TestTable] (
> [userID] [varchar] (10) NULL ,
> [u_key] [int] NULL ,
> [TS] [datetime] NULL
> ) ON [PRIMARY]
> GO
> insert into testTable values ('a', 3, '7/9/2005 6:12:59 PM')
> insert into testTable values ('b', 2, '7/9/2005 6:13:35 PM')
> insert into testTable values ('d', 2, '7/9/2005 6:14:07 PM')
> insert into testTable values ('d', 2, '7/22/2005 11:26:08 AM')
> insert into testTable values ('d', 4, '7/22/2005 11:26:08 AM')
> insert into testTable values ('e', 2, '7/27/2005 1:27:18 PM')
> insert into testTable values ('f', 2, '7/27/2005 5:21:36 PM')
> insert into testTable values ('a', 2, '8/1/2005 12:02:02 PM')
> insert into testTable values ('b', 2, '8/1/2005 12:02:05 PM')
> insert into testTable values ('c', 2, '8/1/2005 3:49:16 PM')
> '// This is the query I run
> Select a.u_key, DATEPART(ww,a.ts) as Period, count(*) as Counter
> From testtable a
> Group by a.u_key, DATEPART(ww,a.ts)
> '// I get this Result set, which is exactly what I want.
> u_key Period Counter
> 2 28 2
> 3 28 1
> 2 30 1
> 4 30 1
> 2 31 2
> 2 32 3
> I am assuming that using the DatePart(ww..) automagically makes the
> wending a Saturday. Now, my client wants to change the w ending to
> Thursday (or whatever). I have no idea how I would change the query. I
> most definitely need to have the period number returned as part of the
> select clause.
> Thanks for all your help.
> -Jack
>
>