2020-08-05 查询七天各个商品出库情况

select F_MaterielCode,sum(C_QtyIn) as Qty,C_Name , (select Convert(varchar(10),T_Out_OutboundDetail.C_CreateTime,112)) as CreteTime  INTO #t1  
from T_Out_OutboundDetail inner join T_Base_Materiel on F_MaterielCode = C_Code   where 
             T_Base_Materiel.C_IsDelete = 0 and T_Out_OutBoundDetail.C_IsDelete = 0 group by F_MaterielCode,C_Name ,T_Out_OutboundDetail.C_CreateTime
             select F_MaterielCode,SUM(Qty) AS Qty, C_Name, CreteTime into #t2 from  #t1 group by CreteTime,F_MaterielCode,C_Name

select a.riqi, Qty,F_MaterielCode,C_Name  from 
(
select ( Convert(varchar(10),GETDATE(),112)) as riqi union all
select convert(char,dateadd(dd,-1,(select Convert(varchar(10),GETDATE(),112)) ),112) as riqi UNION ALL

select convert(char,dateadd(dd,-2,( Convert(varchar(10),GETDATE(),112))),112) as riqi union all

select convert(char,dateadd(dd,-3,( Convert(varchar(10),GETDATE(),112))),112) as riqi union all

select convert(char,dateadd(dd,-4,( Convert(varchar(10),GETDATE(),112))),112) as riqi union all

select convert(char,dateadd(dd,-5,( Convert(varchar(10),GETDATE(),112))),112) as  riqi union all
select convert(char,dateadd(dd,-6,( Convert(varchar(10),GETDATE(),112))),112) as  riqi 
) a 
LEFT JOIN ( select F_MaterielCode,
CreteTime as CreateTime,
C_Name,
sum(Qty) as Qty from #t2
 group by CreteTime,F_MaterielCode,C_Name
) b on b.CreateTime=a.riqi