gerald.drouin (8/12/2009)

---

declare @DayToFind tinyint, @AnyDayInAMonth datetime

select @DayToFind = 1, --Monday

@AnyDayInAMonth = '2009-04-15'--Target month

select dateadd(day, (datepart(d, @AnyDayInAMonth) - datepart(dw, @AnyDayInAMonth) - @@datefirst + @DayToFind) % 7, dateadd(day, 1-datepart(d, @AnyDayInAMonth), @AnyDayInAMonth))

Seems like...

Have a look at this:

http://www.simple-talk.com/sql/sql-server-2005/using-and-monitoring-sql-2005-query-notification/

I tried it some time ago but could never get it to work, you might have more luck.

It all depends whether you can reference...

David,

That is true in...

wildh (8/12/2009)

---

One thing to be mindful with - SET @dt = DATEADD(d,1-DAY(@dt),@dt) is that when it's used with getdate() the result includes the time as well as the...

anand.ramanan (8/11/2009)

---

Nigel,

I think you missed my post on page 1. It has as a similar implementation, and it allows to find the first sunday - saturday of...

publicdh-tech (8/11/2009)

---

I still prefer the following to get to the first of the month:

SET @dt = DATEADD(d,1-DAY(@dt),@dt)

Nigel, nice! Your arithmetic is very crafty and gets a solution in...

Just worked out an alternative that avoids one of the the modulus operations

SELECT firstsaturday = DATEADD(dd,

...

No need to change DATEFIRST, create intermediate variables, or a WHILE loop :w00t:.

Just a bit of arithmetic to work out the offset from the first of the month to...

Not a chance, I was sleeping while you were working on this.

Nice solution Jeff, I think I just about get it.

I...

Thank you Jeff....

aktikt (8/5/2009)

---

Nigel,

I tried your second solution on 35923 records but I added the

HAVING clause to restrict the rows to 2006 and 2007 with this:

HAVING YEAR(theDay) > 2005 AND YEAR(theDay) <...

aktikt,

Apologies, just noticed that you don't want it grouped by ClaimID, just the Month and Year.

Try this slightly modified version instead:

SELECT

...

aktikt,

Try this on your data and let us know how it goes (you will need the Tally table):

SELECT

ID,

...

First read Jeff Moden's excellent article on Tally tables here[/url]. Create one, then try this:

DECLARE

@startdate datetime,

...

I've found these from Apress and Wrox to be useful. If you're using SSRS 2005, that is.