day 06 整理排序

--单子国家品牌日期 订单数
select
country_id
,product_group_ops
--,case when service_order_creation_date ='00:00.0' then null
-- else CAST(service_order_creation_date as date) end
,CONVERT(varchar(7),case when service_order_creation_date ='00:00.0' then null
else CAST(service_order_creation_date as date) end,120) month
,COUNT(1) num_sell
into sdata.dbo.cost_detail_month_sell_num
from sdata.dbo.cost_detail
group by
country_id
,product_group_ops
,CONVERT(varchar(7),case when service_order_creation_date ='00:00.0' then null
else CAST(service_order_creation_date as date) end,120)

 

select * from sdata.dbo.billing_data

---汇率转换

drop table sdata.dbo.billing_data_deal
select product_group_ops,country_id,invoice,a.billing_month,cast(a.total_amount as float)*cast(b.loc_to_usd as float) money
into sdata.dbo.billing_data_deal
from sdata.dbo.billing_data a
left join sdata.dbo.month_rate b
on a.currency= b.currency
and cast(a.billing_month as DATE)= cast(b.month as DATE)

---各维度上的总钱
drop table sdata.dbo.billing_data_sum
select
country_id
,product_group_ops
,CONVERT(varchar(7),cast(billing_month as DATE),120) month
,SUM(money) money
into sdata.dbo.billing_data_sum
from
sdata.dbo.billing_data_deal
group by country_id
,product_group_ops
,CONVERT(varchar(7),cast(billing_month as DATE),120)

--取最大日期--
select distinct
Country_ID,Product_Group_Ops
,CONVERT(varchar(7),case when service_order_creation_date ='00:00.0' then null
else CAST(service_order_creation_date as date) end,120) month
into sdata.dbo.max_month
from sdata.dbo.cost_detail

---最新账单日期
select
*
into sdata.dbo.billing_data_seq
from (
select
*
,ROW_NUMBER() OVER(PARTITION BY Country_ID,Product_Group_Ops order by month desc ) as row
from sdata.dbo.billing_data_sum
where money is not null
) a
where row =1

select * from sdata.dbo.billing_data_seq

select * from sdata.dbo.billing_data_sum

--计算钱数且赋值给五月 --

select
a.*
,case when CONVERT(varchar(7),CAST(max_date as DATE),120)=a.month
then (case when b.money IS not null then b.money
when b.money IS null and a.month >= c.month then c.money
else null end ) *(day(CAST(max_date as DATE))*1.0/DAY(DATEADD(MONTH,1,CONVERT(varchar(6),CAST(max_date as DATE),112) + '01 ')-1))
else (case when b.money IS not null then b.money
when b.money IS null and a.month >= c.month then c.money
else null end ) end money
into sdata.dbo.billing_data_sum_forecast
from sdata.dbo.max_month a
left join sdata.dbo.billing_data_sum b
on a.COUNTRY_ID = b.Country_ID
and a.PRODUCT_GROUP_OPS = b.Product_Group_Ops
and a.month= b.month
left join sdata.dbo.billing_data_seq c
on a.COUNTRY_ID = c.Country_ID
and a.PRODUCT_GROUP_OPS = c.Product_Group_Ops
left join sdata.dbo.max_date d
on 1=1
order by a.COUNTRY_ID asc ,a.month asc

--各个维度上平均的钱--
drop table sdata.dbo.avg_sell_money
select
a.country_id
,a.month
,a.product_group_ops
,a.money
,isnull(b.num_sell,0) num_sell
,case when isnull(b.num_sell,0)= 0 then 0
else a.money/b.num_sell end avg_money
into sdata.dbo.avg_sell_money
from sdata.dbo.billing_data_sum_forecast a
left join
sdata.dbo.cost_detail_month_sell_num b
on a.country_id = b.country_id
and a.month =b.month
and a.product_group_ops=b.product_group_ops

 

---每个单子的钱
select * from sdata.dbo.cost_detail a
left join sdata.dbo.avg_sell_money b
on a.country_id=b.country_id
and a.product_group_ops =b.product_group_ops
and CONVERT(varchar(7),case when service_order_creation_date ='00:00.0' then null
else CAST(service_order_creation_date as date) end,120) =b.month
where b.avg_money is not null

 

3 1000 40 25
4 1000 10 100
5 1000*23/31 20 ....

RRR --- 7
ECT

 

 

 

--小细节--

select
DAY(DATEADD(MONTH,1,CONVERT(varchar(6),'20180509',112) + '01 ')-1) --一个月有多少天
select day('20180509')

--
select MAX(SERVICE_ORDER_CREATION_DATE) max_date into sdata.dbo.max_date from sdata.dbo.cost_detail

--这个月几天占这个月的百分比--

select
day(CAST(max_date as DATE))*1.0/DAY(DATEADD(MONTH,1,CONVERT(varchar(6),CAST(max_date as DATE),112) + '01 ')-1) motn
from
sdata.dbo.max_date