I have a requirement to allow end users to group data by Day, Month, and selected week days.

For group by month I have came up with

```
SELECT StoreNum,
       StoreName,
       DATEADD(MONTH, DATEDIFF(MONTH, 0, CloseDate), 0) As 'Day', 
       SUM(Price) 
 FROM tbl_checktable 
 WHERE Type = 8027 
AND OwnerID = 32 
 AND CloseDate BETWEEN '2015-02-07' AND '2015-03-19'
Group By StoreNum,StoreName,DATEADD(MONTH, DATEDIFF(MONTH, 0, CloseDate), 0)

```

For Group by Day was easy;

```
SELECT 
  StoreNum,
  StoreName,
  closeDate, 
  SUM(Price) 
FROM tbl_checktable 
WHERE Type = 3046 
AND OwnerID = 32 
AND CloseDate BETWEEN '2015-02-07' AND '2015-03-19'
Group By StoreNum,StoreName,CloseDate

```

The last one is what I am stuck on. Week. End user can choose from a radio button list either Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, and Sunday. If they choose Saturday, it should group on week starting Saturdays. If they choose Wednesday it should group on week starting Wednesday.

I have came up with a solution that lets me group by week:

```
CREATE FUNCTION dbo.yearweek(@date date)
RETURNS INT
as
 begin
    set @date = dateadd(dd,-datepart(dw,@date)+1, @date)

    return datepart(year,@date)*100 + datepart(week,@date)
 end
go

SELECT 
  StoreNum,
  StoreName,
  dbo.yearweek(closeDate), 
   SUM(Price) 
FROM tbl_checktable 
WHERE Type = 8027 
AND OwnerID = 32 
AND CloseDate BETWEEN '2015-02-07' AND '2015-03-19'
Group By StoreNum,StoreName, dbo.yearweek(CloseDate)

```

But this only does sunday - sunday. 

Is what I want to accomplish even possible? 
This is for sql server 2008.