day20 函数，同比，和基础

create table sdata.dbo.onetest(
Order_Source_Name varchar(255)
,Plant_Location_Number varchar(255)
,Plant_Location_Name varchar(255)
,Booked_Date varchar(255)
,Booked_Fiscal_Year varchar(255)
,Booked_Fiscal_Month varchar(255)
,Ordered_Date varchar(255)
,CRSD_Date varchar(255)
,Customer_Account_Number varchar(255)
,Customer_Name varchar(255)
,Product_Number varchar(255)
,Source_Order_Line_Flow_Status varchar(255)
,Fulfillment_Status varchar(255)
,Line_Number varchar(255)
,Line_Type varchar(255)
,Open_Flag varchar(255)
,Sales_Order_Number varchar(255)
,Purchase_Order_Number varchar(255)
,Order_Type varchar(255)
,Product_Name varchar(255)
,Ordered_Qty varchar(255)
,Reserved_Qty varchar(255)
,Open_Qty varchar(255)
,Shipped_Qty varchar(255)
,Order_Source_Description varchar(255)
,Created_By_Login varchar(255)
)

将后面的表数据复制到前面的表里面
select top 50 * into sdata.dbo.tagert from sdata.dbo.onetest

select Product_Number from sdata.dbo.onetest
select * from sdata.dbo.tagert

select (103-51)*1.0/50*100.0

--同比增长率:
--cast强制转换,保留两位小数
select a.total_last
,b.total_now
,(b.total_now-a.total_last)*1.0/(a.total_last)*100.0 huanbilv
,CAST((b.total_now-a.total_last)*1.0/(a.total_last)*100.0 as decimal(18,2))
from
(select COUNT(1) total_last from sdata.dbo.onetest) a
left join
(select COUNT(1) total_now from sdata.dbo.tagert) b
on 1= 1

 

select
a.Product_Number a_Product_Number
,a.total_last
,b.Product_Number b_Product_Number
,b.total_now
,(b.total_now-a.total_last)*1.0/(a.total_last)*100.0 huanbilv
,MAX((b.total_now-a.total_last)*1.0/(a.total_last)*100.0) max_lu
into sdata.dbo.bill_Product
from (select Product_Number, COUNT(1) total_last from sdata.dbo.onetest group by Product_Number) a
left join (select Product_Number ,COUNT(1) total_now from sdata.dbo.onetest group by Product_Number) b
on a.Product_Number =b.Product_Number

where a.total_last<>0
group by a.Product_Number
,a.total_last
,b.Product_Number
,b.total_now

--求最大和最小已经平均
select max(total_last)
,max(total_now)
,cast(AVG(total_now*1.0) as decimal(18,4))
from sdata.dbo.bill_Product