SELECT  DATEPART(year, weekdat) AS [year]
		, DATEPART(month, weekdat) AS [month], MAX(weekdat) AS LastFridayInMonth
    FROM (SELECT GetDate() AS weekdat) AS YourTable
GROUP BY  DATEPART(year, weekdat), DATEPART(month, weekdat)
ORDER BY  LastFridayInMonth

year        month       LastFridayInMonth
----------- ----------- -----------------------
2009        11          2009-11-19 10:19:14.873

(1 row(s) affected)

DECLARE @dateCol datetime
SELECT @dateCol = GetDate()

SELECT LastWeekDay 
  FROM (
SELECT DATEADD(mm,1,@dateCol - DAY(@dateCol)+1)-1 AS LastWeekDay
UNION ALL
SELECT DATEADD(mm,1,@dateCol - DAY(@dateCol)+1)-2 AS LastWeekDay
UNION ALL
SELECT DATEADD(mm,1,@dateCol - DAY(@dateCol)+1)-3 AS LastWeekDay
UNION ALL
SELECT DATEADD(mm,1,@dateCol - DAY(@dateCol)+1)-4 AS LastWeekDay
UNION ALL
SELECT DATEADD(mm,1,@dateCol - DAY(@dateCol)+1)-5 AS LastWeekDay
UNION ALL
SELECT DATEADD(mm,1,@dateCol - DAY(@dateCol)+1)-6 AS LastWeekDay
UNION ALL
SELECT DATEADD(mm,1,@dateCol - DAY(@dateCol)+1)-7 AS LastWeekDay
) AS XXX
WHERE DATENAME(WeekDay,LastWeekDay) = 'Friday'

SELECT weekdat
  FROM (
		 SELECT  [Col] = dateadd(month,datediff(month,0,weekdat),0)
				, weekdat
				, [Rank] = rank() OVER(
					PARTITION BY DATEADD(month,DATEDIFF(month,0,weekdat),0) 
						ORDER BY weekdat DESC)
		   FROM  (SELECT GetDate() AS weekdat) AS YourTable
) t1
 WHERE rank = 1

quote:

---

Originally posted by jimf

Supposedly all he has in his tables are Fridays, he just wants the last one from each month. Won't my solution give him that?

Jim

Everyday I learn something that somebody else already knew

---

quote:

---

Originally posted by X002548

ummmm...That's not what I get with the code

---

quote:

---

Originally posted by khtan

http://weblogs.sqlteam.com/peterl/archive/2009/06/17/How-to-get-the-Nth-weekday-of-a-month.aspx

---

KH
[spoiler]Time is always against us[/spoiler]

---

CREATE FUNCTION dbo.fnGetNthWeekdayOfMonth
(
    @theDate DATETIME,
    @theWeekday TINYINT,
    @theNth SMALLINT
)
RETURNS DATETIME
BEGIN
    RETURN  (
                SELECT  theDate
                FROM    (
                            SELECT  DATEADD(DAY, 7 * @theNth - 7 * SIGN(SIGN(@theNth) + 1) +(@theWeekday + 6 - DATEDIFF(DAY, '17530101', DATEADD(MONTH, DATEDIFF(MONTH, @theNth, @theDate), '19000101')) % 7) % 7, DATEADD(MONTH, DATEDIFF(MONTH, @theNth, @theDate), '19000101')) AS theDate
                            WHERE   @theWeekday BETWEEN 1 AND 7
                                    AND @theNth IN (-5, -4, -3, -2, -1, 1, 2, 3, 4, 5)
                        ) AS d
                WHERE   DATEDIFF(MONTH, theDate, @theDate) = 0
            )
END
GO


SELECT dbo.fnGetNthWeekdayOfMonth(GetDate(), 5, -1)
GO

SELECT	DatePart(year, weekdat) as [year], DatePart(month, weekdat) as [month], Max(weekdat) as LastFridayInMonth
FROM	DiaryPct
Group BY
	DatePart(year, weekdat), DatePart(month, weekdat)
ORDER BY
	LastFridayInMonth

DECLARE @listCol VARCHAR(2000)

SELECT  @listCol = STUFF(( SELECT DISTINCT

            '],[' + convert(varchar(10),(Weekdat),1) --converting the weekdat

                        FROM    DiaryPct

		--where (dateadd(ww, -14, getdate()) < weekdat) --getting 13 weeks of data out

                        ORDER BY '],[' + convert(varchar(10),(weekdat),1)

                        FOR XML PATH('')

                                    ), 1, 2, '') + ']';
DECLARE @sql NVARCHAR(2000); 
SET @sql = N' 
WITH Totals AS ( 
SELECT sort, doc, dist, area,CASE 
  WHEN SUM(totovr)= 0 THEN 0 
  WHEN SUM(totpnd) = 0 THEN 0 
  ELSE 
(convert(decimal(6,1),(((((sum(totovr))) * 1.00)  / (sum(totpnd))) * 100))) 
  END AS total, weekdat 
FROM DiaryPct
GROUP BY sort,doc, dist, area,weekdat) 
SELECT sort,doc, dist, area, ' + @listCol + 
N' Into testPivot ' +
N'FROM 
   (SELECT sort,doc, dist, area,total, weekdat 
    FROM Totals) as O ' + 
         N' PIVOT ' + 
N'(SUM(total) FOR weekdat IN (' + @listCol + N')) AS P;'; 


EXEC(@sql);