我有这段代码需要帮助。代码的关键是将所有日期打印在一张表中,并在每周结束后中断。本周从星期一开始。我的sql代码打印出所有内容,但我不确定如何使用循环来实现其余内容
SELECT name, comment, hrs, CONVERT(varchar(20), timestamp, 120) AS timestamp, DATEADD(day, - DATEPART(weekday, timestamp), timestamp) AS Week_Start,
CONVERT(varchar(10), DATEPART(ww, DATEADD(dd, 0, timestamp))) AS WeekNo
FROM Tablename
WHERE MONTH(timestamp) = '$month'
and YEAR(timestamp) = '$year'
order by wk
假设我们的日期为2001/01/01至2001/01/31
我想让所有的日子按周分组,例如,如果2001/01/01是星期天,我想分组如下:
Day Date Name Hrs
Mon 2016-03-01 Username 8
Tue 2016-03-02 Username 8
Wed 2016-03-03 Username 9
Thu 2016-03-04 Username 10
Fri 2016-03-05 Username 10
Sat 2016-03-06 -
Sun 2016-03-07 Username 10
----------------------------------
Mon 2016-03-08 Username 8
Tue 2016-03-09 -
Wed 2016-03-10 -
Thu 2016-03-11 -
Fri 2016-03-12 Username 10
Sat 2016-03-13 -
Sun 2016-03-15 Username 10
我使用的是PHP/MS SQL 2008。
每周之后我该如何休息?
谢谢你的帮助。
按CTRL+T将查询输出更改为文本。
SET NOCOUNT ON
SELECT name,
comment,
hrs,
CONVERT(varchar(20), timestamp, 120) AS timestamp,
DATEADD(day, - DATEPART(weekday, timestamp), timestamp) AS Week_Start,
CONVERT(varchar(10), DATEPART(ww, DATEADD(dd, 0, timestamp))) AS WeekNo
INTO #TempTableName
FROM Tablename
WHERE MONTH(timestamp) = '$month'
and YEAR(timestamp) = '$year'
order by wk
while ((select count(*) from #TempTableName) > 0)
begin
select top 7 * from #TempTableName
delete top (7) from #TempTableName
end