sql server 查询本周、本月所有天数的数据

时间:2024-09-13 11:35:38

查询本月所有的天数:

--本月所有的天数
select convert(varchar(10),dateadd(DAY,t2.number,t1.day),120) day from
(select substring(convert(varchar,GETDATE(),120),1,7)+'-01' day) t1,
(select number from MASTER..spt_values WHERE TYPE='P' AND number>=0 and number<=31) t2
where convert(varchar(10),dateadd(DAY,t2.number,t1.day),120) like substring(convert(varchar,GETDATE(),120),1,7)+'%'

查询本周所有的天数:

 select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 0),120)
union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 1),120)
union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 2),120)
union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 3),120)
union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 4),120)
union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 5),120)
union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 6),120)

示例:

如下图所示,有表MO_Orders,字段:BookTime,Number,Count,有如下数据。

sql server 查询本周、本月所有天数的数据

查询出本周的每天的数据总和,语句如下:

 with t as
(
select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 0),120)
union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 1),120)
union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 2),120)
union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 3),120)
union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 4),120)
union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 5),120)
union all select date=convert(varchar(10),dateadd(wk, datediff(wk,0,getdate()), 6),120)
)
select id=ROW_NUMBER()OVER(ORDER BY t1.date),
DATENAME(weekday,t1.date) as weekday,
t1.date,
Numbers=sum(isnull(t2.Number,0)),
Count=sum(isnull(t2.Count,0))
from t t1
left join
(
select substring(convert(varchar,BookTime,120),1,11) as BookTime,
Number,Count
from MO_Orders
where datediff(wk,BookTime-1,getdate()) = 0
)
t2
on t1.date= substring(convert(varchar,t2.BookTime,120),1,11)
group by t1.date

查询效果如下图,其中 weekday为星期,此图还需替换,稍后补上:
sql server 查询本周、本月所有天数的数据

示例:如下图所示,有表: MO_Orders,字段:BookTime,Cost,Count 。

查询本月的所有数据总和(其中:total=Cost*Count)。

sql server 查询本周、本月所有天数的数据

查询出本月的每天的数据总和,显示每天的,查询语句如下:

 with t as
(
select convert(varchar(10),dateadd(DAY,t2.number,t1.day),120) date from
(select substring(convert(varchar,GETDATE(),120),1,7)+'-01' day) t1,
(select number from MASTER..spt_values WHERE TYPE='P' AND number>=0 and number<=31) t2
where convert(varchar(10),dateadd(DAY,t2.number,t1.day),120) like substring(convert(varchar,GETDATE(),120),1,7)+'%'
)
select id=ROW_NUMBER()OVER(ORDER BY t1.date),
t1.date,
CostTotal=sum(isnull(t2.CostTotal,0))
from t t1
left join
(
select BookTime,sum(CostTotal) as CostTotal from
(
select substring(convert(varchar,BookTime,120),1,11) as BookTime,
Cost*Count as CostTotal
from MO_Orders
where datediff(month,BookTime,getdate()) = 0
) o group by BookTime
) t2
on t1.date= t2.BookTime
group by t1.date

查询结果如下图:
sql server 查询本周、本月所有天数的数据