当天、三天、七天数据统计

最新推荐文章于 2022-07-07 14:04:45 发布

周道

最新推荐文章于 2022-07-07 14:04:45 发布

阅读量430

点赞数

分类专栏： SQLServer

本文链接：https://blog.csdn.net/lemon627497658/article/details/81094500

版权

SQLServer 专栏收录该内容

25 篇文章 0 订阅

订阅专栏

语句一：

with s as (
           select cast(getdate() as date) as 'shift_date',1 as 'do_code','当天入库' as 'type'
           union all
           select cast(getdate() as date) as 'shift_date',0 as 'do_code','当天出库' as 'type'
           union all
           select cast(getdate() as date) as 'shift_date',1 as 'do_code','3天入库' as 'type'
           union all
           select cast(getdate() as date) as 'shift_date',0 as 'do_code','3天出库' as 'type'
           union all
           select cast(dateadd(dd,-1,getdate()) as date) as 'shift_date',1 as 'do_code','3天入库' as 'type'
           union all
           select cast(dateadd(dd,-1,getdate()) as date) as 'shift_date',0 as 'do_code','3天出库' as 'type'
           union all
           select cast(dateadd(dd,-2,getdate()) as date) as 'shift_date',1 as 'do_code','3天入库' as 'type'
           union all
           select cast(dateadd(dd,-2,getdate()) as date) as 'shift_date',0 as 'do_code','3天出库' as 'type'
           union all
           select cast(getdate() as date) as 'shift_date',1 as 'do_code','7天入库' as 'type'
           union all
           select cast(getdate() as date) as 'shift_date',0 as 'do_code','7天出库' as 'type'
           union all
           select cast(dateadd(dd,-1,getdate()) as date) as 'shift_date',1 as 'do_code','7天入库' as 'type'
           union all
           select cast(dateadd(dd,-1,getdate()) as date) as 'shift_date',0 as 'do_code','7天出库' as 'type'
           union all
           select cast(dateadd(dd,-2,getdate()) as date) as 'shift_date',1 as 'do_code','7天入库' as 'type'
           union all
           select cast(dateadd(dd,-2,getdate()) as date) as 'shift_date',0 as 'do_code','7天出库' as 'type'
           union all
           select cast(dateadd(dd,-3,getdate()) as date) as 'shift_date',1 as 'do_code','7天入库' as 'type'
           union all
           select cast(dateadd(dd,-3,getdate()) as date) as 'shift_date',0 as 'do_code','7天出库' as 'type'
           union all
           select cast(dateadd(dd,-4,getdate()) as date) as 'shift_date',1 as 'do_code','7天入库' as 'type'
           union all
           select cast(dateadd(dd,-4,getdate()) as date) as 'shift_date',0 as 'do_code','7天出库' as 'type'
           union all
           select cast(dateadd(dd,-5,getdate()) as date) as 'shift_date',1 as 'do_code','7天入库' as 'type'
           union all
           select cast(dateadd(dd,-5,getdate()) as date) as 'shift_date',0 as 'do_code','7天出库' as 'type'
           union all
           select cast(dateadd(dd,-6,getdate()) as date) as 'shift_date',1 as 'do_code','7天入库' as 'type'
           union all
           select cast(dateadd(dd,-6,getdate()) as date) as 'shift_date',0 as 'do_code','7天出库' as 'type')

SELECT s.type,sum(hand_num) as 'in_out_qty'

FROM [zws].[dbo].[zws] a left join s on a.shift_date=s.shift_date and a.do_code=s.do_code
where a.shift_date>='2018-07-10'
group by s.type
order by s.type

语句二：

select *,sum(qty) over(partition by right(shift_date,2) order by qty) as 'new_qty'
from
(SELECT case when shift_date=cast(getdate() as date) and do_code=1 then '当天入库'
when shift_date=cast(getdate() as date) and do_code=0 then '当天出库'
when shift_date<=cast(getdate() as date) and shift_date>=cast(dateadd(dd,-2,getdate()) as date) and do_code=1 then '3天入库'
           when shift_date<=cast(getdate() as date) and shift_date>=cast(dateadd(dd,-2,getdate()) as date) and do_code=0 then '3天出库'
    when shift_date<=cast(getdate() as date) and shift_date>=cast(dateadd(dd,-7,getdate()) as date) and do_code=1 then '7天入库'
           when shift_date<=cast(getdate() as date) and shift_date>=cast(dateadd(dd,-7,getdate()) as date) and do_code=0 then '7天出库' end as 'shift_date'
,sum(hand_num) 'qty'

FROM [zws].[dbo].[zws]
where shift_date>='2018-07-10'
group by case when shift_date=cast(getdate() as date) and do_code=1 then '当天入库'
               when shift_date=cast(getdate() as date) and do_code=0 then '当天出库'
               when shift_date<=cast(getdate() as date) and shift_date>=cast(dateadd(dd,-2,getdate()) as date) and do_code=1 then '3天入库'
               when shift_date<=cast(getdate() as date) and shift_date>=cast(dateadd(dd,-2,getdate()) as date) and do_code=0 then '3天出库'
                when shift_date<=cast(getdate() as date) and shift_date>=cast(dateadd(dd,-7,getdate()) as date) and do_code=1 then '7天入库'
               when shift_date<=cast(getdate() as date) and shift_date>=cast(dateadd(dd,-7,getdate()) as date) and do_code=0 then '7天出库' end

) s