1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
|
SELECT
distinct 'XXX' as FlowType ,
rh.delivery_memo,
NULL as sender,
rh.store_code_id as dest,
rh.creation_date as dt,
rdl.product_id,
rdl.color_id,
rdl.size_id,
abs(rdl.loose_qty_rm) as qty,
NULL as reason_id,
rdl.landed_price as avg_cost,
rdl.price_retail as retail_price
from rm_header rh (nolock)
inner join rm_detail_loose rdl (nolock) on rh.rm_number = rdl.rm_number and rh.store_code_id = rdl.store_code_id
--and rh.store_code_id = 88
where
(
(convert(int,convert(char(8), rh.creation_date, 112)) <= convert(int,convert(char(8), @date, 112))
and rh.rm_status = 'R' and rh.reception_status = 'N'
)
or
(convert(int,convert(char(8), rh.creation_date, 112)) <= convert(int,convert(char(8), @date, 112))
and
convert(int,convert(char(8), rh.modified_date, 112)) > convert(int,convert(char(8),@date,112)) and
rh.rm_status = 'R' and rh.reception_status = 'C'
)
)
and ( rh.delivery_memo is null or ( rh.delivery_memo is not null and rh.delivery_memo not like 'ZZZ%'))
union all
SELECT
distinct 'YYY' as FlowType,
NULL as delivery_memo,
th.transfer_from_store_id as sender,
th.transfer_to_store_id as dest,
th.transfer_date as dt,
bd.product_id,
bd.color_id,
bd.size_id,
case when th.transfer_status = 3 then abs(bd.bin_qty_stocks)
when th.transfer_status = 5 then abs(troutd.quantity) end as qty, th.reason_id as reason_id
, TROD_PriceCost as avg_cost, Cession_Price as retail_price
from bin_header bh (nolock)
inner join bin_detail bd (nolock) on bh.bin_id = bd.bin_id and bh.store_code_id = bd.store_code_id and bd.store_code_id in (select store_code_id from store (nolock) where store_type = 'V')
inner join transfer_header th (nolock) on bh.bin_id = th.transfer_number
inner join trout_header trout (nolock) on trout.transfer_number = th.transfer_number
inner join trout_detail troutd (nolock) on trout.transdate= troutd.transdate and trout.store_code_id = troutd.store_code_id
and trout.till_id=troutd.till_id and trout.transnum=troutd.transnum and trout.transtime=troutd.transtime
and troutd.product_id = bd.product_id and troutd.color_id = bd.color_id and troutd.size_id = bd.size_id
where ((th.transfer_status = 3 and convert(int,convert(char(8), th.transfer_date, 112)) <= convert(int,@date))
or
(th.transfer_status = 5 and convert(int,convert(char(8), th.transfer_date, 112)) <= convert(int,@date)
and not exists (select 'f' from trin_header inh where th.transfer_number = inh.transnum and
th.transfer_to_store_id = inh.store_code_id and
convert(char(8), inh.transdate, 112) <= CONVERT(char(8),@date,112))
))
and trout.approved = 'Y'
union all
SELECT
distinct 'ZZZ' as FlowType,
rmh.delivery_memo,
rh.store_code_id as sender,
sdest.store_code_id as dest,
rmh.creation_date as dt,
rd.product_id,
rd.color_id,
rd.size_id,
abs(rd.loose_qty_rtv) as qty, r.reason_id,
rdl.landed_price as avg_cost, rdl.price_retail as retail_price
from
rtv_header rh (nolock)
inner join store s1 (nolock) on rh.store_code_id = s1.store_code_id
inner join rtv_detail_loose rd (nolock) on rh.rtv_number = rd.rtv_number
inner join reasons r (nolock) on r.reason_id = rh.reason_id
inner join store sdest (nolock) on convert(nvarchar(8),sdest.store_code_id) = r.mq_queue_sender
inner join rm_header rmh (nolock) on rtrim(rmh.delivery_memo) like 'VIS%'+ rtrim(convert(varchar,rh.rtv_number))
inner join rm_detail_loose rdl (nolock) on rmh.rm_number = rdl.rm_number and rmh.store_code_id = rdl.store_code_id
and rd.product_id = rdl.product_id and rd.color_id = rdl.color_id and rd.size_id = rdl.size_id
where
(
(convert(int,convert(char(8), rmh.creation_date, 112)) <= convert(int,convert(char(8), @date, 112))
and rmh.rm_status = 'R' and rmh.reception_status = 'N'
)
or
(convert(int,convert(char(8), rmh.creation_date, 112)) <= convert(int,convert(char(8), @date, 112))
and
convert(int,convert(char(8), rmh.modified_date, 112)) > convert(int,convert(char(8),@date,112)) and
rmh.rm_status = 'R' and rmh.reception_status = 'C'
)
)
and rh.approved = 'Y' and convert(int,convert(char(8), rh.creation_date, 112)) <= convert(int,convert(char(8), @date, 112))
and r.description like 'ZZZ%' |
Optimisation Union All SQL SERVER 2k5
Répondre avec citation
Partager