sql server 列举近月每天

转载

网络安全守护先锋 2024-10-21 16:38:11

文章标签 sql server 列举近月每天字段 User 建表 文章分类 SQL Server 数据库

----本日
select cust_order_id ,description,customer_name,order_date,desired_ship_date,
part_description,standard,stock_um ,user_order_qty from  dbo.BS_XSReport 
WHERE (DATEDIFF(d,order_date,GETDATE())=0)
--本周
select cust_order_id ,description,customer_name,order_date,desired_ship_date,
part_description,standard,stock_um ,user_order_qty from  dbo.BS_XSReport
 WHERE    datediff(week,order_date-1,getdate())=0
--本月
select cust_order_id ,description,customer_name,order_date,desired_ship_date,
part_description,standard,stock_um ,user_order_qty from  dbo.BS_XSReport
 WHERE    datediff(mm,order_date,getdate())=0
--年
select cust_order_id ,description,customer_name,order_date,desired_ship_date,
part_description,standard,stock_um ,user_order_qty from  dbo.BS_XSReport
where datediff(year, order_date,getdate())=0

 

 

 


---求相差天数   
select   datediff(day,'2004-01-01',getdate())       
     
--1.一个月第一天的   
SELECT   DATEADD(mm,   DATEDIFF(mm,0,getdate()),   0)   
     
--2.本周的星期一   
SELECT   DATEADD(wk,   DATEDIFF(wk,0,getdate()),   0)   
     
select   dateadd(wk,datediff(wk,0,getdate()),6)  

--3.一年的第一天   
SELECT   DATEADD(yy,   DATEDIFF(yy,0,getdate()),   0)   
     
--4.季度的第一天   
SELECT   DATEADD(qq,   DATEDIFF(qq,0,getdate()),   0)   
     
--5.当天的半夜   
SELECT   DATEADD(dd,   DATEDIFF(dd,0,getdate()),   0)   
     
--6.上个月的最后一天   
SELECT   dateadd(ms,-3,DATEADD(mm,   DATEDIFF(mm,0,getdate()),   0))   
     
--7. 去年的最后一天   
SELECT   dateadd(ms,-3,DATEADD(yy,   DATEDIFF(yy,0,getdate()),   0))   
     
--8.本月的最后一天   
SELECT   dateadd(ms,-3,DATEADD(mm,   DATEDIFF(m,0,getdate())+1,   0))   
     
--9.本年的最后一天   
SELECT   dateadd(ms,-3,DATEADD(yy,   DATEDIFF(yy,0,getdate())+1,   0))   
     
--10.本月的第一个星期一   
select   DATEADD(wk,   
DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())),   0)

--查询本周注册人数   
select   count(*)   from   [user]   
where   datediff(week,create_day-1,getdate())=0   
     
--上周注册人数   
select   count(*)   from   [user]   
where   datediff(week,create_day-1,getdate())=1   
     
     
--本月注册人数   
select   count(*)   from   [user]   
where   datediff(month,create_day,getdate())=0   
     
--上月注册人数   
select   count(*)   from   [user]   
where   datediff(month,create_day,getdate())=1   
   
--如果要效率,这样写查询   
     
--查询本周注册人数   
select   count(*)   from   [user]   
where   create_day>=dateadd(day,2-datepart(weekday,getdate()),convert(varchar,getdate(),112))   
and   create_day<dateadd(day,9-datepart(weekday,getdate()),convert(varchar,getdate(),112))   
     
--上周注册人数   
select   count(*)   from   [user]   
where   create_day>=dateadd(day,-5-datepart(weekday,getdate()),convert(varchar,getdate(),112))   
and   create_day<dateadd(day,2-datepart(weekday,getdate()),convert(varchar,getdate(),112))   
     
     
--本月注册人数   
select   count(*)   from   [user]   
where   create_day>=dateadd(day,1-day(getdate()),convert(varchar,getdate(),112))   
and   create_day<dateadd(month,1,dateadd(day,1-day(getdate()),convert(varchar,getdate(),112)))   
     
--上月注册人数   
select   count(*)   from   [user]   
where   create_day>=dateadd(month,-1,dateadd(day,1-day(getdate()),convert(varchar,getdate(),112)))   
and   create_day<dateadd(day,1-day(getdate()),convert(varchar,getdate(),112))   
   
--本周   
select   count(*)   from   User   
where   datediff(dd,create_day,getdate())   <=   datepart(dw,getdate())   
         
--上周   
select   count(*)   from   User   
where   datediff(dd,create_day,(getdate()   -   datepart(dw,getdate())))   <=   7   
     
--本月   
select   count(*)   from   User   
where   datepart(mm,create_day)   =   datepart(mm,getdate())  

--上月   
select   count(*)   from   User   
where   datepart(mm,create_day)   =   datepart(mm,getdate())   -   1


--上周   
select   count(*)   from   [User]   
where   datediff(dd,create_day,(getdate()   -   datepart(dw,getdate())))   <=   7   
     
