?? 附件入庫銷售庫存情況查詢.sql
字號:
/***********************************************
統計出每種類型的進貨
@fall 標記是否查找萬能ALL,所有的機型（萬能機型和適用機型）BFJ只查詢有適用機型，WLJ只查詢適用萬能機型的
@fpjzl all,所有的配件資料當對配件資料進行選擇后，就不為ALL
@act
1:查看總表
2:查看明細表
**************************************************/
--select f.fid,r.fmon,r.fnum,r.fdate, from tfjrc r
declare @ffdate datetime
declare @fedate datetime
declare @fpjzl char(5)--配件資料all,所有的配件資料
declare @fadr char(4)--配件貨源地
declare @fname char(5)--配件廠商
declare @fpjlx char(2)--配件類型--附件類型 ALL為所有附件類型
declare @fjx char(5)--標記查找的機型'ALL為所有'
declare @fall char(3)--標記是否查找萬能ALL,所有的機型（萬能機型和適用機型）BFJ只查詢有適用機型，WLJ只查詢適用萬能機型的
declare @act int
declare @lx char(2)
DECLARE @slx CHAR(2)--銷售類型 （贈品，還是銷售）AL為所有銷售類型
declare @temp table
(
flx char(2),
fid char(12),
fpjzl char(5),
fmon money,
fnum int,
fdate datetime,
fadr char(4),
fmemo char(200)
)
declare @temppjzl table
(
fid char(12)
)
declare @tempcount table
(
flx char(2),
fpjzl char(5),
ffjlx char(10),
ffjcs char(15),
fjx char(20),
fnum int
)
declare @tempwx table
(
flx char(2),
fpjzl char(5),
ffjlx char(10),
ffjcs char(16),
fjx char(20),
fnum int,
fxc int,
fwxnum int
)
set @ffdate='2001-01-01'
set @fedate='2005-12-30'
set @fpjzl='000'
set @fadr='ALL'
set @fname='ALL'
set @fpjlx='AL'
set @fjx='ALL'
set @fall='ALL'
set @act=2
set @lx='AL'
set @slx='AL'
if @slx='AL'
set @slx='%%'
if @lx='AL'
set @lx='%%'
if @fadr='ALL'
set @fadr='%%'
if @fname='ALL'
set @fname='%%'
if @fpjlx='AL'
set @fpjlx='%%'
if @fjx='ALL'
set @fjx='%%'
--得到配件資料
if @fpjzl='ALL'
begin
insert into @temp
select 'FJ' AS flx,fid,fpjzl,fmon,fnum,fdate,fadr,fmemo
from tfjrc
where fdate>=@ffdate and fdate<=@fedate
and fadr like ltrim(rtrim(@fadr))
insert into @temp
select 'FS' as flx,fid,fpjzl,fmon,fnum,fdate,ffxlx,fmemo
from tfjxc
where fdate>=@ffdate and fdate<=@fedate and ffxlx like ltrim(rtrim(@slx))
--select * from tfjxc
end
if @fpjzl<>'ALL'
begin
if @fall='ALL'--當不是查找所有的附件類型 資料時，但是附件的適用機型又是所有的情況
begin
insert into @temppjzl
select fid
from tpjzl
where fname like ltrim(rtrim(@fname ))
and fpjlx like ltrim(rtrim(@fpjlx))
--and fjx like ltrim(rtrim(@fjx))
--union
--select fid
--from tpjzl
--where fname like ltrim(rtrim(@fname ))
--and fpjlx like ltrim(rtrim(@fpjlx))
--and fjx is null
end
if @fall='BFJ'--只查部分適用機型
insert into @temppjzl
select fid
from tpjzl
where fname like ltrim(rtrim(@fname ))
and fpjlx like ltrim(rtrim(@fpjlx))
and fjx like ltrim(rtrim(@fjx))
if @fall='WLJ'--只查部分適用機型
insert into @temppjzl
select fid
from tpjzl
where fname like ltrim(rtrim(@fname ))
and fpjlx like ltrim(rtrim(@fpjlx))
and fjx is null
insert into @temp
select 'FJ' as flx,fid,fpjzl,fmon,fnum,fdate,fadr,fmemo
from tfjrc
where fdate>=@ffdate and fdate<=@fedate
and fadr like ltrim(rtrim(@fadr))
and fpjzl in (select fid from @temppjzl)
--得到銷售的數據
insert into @temp
select 'FS' as flx,fid,fpjzl,fmon,fnum,fdate,ffxlx,fmemo
from tfjxc
where fdate>=@ffdate and fdate<=@fedate and fpjzl in (select fid from @temppjzl)
and ffxlx like ltrim(rtrim(@slx))
end
insert into @tempcount
select a.flx,a.fpjzl,b.ffjlx,b.ffjcs,b.fjx,a.fnum
from(
--統計出總數
select top 10000 flx,fpjzl,sum(fnum) as fnum
from @temp
group by flx,fpjzl
order by flx,fpjzl
)a,(
--查看附件類型
select top 10000 p.fid,ltrim(rtrim(tfjcs.fna))as ffjcs,ltrim(rtrim(f.fname)) as ffjlx,
ltrim(rtrim((case
when p.fjx is null then '萬能'
when p.fjx is not null then (select ltrim(rtrim(tsjcs.fna))+ltrim(rtrim(tsjxh.fmodel)) from tsjxh,tsjcs where tsjcs.fid=tsjxh.fsjcs and tsjxh.fid=p.fjx)
end))) as fjx
from tpjzl p,tfjlx f,tfjcs
where f.fid=p.fpjlx and tfjcs.fid=p.fname
)b
where a.fpjzl=b.fid --and a.flx like @lx
order by a.flx,b.ffjlx,b.ffjcs,b.fjx
--得到未銷的數據
insert into @tempwx
select 'FW' as flx,a.fpjzl,a.ffjlx,a.ffjcs,a.fjx,a.fnum,isnull(b.fxcnum,0)as fxcnum,isnull(b.fwxnum,a.fnum) as fwxnum
from(
select top 10000 *
from @tempcount a
where a.flx='FJ'
)a left outer join
(
select top 10000 a.fpjzl,a.ffjlx,a.ffjcs,a.fjx,b.fnum as fxcnum,(a.fnum-b.fnum) as fwxnum
from
(
select top 10000 *
from @tempcount a
where a.flx='FJ'
)a,
(
select top 10000 *
from @tempcount a
where a.flx='FS'
)b
where a.fpjzl=b.fpjzl
order by a.ffjlx,a.ffjcs,a.fjx
)b
on a.fpjzl=b.fpjzl
if @act=1
begin
--統計出每種型號的數據量
select a.flx,
(case a.flx
when 'FJ' then '進貨'
when 'FS' then '銷售'
when 'FW' then '庫存'
end
) as flxcn,
a.ffjlx,a.fjx,a.fallcount
from
(
select top 10000 a.*
from
(
select top 10000 b.flx,ltrim(rtrim(b.ffjlx)) as ffjlx,ltrim(rtrim(b.fjx)) as fjx,sum(b.fnum) as fallcount
from @tempcount b
group by b.flx,b.ffjlx,b.fjx
order by b.flx,ltrim(rtrim(b.ffjlx)),ltrim(rtrim(b.fjx))
)a
union
select top 10000 a.*
from
(
select top 10000 a.flx,ltrim(rtrim(a.ffjlx)) as ffjlx,ltrim(rtrim(a.fjx)) as fjx,sum(fwxnum) as fallcount
from @tempwx a
group by a.flx,a.ffjlx,a.fjx
order by a.flx,ltrim(rtrim(a.ffjlx)),ltrim(rtrim(a.fjx))
)a
)a
where a.flx like @lx
order by a.flx
end
if @act=2
begin
select a.flx,
(case a.flx
when 'FJ' then '進貨'
when 'FS' then '銷售'
when 'FW' then '庫存'
end
) as flxcn,
a.ffjlx,a.fjx,a.fallcount,a.ffjcs,a.fnum
from(
select top 10000 a.*
from (
SELECT top 10000 a.flx,ltrim(rtrim(a.ffjlx)) as ffjlx,ltrim(rtrim(a.fjx)) as fjx,a.fallcount,ltrim(rtrim(b.ffjcs)) as ffjcs,b.fwxnum as fnum
from
(
select top 10000 a.flx,ltrim(rtrim(a.ffjlx)) as ffjlx,ltrim(rtrim(a.fjx)) as fjx,sum(fwxnum) as fallcount
from @tempwx a
group by a.flx,a.ffjlx,a.fjx
--order by a.flx,ltrim(rtrim(a.ffjlx)),ltrim(rtrim(a.fjx))
)A,@TEMPWX B
WHERE a.ffjlx=b.ffjlx and a.fjx=b.fjx
--order by a.flx,b.ffjlx,b.fjx,b.ffjcs
)a
union
select top 10000 a.*
from (
--統計出總數據報表
select top 10000 a.flx,ltrim(rtrim(a.ffjlx))as ffjlx,ltrim(rtrim(a.fjx)) as fjx,a.fallcount,ltrim(rtrim(b.ffjcs)) as ffjcs,b.fnum
from
(
select top 1000 b.flx,b.ffjlx,b.fjx,sum(b.fnum) as fallcount
from @tempcount b
group by b.flx,b.ffjlx,b.fjx
--order by b.flx,ltrim(rtrim(b.ffjlx)),ltrim(rtrim(b.fjx))
)a,
(
select top 10000 a.flx,a.fpjzl,b.ffjlx,b.ffjcs,b.fjx,a.fnum
from(
--統計出總數
select top 10000 flx,fpjzl,sum(fnum) as fnum
from @temp
group by flx,fpjzl
--order by flx,fpjzl
)a,(
--查看附件類型
select top 10000 p.fid,ltrim(rtrim(tfjcs.fna))as ffjcs,ltrim(rtrim(f.fname)) as ffjlx,
ltrim(rtrim((case
when p.fjx is null then '萬能'
when p.fjx is not null then (select ltrim(rtrim(tsjcs.fna))+ltrim(rtrim(tsjxh.fmodel)) from tsjxh,tsjcs where tsjcs.fid=tsjxh.fsjcs and tsjxh.fid=p.fjx)
end))) as fjx
from tpjzl p,tfjlx f,tfjcs
where f.fid=p.fpjlx and tfjcs.fid=p.fname
)b
where a.fpjzl=b.fid
--order by a.flx,b.ffjlx,b.ffjcs,b.fjx
)b
where a.flx=b.flx and ltrim(rtrim(a.ffjlx))=ltrim(rtrim(b.ffjlx)) and ltrim(rtrim(a.fjx))=ltrim(rtrim(b.fjx))
--order by a.flx,a.ffjlx,a.fjx,b.ffjcs
)a
)a
where a.flx like @lx
order by a.flx,a.ffjlx,a.fjx,a.ffjcs
end
/***********************
**************************/
if @act=3
begin
select a.flx,
(case a.flx
when 'FJ' then '進貨'
when 'FS' then '銷售'
when 'FW' then '庫存'
end
) as flxcn,
a.ffjlx,a.fjx,a.fallcount,a.ffjcs,a.fnum,a.fdate,a.fnumb,a.fhyd,a.fmemo
from
(
SELECT TOP 10000 a.* ,null as fdate,null as fnumb,null as fhyd, null as fmemo
FROM
(
SELECT top 10000 a.flx,ltrim(rtrim(a.ffjlx)) as ffjlx,ltrim(rtrim(a.fjx)) as fjx,a.fallcount,ltrim(rtrim(b.ffjcs)) as ffjcs,b.fwxnum as fnum
from
(
select top 10000 a.flx,ltrim(rtrim(a.ffjlx)) as ffjlx,ltrim(rtrim(a.fjx)) as fjx,sum(fwxnum) as fallcount
from @tempwx a
group by a.flx,a.ffjlx,a.fjx
--order by a.flx,ltrim(rtrim(a.ffjlx)),ltrim(rtrim(a.fjx))
)A,@TEMPWX B
WHERE a.ffjlx=b.ffjlx and a.fjx=b.fjx
--order by a.flx,b.ffjlx,b.fjx,b.ffjcs
)a
union
SELECT TOP 10000 *
FROM
(
select a.flx,a.ffjlx,a.fjx,a.fallcount,a.ffjcs,a.fnum,b.fdate,b.fnumb,b.fhyd,b.fmemo
from
(
select top 10000 a.flx,ltrim(rtrim(a.ffjlx))as ffjlx,ltrim(rtrim(a.fjx)) as fjx,a.fallcount,ltrim(rtrim(b.ffjcs)) as ffjcs,b.fnum
from
(
select top 1000 b.flx,b.ffjlx,b.fjx,sum(b.fnum) as fallcount
from @tempcount b
group by b.flx,b.ffjlx,b.fjx
--order by b.flx,ltrim(rtrim(b.ffjlx)),ltrim(rtrim(b.fjx))
)a,
(
select top 10000 a.flx,a.fpjzl,b.ffjlx,b.ffjcs,b.fjx,a.fnum
from(
--統計出總數
select top 10000 flx,fpjzl,sum(fnum) as fnum
from @temp
group by flx,fpjzl
--order by flx,fpjzl
)a,(
--查看附件類型
select top 10000 p.fid,ltrim(rtrim(tfjcs.fna))as ffjcs,ltrim(rtrim(f.fname)) as ffjlx,
ltrim(rtrim((case
when p.fjx is null then '萬能'
when p.fjx is not null then (select ltrim(rtrim(tsjcs.fna))+ltrim(rtrim(tsjxh.fmodel)) from tsjxh,tsjcs where tsjcs.fid=tsjxh.fsjcs and tsjxh.fid=p.fjx)
end))) as fjx
from tpjzl p,tfjlx f,tfjcs
where f.fid=p.fpjlx and tfjcs.fid=p.fname
)b
where a.fpjzl=b.fid
--order by a.flx,b.ffjlx,b.ffjcs,b.fjx
)b
where a.flx=b.flx and ltrim(rtrim(a.ffjlx))=ltrim(rtrim(b.ffjlx)) and ltrim(rtrim(a.fjx))=ltrim(rtrim(b.fjx))
--order by a.flx,a.ffjlx,a.fjx,b.ffjcs
)a,
(
select top 10000 a.*,b.fdate,b.fnum as fnumb,
(
case
when substring(b.fadr,1,1)='H' then (select fna from thyd where ltrim(rtrim(fid))=B.FADR)
when substring(b.fadr,1,1)<>'H' then (select fname from tfxlx where fid=fadr)
end
) as fhyd,b.fmemo
--substring(b.fadr,1,1) as fadr
from
(
select a.flx,a.fpjzl,b.ffjlx,b.ffjcs,b.fjx,a.fnum
from(
--統計出總數
select top 10000 flx,fpjzl,sum(fnum) as fnum
from @temp
group by flx,fpjzl
--order by flx,fpjzl
)a,(
--查看附件類型
select top 10000 p.fid,ltrim(rtrim(tfjcs.fna))as ffjcs,ltrim(rtrim(f.fname)) as ffjlx,
ltrim(rtrim((case
when p.fjx is null then '萬能'
when p.fjx is not null then (select ltrim(rtrim(tsjcs.fna))+ltrim(rtrim(tsjxh.fmodel)) from tsjxh,tsjcs where tsjcs.fid=tsjxh.fsjcs and tsjxh.fid=p.fjx)
end))) as fjx
from tpjzl p,tfjlx f,tfjcs
where f.fid=p.fpjlx and tfjcs.fid=p.fname
)b
where a.fpjzl=b.fid
)a,@temp b
where a.flx=b.flx and a.fpjzl=b.fpjzl
--order by a.flx,a.ffjlx,a.ffjcs,a.fjx
)b
where a.flx=b.flx and a.ffjlx=b.ffjlx and a.ffjcs=b.ffjcs and a.fjx=b.fjx
--order by a.flx,a.ffjlx,a.fjx,a.ffjcs,b.fdate,b.fhyd
)A
)a
where a.flx like @lx
order by a.flx,a.ffjlx,a.fjx,a.ffjcs,a.fdate,a.fhyd
end
--select * from @temp?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -