|  |  |  |  | 1| 2|<- week1
 3| 4| 5| 6| 7| 8| 9|<- week2
10|11|12|13|14|15|16|<- week3
17|18|19|20|21|22|23|<- week4
24|25|26|27|28|29|30|<- week5
30|  |  |  |  |  |  |<- week6

select	weekno = (datediff(day, '17530101', date1) / 7) - (datediff(day, '17530101', '2009-08-01') / 7) + 1, 
	sum(quantity)
from	#Tempplaydate
where	date1	>= '2009-08-01'
and	date1	<= '2009-08-31'
group by datediff(day, '17530101', date1) / 7

quote:

---

'17530101'

---

SELECT	weekno, SUM(quantity)
FROM	(	
		select	row_number() over ( order by date1)-convert(int, date1)%7 as weekno,
				quantity 
		from	#Tempplaydate
		)a
GROUP BY weekno

quote:

---

where you learn all this thing

---

quote:

---

Originally posted by waterduck

i think i get it.....where you learn all this thing...btw the following are half done by me.....doesn't know how to make the week number become nice...haha

SELECT	weekno, SUM(quantity)
FROM	(	
		select	row_number() over ( order by date1)-convert(int, date1)%7 as weekno,
				quantity 
		from	#Tempplaydate
		)a
GROUP BY weekno

this query does not gives you what you want. The weekno is wrong

---

Hope can help...but advise to wait pros with confirmation...

---

;with data (weekno, mth, date1, quantity)
as
(
	select	weekno = datediff(day, '17530101', date1) / 7, 
		mth	= dateadd(month, datediff(month, 0, date1), 0),
		date1, 
		quantity
	from	#Tempplaydate
)
select	[Year]	= year(mth),
	[Month]	= month(mth),
	[Week]	= row_number() over (partition by min(mth) order by weekno),
	[Sum]	= sum(quantity)
from	data
group by mth, weekno

DECLARE	@STARTDATE	DATETIME
DECLARE	@ENDDATE	DATETIME

SELECT	@STARTDATE = '1/1/2009'
SELECT	@ENDDATE = '12/31/2009'

DECLARE @TEMP TABLE ( DATE DATETIME )
INSERT INTO @TEMP 
SELECT	DATEADD(DAY, NUMBER, @STARTDATE)
FROM		MASTER..SPT_VALUES
WHERE		[TYPE] = 'P'
		AND DATEADD(DAY, NUMBER, @STARTDATE) <= @ENDDATE

CREATE TABLE #DETAILS ( [MONTH] VARCHAR(32),  DAY_NAME VARCHAR(32), [DAY] INT, MONTH_NUM INT ,WEEK_NUM INT,[WEEKDAY] INT)
INSERT INTO #DETAILS
SELECT	DATENAME(M,DATE),DATENAME(DW,DATE),	DAY(DATE), MONTH(DATE),DATEPART(WW,DATE),DATEPART(DW,DATE) FROM @TEMP

DECLARE @STRCOLS VARCHAR(MAX),@VALUES VARCHAR(MAX),@STRING VARCHAR(MAX)
SELECT @STRCOLS = '',@VALUES = '' ,@STRING = ''

SELECT @STRCOLS = @STRCOLS + ', MAX([' + CAST([WEEKDAY] AS VARCHAR(32)) + ']) AS '+ DAY_NAME,
		@VALUES = @VALUES + ', ['+ CAST([WEEKDAY] AS VARCHAR(32)) +']'
FROM
	( SELECT DISTINCT TOP 100 PERCENT [WEEKDAY],DAY_NAME FROM #DETAILS ORDER BY [WEEKDAY])T

	SELECT @STRING = '
		SELECT 
			Month' + @STRCOLS+'			
		FROM #DETAILS
		PIVOT(MAX(DAY) FOR [WEEKDAY] IN (' + STUFF(@VALUES,1,1,'') + '))P
		GROUP BY WEEK_NUM,[MONTH],MONTH_NUM
		ORDER BY MONTH_NUM'
		
		PRINT(@STRING)

		EXEC (@STRING)

DECLARE	@STARTDATE	DATETIME
DECLARE	@ENDDATE	DATETIME

SELECT	@STARTDATE = '1/1/2009'
SELECT	@ENDDATE = '12/31/2009'

DECLARE @TEMP TABLE ( DATE DATETIME )
INSERT INTO @TEMP 
SELECT	DATEADD(DAY, NUMBER, @STARTDATE)
FROM		MASTER..SPT_VALUES
WHERE		[TYPE] = 'P'
		AND DATEADD(DAY, NUMBER, @STARTDATE) <= @ENDDATE

CREATE TABLE #DETAILS ( [MONTH] VARCHAR(32),  DAY_NAME VARCHAR(32), [DAY] INT, MONTH_NUM INT ,WEEK_NUM INT,[WEEKDAY] INT)
INSERT INTO #DETAILS
SELECT	DATENAME(M,DATE),DATENAME(DW,DATE),	DAY(DATE), MONTH(DATE),DATEPART(WW,DATE),DATEPART(DW,DATE) FROM @TEMP

SELECT [Month],MAX([1]) AS 'Sunday', MAX([2]) AS 'Monday', MAX([3]) AS 'Tuesday',MAX([4]) AS 'Wednesday', 
		MAX([5]) AS 'Thursday',MAX([6]) AS 'Friday', MAX([7]) AS 'Saturday'
FROM ( SELECT * FROM #DETAILS) P
PIVOT ( MAX([DAY]) FOR [WEEKDAY] IN ([1],[2],[3],[4],[5],[6],[7]))P
GROUP BY WEEK_NUM,[MONTH],MONTH_NUM
ORDER BY MONTH_NUM

quote:

---

Originally posted by waterduck

thx Nageswar9 ^^

---

Hope can help...but advise to wait pros with confirmation...

---