--本月   
select   count(*)   from   [User]   
where   datepart(mm,create_day)   =   datepart(mm,getdate())   
--上月   
select   count(*)   from   [User]   
where   datepart(mm,create_day)   =   datepart(mm,getdate())   -   1 
学习   
month(create_day)=month(getdate()) 本月   
month(create_day)=month(getdate())-1   上月

1、每年  
select year(ordertime) AS '年',  
sum(Total) '销售合计'  
from order_list  
group by year(ordertime)  
  
2、每月  
select year(ordertime) '年',  
month(ordertime) '月',  
sum(Total) '销售合计'  
from order_list  
group by year(ordertime),  
month(ordertime)  
  
3、每日  
select year(ordertime) '年',  
month(ordertime) '月',  
day(ordertime) '日',  
sum(Total) '销售合计'  
from order_list  
group by year(ordertime),  
month(ordertime),  
day(ordertime)  
  
另外每日也可以这样:  
select convert(char(8),ordertime,112) dt,  
sum(Total) '销售合计'  
from order_list  
group by convert(char(8),ordertime,112)  
  
  
另外，每月(年、日)的记录条数  
select year(ordertime) '年',  
month(ordertime) '月',  
count(*) '销售记录'  
from order_list  
group by year(ordertime),  
month(ordertime)

SELECT YEAR(日期字段) 年度,SUM(CASE WHEN MONTH(日期字段) =1 THEN 统计的字段 ELSE 0 END) 一月,   
SUM(CASE WHEN MONTH(日期字段) =2 THEN 统计的字段 ELSE 0 END) 二月,
SUM(CASE WHEN MONTH(日期字段) =3 THEN 统计的字段 ELSE 0 END) 三月,
SUM(CASE WHEN MONTH(日期字段) =4 THEN 统计的字段 ELSE 0 END) 四月,
SUM(CASE WHEN MONTH(日期字段) =5 THEN 统计的字段 ELSE 0 END) 五月,
SUM(CASE WHEN MONTH(日期字段) =6 THEN 统计的字段 ELSE 0 END) 六月,
SUM(CASE WHEN MONTH(日期字段) =7 THEN 统计的字段 ELSE 0 END) 七月,
SUM(CASE WHEN MONTH(日期字段) =8 THEN 统计的字段 ELSE 0 END) 八月,
SUM(CASE WHEN MONTH(日期字段) =9 THEN 统计的字段 ELSE 0 END) 九月,
SUM(CASE WHEN MONTH(日期字段) =10 THEN 统计的字段 ELSE 0 END) 十月,
SUM(CASE WHEN MONTH(日期字段) =11 THEN 统计的字段 ELSE 0 END) 十一月,
SUM(CASE WHEN MONTH(日期字段) =12 THEN 统计的字段 ELSE 0 END) 十二月,
    FROM 表

        GROUP BY YEAR(日期字段)

－－如何计算每日合计，每月合计，本年累计
--建表
create table stockin(
   fdate datetime,
   fitemname varchar(100),
   fqtyin decimal(18,2)
)
--加字段
alter table stockin add fmonth datetime
--建资料
insert into stockin values('2003-1-3','AAA',125,'2003-1-31 23:59:59')
insert into stockin values('2003-1-10','AAA',120,'2003-1-31 23:59:59')
insert into stockin values('2003-1-13','AAA',-20,'2003-1-31 23:59:59')
insert into stockin values('2003-1-30','AAA',25,'2003-1-31 23:59:59')
insert into stockin values('2003-2-3','AAA',100,'2003-2-28 23:59:59')
insert into stockin values('2003-3-5','AAA',120,'2003-3-31 23:59:59')
--我查
select fdate 日期, fitemname 项目, fqtyin 发生数量,         --挑日常资料
       (select sum(fqtyin) from stockin where fdate<=s1.fdate) 结余数量
from stockin s1
union
select fdate 日期, '本日发生额' 项目, fqtyin 发生数量,      --挑日结资料
       (select sum(fqtyin) from stockin where fdate<=s2.fdate) 结余数量 
from stockin s2
union
select fmonth 月份, '本月合计' 项目,sum(fqtyin) 发生数量,   --挑月结资料
       (select sum(fqtyin) 
        from 
        (select fmonth,sum(fqtyin) fqtyin from stockin group by fmonth) mm 
         where fmonth<=s3.fmonth) 结余数量
from stockin s3
group by fmonth
union
select fmonth 月份, '本年累计' 项目,                        --年结资料
       (select sum(fqtyin) 
        from (select fmonth,sum(fqtyin) fqtyin from stockin group by fmonth) mm 
        where fmonth<=s4.fmonth) 发生数量,
       (select sum(fqtyin) from stockin where fdate<=s4.fmonth) 结余数量
from stockin s4
group by fmonth

--说明
1.建一辅助字段 fmonth,其用意看看挑本月合计和本年累计就知道了,其值定义很精确用于排序
2.本年累计和本月合计顺序不对，将就着用吧

活到老，学到老。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。