SQL Group Dates Between selected days of the week

Question

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.

Check out DATEFIRST msdn.microsoft.com/en-us/library/ms181598.aspx — Brian Rudolph
Assuming SQL Server - You can set the first day of the week to be whichever day you wish using SET DATEFIRST X Replace X with the day you want (1 = Monday but does depend on your language settings)) — Fred
@marc_s Sorry about that, I have updated. Sql-server 2008. Going to take a look at DateFirst thank you guys — clamchoda
@BrianRudolph and marc_s Thank you very much, this seems to be the solution! — clamchoda

Bhavin Jani Bhavin Jani · Accepted Answer · 2016-07-29T14:36:07

hi here is your desire solution in this blog as it works perfect for me

 -- Declaring variables to store from and to datetime values
declare @fromDate datetime, @toDate datetime
declare @dtToTempDate datetime

 --Declaring a variable to store difference count
declare @count int

--Assigning values to @fromDate and @toDate
set @fromDate =  '10/6/2013'
 set @toDate = '10/13/2013'

--if @fromDate is sunday then adding 1 day to it to increment 
 --the date value to move to Monday
 --if @fromDate is saturday then adding 2 day to it to increment 
 --the date value to move to Monday
if datepart(dw, @fromDate)=1
       set @fromDate=@fromDate+1
else if datepart(dw, @fromDate)=7
   set @fromDate=@fromDate+2

--storing the @toDate value in a temp variable
set @dtToTempDate = @toDate

--if @fromDate is sunday then substracting 2 days to it to 
 --decrement the date value to move to Friday
 --if @fromDate is saturday then substracting 1 day to it to 
--decrement the date value to move to Friday
if datepart(dw, @toDate)=1
    set @toDate=@toDate-2
else if datepart(dw, @toDate)=7
    set @toDate=@toDate-1

 --Difference between newly calculated @fromDate and @toDate    
select @count = datediff(dd, @fromDate, @toDate) - (datediff(wk, @fromDate, @toDate) * 2)

--Adding 1 to @count if @toDate was falling in Saturday or 
--Sunday    
select @count=case when datepart(dw, @dtToTempDate) in (1,7) then @count+1 else @count end

--Displaying the result 

select @count

http://dotnetblue.blogspot.in/2013/10/calculate-difference-between-two-dates.html i hope it may give you desired result

SQL Group Dates Between selected days of the week

2 Answers