Problem with group by week

Hi
Is there some internal setting within SQL Server that can override your
group by date ' I have a query that I am trying to group by a Monday
and I am using :
DateADD("wk",datediff("wk",0,TA_DATE),0) as 'W Starting'
to do my select & group by
This brings back
W Starting Contr dDate Fault
31/10/2005 00:00:00 M&E 05/11/2005 94
31/10/2005 00:00:00 M&E 30/10/2005 18
31/10/2005 00:00:00 M&E 31/10/2005 93
07/11/2005 00:00:00 M&E 06/11/2005 54
07/11/2005 00:00:00 M&E 07/11/2005 24
but this should look like, if the DB was grouping the data correctly
under "W Starting":
W Starting Contr dDate Fault
31/10/2005 00:00:00 M&E 05/11/2005 94
31/10/2005 00:00:00 M&E 30/10/2005 18
31/10/2005 00:00:00 M&E 31/10/2005 93
31/10/2005 00:00:00 M&E 06/11/2005 54
07/11/2005 00:00:00 M&E 07/11/2005 24
Monday is the 7th which is correct. but then it grouped the 06/11/2005
( Which is the Sunday) under the 7th as well but it should be under the
31st Mon - Sun. I think that this may be caused by our sql DB set up
with a default w start of a Sunday (@.@.DateFirst). Is there anyway to
override this ' or has anyone else ever seem this issue.
Any help appreciated, as I have been knocking my head off it for the
last w.(murtagh@.surfanytime.co.uk) writes:
> DateADD("wk",datediff("wk",0,TA_DATE),0) as 'W Starting'
> to do my select & group by
> This brings back
> W Starting Contr dDate Fault
> 31/10/2005 00:00:00 M&E 05/11/2005 94
> 31/10/2005 00:00:00 M&E 30/10/2005 18
> 31/10/2005 00:00:00 M&E 31/10/2005 93
> 07/11/2005 00:00:00 M&E 06/11/2005 54
> 07/11/2005 00:00:00 M&E 07/11/2005 24
> but this should look like, if the DB was grouping the data correctly
> under "W Starting":
> W Starting Contr dDate Fault
> 31/10/2005 00:00:00 M&E 05/11/2005 94
> 31/10/2005 00:00:00 M&E 30/10/2005 18
> 31/10/2005 00:00:00 M&E 31/10/2005 93
> 31/10/2005 00:00:00 M&E 06/11/2005 54
> 07/11/2005 00:00:00 M&E 07/11/2005 24
> Monday is the 7th which is correct. but then it grouped the 06/11/2005
> ( Which is the Sunday) under the 7th as well but it should be under the
> 31st Mon - Sun. I think that this may be caused by our sql DB set up
> with a default w start of a Sunday (@.@.DateFirst). Is there anyway to
> override this ' or has anyone else ever seem this issue.
SET DATEFIRST could help you, although I would rather write something
that looked at @.@.datefirst.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> I think that this may be caused by our sql DB set up
> with a default w start of a Sunday (@.@.DateFirst). Is there anyway to
> override this ' or has anyone else ever seem this issue.
You can change the starting day to Monday using DATEFIRST:
SET DATEFIRST 1
GO
SELECT DATEADD(wk, DATEDIFF(wk,0,'20051105'),0) as 'W Starting'
SELECT DATEADD(wk, DATEDIFF(wk,0,'20051106'),0) as 'W Starting'
Hope this helps.
Dan Guzman
SQL Server MVP
<murtagh@.surfanytime.co.uk> wrote in message
news:1131363682.312830.174660@.z14g2000cwz.googlegroups.com...
> Hi
> Is there some internal setting within SQL Server that can override your
> group by date ' I have a query that I am trying to group by a Monday
> and I am using :
> DateADD("wk",datediff("wk",0,TA_DATE),0) as 'W Starting'
> to do my select & group by
> This brings back
> W Starting Contr dDate Fault
> 31/10/2005 00:00:00 M&E 05/11/2005 94
> 31/10/2005 00:00:00 M&E 30/10/2005 18
> 31/10/2005 00:00:00 M&E 31/10/2005 93
> 07/11/2005 00:00:00 M&E 06/11/2005 54
> 07/11/2005 00:00:00 M&E 07/11/2005 24
> but this should look like, if the DB was grouping the data correctly
> under "W Starting":
> W Starting Contr dDate Fault
> 31/10/2005 00:00:00 M&E 05/11/2005 94
> 31/10/2005 00:00:00 M&E 30/10/2005 18
> 31/10/2005 00:00:00 M&E 31/10/2005 93
> 31/10/2005 00:00:00 M&E 06/11/2005 54
> 07/11/2005 00:00:00 M&E 07/11/2005 24
> Monday is the 7th which is correct. but then it grouped the 06/11/2005
> ( Which is the Sunday) under the 7th as well but it should be under the
> 31st Mon - Sun. I think that this may be caused by our sql DB set up
> with a default w start of a Sunday (@.@.DateFirst). Is there anyway to
> override this ' or has anyone else ever seem this issue.
> Any help appreciated, as I have been knocking my head off it for the
> last w.
>|||I would avoid anything that relies on DATEFIRST or regional
settings, and instead, define the w start explicitly in the query.
Here is one solution (using the sameple database Northwind)
that does not use any auxiliary tables, shown with both Monday
and Tuesday as the first day of the w (note that January 1, 1900
was a Monday):
-- Group by ws starting on Mondays
select
count(OrderID) as numOrders,
min(dateadd(day, datediff(day,'19000101',OrderDate)/7*7, '19000101'))
as WStarting,
min(OrderDate) as EarliestOrder,
max(OrderDate) as LatestOrder
from Northwind..Orders
group by datediff(day,'19000101',OrderDate)/7
order by WStarting
-- Group by ws starting on Tuesdays
select
count(OrderID) as numOrders,
min(dateadd(day, datediff(day,'19000102',OrderDate)/7*7, '19000102'))
as WStarting,
min(OrderDate) as EarliestOrder,
max(OrderDate) as LatestOrder
from Northwind..Orders
group by datediff(day,'19000102',OrderDate)/7
order by WStarting
If you need something more efficient, there are other options using
a calendar table. See http://www.aspfaq.com/show.asp?id=2519.
A calendar table used with an outer join can also provide result
rows for ws in which your table has no data.
Steve Kass
Drew University
murtagh@.surfanytime.co.uk wrote:

