显示报告周 [英] displaying reporting weeks
本文介绍了显示报告周的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我不知道如何循环以便查询显示从报告日期开始的所有周?
I dont know how to loop through so that the query displays all the weeks from the reporting date?
代码确定周一至周日,然后应将值插入临时表中,然后查询周.我对 report_date 进行了硬编码,它应该显示不止一条记录.
The code determines the Mon - Sun week then should insert the values in a temp table to then query the weeks. I have hard coded the report_date and its hould display more than one record.
任何想法
DECLARE @REPORT_DATE DATETIME, @WEEK_BEGINING VARCHAR(10)
SELECT @REPORT_DATE = '2011-01-01T00:00:00'
--SELECT @REPORT_DATE = GETDATE() -- should grab the date now.
SELECT @WEEK_BEGINING = 'MONDAY'
IF @WEEK_BEGINING = 'MONDAY'
SET DATEFIRST 1
ELSE IF @WEEK_BEGINING = 'TUESDAY'
SET DATEFIRST 2
ELSE IF @WEEK_BEGINING = 'WEDNESDAY'
SET DATEFIRST 3
ELSE IF @WEEK_BEGINING = 'THURSDAY'
SET DATEFIRST 4
ELSE IF @WEEK_BEGINING = 'FRIDAY'
SET DATEFIRST 5
ELSE IF @WEEK_BEGINING = 'SATURDAY'
SET DATEFIRST 6
ELSE IF @WEEK_BEGINING = 'SUNDAY'
SET DATEFIRST 7
DECLARE @WEEK_START_DATE DATETIME, @WEEK_END_DATE DATETIME
--GET THE WEEK START DATE
SELECT @WEEK_START_DATE = @REPORT_DATE - (DATEPART(DW, @REPORT_DATE) - 1)
--GET THE WEEK END DATE
SELECT @WEEK_END_DATE = @REPORT_DATE + (7 - DATEPART(DW, @REPORT_DATE))
PRINT 'Week Start: ' + CONVERT(VARCHAR, @WEEK_START_DATE)
PRINT 'Week End: ' + CONVERT(VARCHAR, @WEEK_END_DATE)
CREATE TABLE #WeekList
(
month_date date
)
DECLARE @Interval int = 1
INSERT INTO #WeekList SELECT @WEEK_START_DATE
WHILE @Interval < 1
BEGIN
INSERT INTO #WeekList SELECT DATEADD(MONTH, - @Interval, @WEEK_START_DATE)
SET @Interval = @Interval + 1
END
SELECT
--Create the month ID code:
@WEEK_START_DATE AS Start_Week, @WEEK_END_DATE AS End_Week
FROM #WeekList
ORDER BY Start_Week DESC
DROP TABLE #WeekList
推荐答案
使用 DATEADD 并更新您的 @interval 初始化和更新的循环逻辑:
Using DATEADD and updating your @interval initialization and updated loop logic:
DECLARE @REPORT_DATE DATETIME, @WEEK_BEGINING VARCHAR(10)
SELECT @REPORT_DATE = '2011-01-01T00:00:00'
--SELECT @REPORT_DATE = GETDATE() -- should grab the date now.
SELECT @WEEK_BEGINING = 'MONDAY'
IF @WEEK_BEGINING = 'MONDAY'
SET DATEFIRST 1
ELSE IF @WEEK_BEGINING = 'TUESDAY'
SET DATEFIRST 2
ELSE IF @WEEK_BEGINING = 'WEDNESDAY'
SET DATEFIRST 3
ELSE IF @WEEK_BEGINING = 'THURSDAY'
SET DATEFIRST 4
ELSE IF @WEEK_BEGINING = 'FRIDAY'
SET DATEFIRST 5
ELSE IF @WEEK_BEGINING = 'SATURDAY'
SET DATEFIRST 6
ELSE IF @WEEK_BEGINING = 'SUNDAY'
SET DATEFIRST 7
DECLARE @WEEK_START_DATE DATETIME, @WEEK_END_DATE DATETIME
--GET THE WEEK START DATE
SELECT @WEEK_START_DATE = @REPORT_DATE - (DATEPART(DW, @REPORT_DATE) - 1)
--GET THE WEEK END DATE
SELECT @WEEK_END_DATE = @REPORT_DATE + (7 - DATEPART(DW, @REPORT_DATE))
PRINT 'Week Start: ' + CONVERT(VARCHAR, @WEEK_START_DATE)
PRINT 'Week End: ' + CONVERT(VARCHAR, @WEEK_END_DATE)
DECLARE @Interval int = datediff(WEEK,getdate(),@WEEK_START_DATE)+1
SELECT Start_Week=@WEEK_START_DATE
, End_Week=@WEEK_END_DATE
INTO #WeekList
WHILE @Interval <= 0
BEGIN
set @WEEK_START_DATE=DATEADD(WEEK,1,@WEEK_START_DATE)
set @WEEK_END_DATE=DATEADD(WEEK,1,@WEEK_END_DATE)
INSERT INTO #WeekList values (@WEEK_START_DATE,@WEEK_END_DATE)
SET @Interval += 1;
END
SELECT *
FROM #WeekList
ORDER BY Start_Week DESC
DROP TABLE #WeekList
结果(列表的前 5 名和后 5 名):
Results (top 5 and bottom 5 of list):
Start_Week End_Week
----------------------- -----------------------
2012-03-12 00:00:00.000 2012-03-18 00:00:00.000
2012-03-05 00:00:00.000 2012-03-11 00:00:00.000
2012-02-27 00:00:00.000 2012-03-04 00:00:00.000
2012-02-20 00:00:00.000 2012-02-26 00:00:00.000
2012-02-13 00:00:00.000 2012-02-19 00:00:00.000
...
2011-01-24 00:00:00.000 2011-01-30 00:00:00.000
2011-01-17 00:00:00.000 2011-01-23 00:00:00.000
2011-01-10 00:00:00.000 2011-01-16 00:00:00.000
2011-01-03 00:00:00.000 2011-01-09 00:00:00.000
2010-12-27 00:00:00.000 2011-01-02 00:00:00.000
顺便说一句,如果您不需要存储时间,您也可以使用 date
类型代替 Datetime
.
As an aside, you could also use the date
type instead of Datetime
, if you don't need to store the time.
这篇关于显示报告周的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文