SQL组日期在一周的选定日期之间-Java 学习之路

我要求允许最终用户按天，月和选定的工作日对数据进行分组 .

对于按月分组，我想出了

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)

对于Group by Day来说很容易;

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

最后一个是我坚持的 . 周 . 最终用户可以从周一，周二，周三，周四，周五，周六和周日的单选按钮列表中进行选择 . 如果他们选择星期六，它应该在星期六开始的星期组 . 如果他们选择星期三，它应该在周三开始分组 .

我想出了一个让我按周分组的解决方案：

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)

但这只是周日 - 周日 .

我想要完成甚至可能吗？这是针对sql server 2008的 .

2 回答

嗨，这是你在这个博客中的渴望解决方案，因为它对我来说非常适合

-- 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我希望它可以给你想要的结果

回复于 2024-04-26T05:27:18+08:00

1

我认为你工作有点太难以得到一周的日子，你可以在sql中轻松地做到：

DATEPART（WEEKDAY，DateColumn1）

回复于 2024-04-26T05:27:18+08:00

SQL组日期在一周的选定日期之间

2 回答

相关问题