>Hi
>Is there some internal setting within SQL Server that can override your
>group by date ' I have a query that I am trying to group by a Monday
>and I am using :
>DateADD("wk",datediff("wk",0,TA_DATE),0) as 'W Starting'
>to do my select & group by
>This brings back
>W Starting Contr dDate Fault
>31/10/2005 00:00:00 M&E 05/11/2005 94
>31/10/2005 00:00:00 M&E 30/10/2005 18
>31/10/2005 00:00:00 M&E 31/10/2005 93
>07/11/2005 00:00:00 M&E 06/11/2005 54
>07/11/2005 00:00:00 M&E 07/11/2005 24
>but this should look like, if the DB was grouping the data correctly
>under "W Starting":
>W Starting Contr dDate Fault
>31/10/2005 00:00:00 M&E 05/11/2005 94
>31/10/2005 00:00:00 M&E 30/10/2005 18
>31/10/2005 00:00:00 M&E 31/10/2005 93
>31/10/2005 00:00:00 M&E 06/11/2005 54
>07/11/2005 00:00:00 M&E 07/11/2005 24
>Monday is the 7th which is correct. but then it grouped the 06/11/2005
>( Which is the Sunday) under the 7th as well but it should be under the
>31st Mon - Sun. I think that this may be caused by our sql DB set up
>with a default w start of a Sunday (@.@.DateFirst). Is there anyway to
>override this ' or has anyone else ever seem this issue.
>Any help appreciated, as I have been knocking my head off it for the
>last w.
>
>|||Hi Dan
unfortunally, changing the datefirst defaults that permission to all
users. Which could just screw someone else up, even if I change it back
to Sunday after I have finished. Surely there has to be a better way
............. hmmmmmm
thanks.|||SET DATEFIRST only affects the current connection.
Erland and Steve mentioned alternatives to SET DATEFIRST that I should have
mentioned. Personally, I'd go with the calendar table. It can be used for
other date related functions as well, such as fiscal years, holidays, etc.
Hope this helps.
Dan Guzman
SQL Server MVP
<murtagh@.surfanytime.co.uk> wrote in message
news:1131372648.404640.99490@.g43g2000cwa.googlegroups.com...
> Hi Dan
> unfortunally, changing the datefirst defaults that permission to all
> users. Which could just screw someone else up, even if I change it back
> to Sunday after I have finished. Surely there has to be a better way
> ............. hmmmmmm
> thanks.
>|||Thanks guys
Going to go with Steve's solution and will come back later to look at
the calender table, unfortunally I have sunk a lot of time into this
'*little*' problem trying to work it out on my own.
Thanks again
Frank
"A guy who though he knew everything but actually knows nothing" - he
he

Problem with FTS

I am trying to setup Full Text Search Service on my SQL 2000 Server Database
The problem I am having is that the "NEXT" button is not setting to enable when I select all IMAGE column to be populate
Why is it behaving like this ? am I missing something? I attach the Table structure & FTS Image link
Please help me
This is a sample image here


Why is theNEXT button disabled
http://80.42.24.114/you/callbackajax/1.gif
This is also my Table structure
http://80.42.24.114/you/callbackajax/2.gif

The text below is from the BOL(books online) about the extra column requirement to use Image column in Full Text index.
Run a search for Full Text image column in the BOL (bboks online) for more info. Hope this helps.
(Thus, when you set up full-text indexing on an image column in a table, you must create a separate column to hold information about the document. This type column can be of any character-based data type and contains the document file extension, such as doc for a Microsoft Word document. If the type column is NULL, the full-text service will assume the document is a text file.

In the Full-Text Indexing Wizard, if you select an image column for indexing, you must also specify a Binding column to hold the document type.

The sp_fulltext_column stored procedure also accepts an argument for the column to contain the document types.

The sp_help_fulltext_columns stored procedure also returns column name and column id of the document type column. )