22,210
社区成员
发帖
与我相关
我的任务
分享
工厂 库地 类型 属性 SKU 批次 名称 入库时间 数量 重量 备注
BJ2006 399 DK RTE 651846 p102329 eeeee 2011-2-7 50 700
BJ2006 399 DK RTE 651846 p102329 eeeee 2011-2-7 30 600
BJ2007 411 DK RTE 646813 p102323 cccc 2011-1-10 50 500
BJ2114 422 EO CRE 218441 p102331 eeeeeee 2010-4-8 15 999
BJ2119 401 RO DFE 218981 p102329 eeeeeeee 2011-3-8 100 213
BJ2119 844 RO DFE 218981 p102329 eeeeeeee 2011-3-8 100 213
有下面六点问题
1.只要工厂等于BJ2006,2007的数据,库地把等于844的删除
2.用今天的日期减去入库时间得出(在库天数)
3.根据在库天数得出""库存天数"",小于30天以下的不要,只要30天以上的
4.SKU包含"65"开头的为"产品属性"为主机,SKU包含64开头的为"产品属性"为显示器,SKU不包含64与65的"产品属性"为其它
5.平台全部为HY
6."工厂"等于BJ2006的事业部全部为PCC,"名称"包含CC的事业部为DCC,不符合这两个条件的事业部全部为OBB
平台 事业部 工厂 库地 SKU 批次 名称 产品属性 入库时间 数量 在库天数 库存天数
HY pcc BJ2006 399 651846 p102329 eeeee 主机 2011-2-7 80 49 30~60
HY dcc BJ2007 411 646813 p102323 cccc 显示器 2011-1-10 50 78 60~90
HY obb BJ2007 422 218441 p102331 eeeeeee 其它 2010-4-8 15 355 90以上
use tempdb;
/*
create table BK
(
工厂 nvarchar(10) not null,
库地 int not null,
类型 nvarchar(10) not null,
属性 nvarchar(10) not null,
SKU int not null,
批次 nvarchar(10) not null,
名称 nvarchar(10) not null,
入库时间 date not null,
数量 int not null,
重量 int not null
);
insert into BK(工厂,库地,类型,属性,SKU,批次,名称,入库时间,数量,重量)
values
('BJ2006',399,'DK','RTE',651846,'p102329','eeeee','2011-2-7',50,700),
('BJ2006',399,'DK','RTE',651846,'p102329','eeeee','2011-2-7',30,600),
('BJ2007',411,'DK','RTE',646813,'p102323','cccc','2011-1-10',50,500),
('BJ2114',422,'EO','CRE',218441,'p102331','eeeeeee','2010-4-8',15,999),
('BJ2119',401,'RO','DFE',218981,'p102329','eeeeeeee','2011-3-8',100,213),
('BJ2119',844,'RO','DFE',218981,'p102329','eeeeeeee','2011-3-8',100,213);
*/
select
MAX(t.[平台]) as [平台],MAX(t.[事业部]) as [事业部],
MAX(t.[工厂]) as [工厂],MAX(t.[库地]) as [库地],
MAX(t.[SKU]) as [SKU],MAX(t.[批次]) as [批次],
MAX(t.[名称]) as [名称],MAX(t.[产品属性]) as [产品属性],
MAX(t.[入库时间]) as [入库时间],SUM(t.[数量]) as [数量],
MAX(t.[在库天数]) as [在库天数],MAX(t.[库存天数]) as [库存天数]
from
(
select 'HY' as [平台],
case
when BK.[工厂] = 'BJ2006' then 'PCC'
when BK.[名称] like '%CC%' then 'DCC'
else 'OBB'
end as [事业部],
BK.[工厂],BK.[库地],BK.[SKU],BK.[批次],BK.[名称],
case
when BK.SKU like '65%' then '主机'
when BK.SKU like '64%' then '显示器'
else '其他'
end as [产品属性],
BK.[入库时间],BK.[数量],
DATEDIFF(DD,BK.[入库时间],CURRENT_TIMESTAMP) as [在库天数],
case
when DATEDIFF(DD,BK.[入库时间],CURRENT_TIMESTAMP) between 30 and 60 then '30~60'
when DATEDIFF(DD,BK.[入库时间],CURRENT_TIMESTAMP) between 60 and 90 then '60~90'
else '90以上'
end as [库存天数]
from BK
where BK.[工厂] in ('BJ2006','BJ2007')
and BK.[库地] <> 844
and DATEDIFF(DD,BK.[入库时间],CURRENT_TIMESTAMP) >= 30
) as t
group by t.[入库时间];
-- ???
create table tb
(
工厂 varchar(10),
库地 int,
类型 varchar(10),
属性 varchar(10),
SKU varchar(10),
批次 varchar(10),
名称 varchar(10),
入库时间 datetime,
数量 int,
重量 int,
备注 varchar(10)
)
insert into tb
select 'BJ2006',399,'DK','RTE','651846','p102329','eeeee','2011-2-7',50,700,'' union all
select 'BJ2006',399,'DK','RTE','651846','p102329','eeeee','2011-2-7',30,600,'' union all
select 'BJ2007',411,'DK','RTE','646813','p102323','cccc','2011-1-10',50,500,'' union all
select 'BJ2114',422,'EO','CRE','218441','p102331','eeeee','2010-4-8',15,999,'' union all
select 'BJ2119',401,'RO','DFE','218981','p102329','eeeeeeee','2011-3-8',100,213,'' union all
select 'BJ2119',844,'RO','DFE','218981','p102329','eeeeeeee','2011-3-8',100,213,''
go
select N'HY' as [平台],
(case when 工厂 = 'BJ2006' then 'PCC' when 工厂 <> 'BJ2006' and charindex('cc',名称) > 0 then 'DCC' else 'OBB' end) as[事业部],
(case when 工厂 = 'BJ2004' then 'BJ2007' else 工厂 end) as 工厂,库地,SKU,批次,名称,
(case when left(SKU,2) = '65' then N'主机' when left(SKU,2) = '64' then N'显示器' else N'其它' end) as [产品属性],
入库时间,数量,
datediff(dd,入库时间,getdate()) as [入库天数],
(case when datediff(dd,入库时间,getdate()) between 30 and 60 then '30~60'
when datediff(dd,入库时间,getdate()) between 30 and 60 then '60~90'
else '90以上' end) as [库存天数]
from tb
where 工厂 in ('BJ2006','BJ2007','BJ2004') and datediff(dd,入库时间,getdate()) >= 30
drop table tb
create table tb
(
工厂 varchar(10),
库地 int,
类型 varchar(10),
属性 varchar(10),
SKU varchar(10),
批次 varchar(10),
名称 varchar(10),
入库时间 datetime,
数量 int,
重量 int,
备注 varchar(10)
)
insert into tb
select 'BJ2006',399,'DK','RTE','651846','p102329','eeeee','2011-2-7',50,700,'' union all
select 'BJ2006',399,'DK','RTE','651846','p102329','eeeee','2011-2-7',30,600,'' union all
select 'BJ2007',411,'DK','RTE','646813','p102323','cccc','2011-1-10',50,500,'' union all
select 'BJ2114',422,'EO','CRE','218441','p102331','eeeee','2010-4-8',15,999,'' union all
select 'BJ2119',401,'RO','DFE','218981','p102329','eeeeeeee','2011-3-8',100,213,'' union all
select 'BJ2119',844,'RO','DFE','218981','p102329','eeeeeeee','2011-3-8',100,213,''
go
select N'HY' as [平台],
(case when 工厂 = 'BJ2006' then 'PCC' when 工厂 <> 'BJ2006' and charindex('cc',名称) > 0 then 'DCC' else 'OBB' end) as[事业部],
工厂,库地,SKU,批次,名称,
(case when left(SKU,2) = '65' then N'主机' when left(SKU,2) = '64' then N'显示器' else N'其它' end) as [产品属性],
入库时间,数量,
datediff(dd,入库时间,getdate()) as [入库天数],
(case when datediff(dd,入库时间,getdate()) between 30 and 60 then '30~60'
when datediff(dd,入库时间,getdate()) between 30 and 60 then '60~90'
else '90以上' end) as [库存天数]
from tb
where 工厂 in ('BJ2006','BJ2007') and datediff(dd,入库时间,getdate()) >= 30
drop table tb