안녕하세요~
더운 날씨에 고생이 많으십니다.
물류비요일 날짜(적용일자) 에 따라 변하는데
출고를 하게되면 출고일자에 적용일자를 조인하여 출고일자별 요율을 얻고 싶은데 결과가 잘 안나오네요
설명이 헷갈릴 수 있으니 아래 표 참고 부탁드립니다.
| 물류비요율테이블 | 출고테이블 | 원하는결과 | |||||
| 적용일자 | 요율 | 출고일자 | 수량 | 출고일자 | 요율 | ||
| 2023-07-01 | 6 | 2023-07-01 | 1 | 2023-07-01 | 6 | ||
| 2023-07-15 | 12 | 2023-07-02 | 2 | 2023-07-02 | 6 | ||
| 2023-07-03 | 1 | 2023-07-03 | 6 | ||||
| 2023-07-04 | 2 | 2023-07-04 | 6 | ||||
| 2023-07-05 | 1 | 2023-07-05 | 6 | ||||
| 2023-07-06 | 2 | 2023-07-06 | 6 | ||||
| 2023-07-07 | 2 | 2023-07-07 | 6 | ||||
| 2023-07-08 | 1 | 2023-07-08 | 6 | ||||
| 2023-07-09 | 1 | 2023-07-09 | 6 | ||||
| 2023-07-10 | 2 | 2023-07-10 | 6 | ||||
| 2023-07-11 | 2 | 2023-07-11 | 6 | ||||
| 2023-07-12 | 2 | 2023-07-12 | 6 | ||||
| 2023-07-13 | 1 | 2023-07-13 | 6 | ||||
| 2023-07-14 | 2 | 2023-07-14 | 6 | ||||
| 2023-07-15 | 2 | 2023-07-15 | 12 | ||||
| 2023-07-16 | 2 | 2023-07-16 | 12 | ||||
| 2023-07-17 | 2 | 2023-07-17 | 12 | ||||
| 2023-07-18 | 2 | 2023-07-18 | 12 | ||||
| 2023-07-19 | 1 | 2023-07-19 | 12 | ||||
| 2023-07-20 | 2 | 2023-07-20 | 12 | ||||
| 2023-07-21 | 2 | 2023-07-21 | 12 | ||||
| 2023-07-22 | 2 | 2023-07-22 | 12 | ||||
| 2023-07-23 | 2 | 2023-07-23 | 12 | ||||
| 2023-07-24 | 1 | 2023-07-24 | 12 | ||||
| 2023-07-25 | 2 | 2023-07-25 | 12 | ||||
| 2023-07-26 | 2 | 2023-07-26 | 12 | ||||
| 2023-07-27 | 11 | 2023-07-27 | 12 | ||||
| 2023-07-28 | 2 | 2023-07-28 | 12 | ||||
| 2023-07-29 | 2 | 2023-07-29 | 12 | ||||
| 2023-07-30 | 2 | 2023-07-30 | 12 | ||||
| 2023-07-31 | 2 | 2023-07-31 | 12 | ||||
Comment 2
-
버전 때문에 LEAD 함수를 사용 못해서
서브쿼리를 사용했습니다.
selectapplydate
,rate
,(select min(applydate) as applydate from data b where (b.applydate > a.applydate)) as next_row_val
from
data a
row가 많아봤자 10개 안밖이라 서브쿼리도 괜찮다 싶어 사용하려하는데
혹시 대안이 있을까요?
물류비요율테이블을 날짜별로 generate해서 출고테이블과 join 시키는 방법으로 해봤습니다.
-- drop table data
create table data (applydate datetime, rate int);
insert data select
'20230101', 6 union all select
'20230105', 12 union all select
'20230110', 18;
--drop table #new_data
select applydate, rate, LEAD(applydate) OVER (ORDER BY applydate) AS next_row_val
into #new_data from data
select * from #new_data
결과
applydate rate next_row_val
----------------------- ----------- -----------------------
2023-01-01 00:00:00.000 6 2023-01-05 00:00:00.000
2023-01-05 00:00:00.000 12 2023-01-10 00:00:00.000
2023-01-10 00:00:00.000 18 NULL
(3개 행이 영향을 받음)
select dateadd(d,v.number,d.applydate) adate, rate
from #new_data d
join master..spt_values v on v.type='P'
and v.number between 0 and datediff(d, applydate, dateadd(DAY, -1,next_row_val))
order by adate;
결과
adate rate
----------------------- -----------
2023-01-01 00:00:00.000 6
2023-01-02 00:00:00.000 6
2023-01-03 00:00:00.000 6
2023-01-04 00:00:00.000 6
2023-01-05 00:00:00.000 12
2023-01-06 00:00:00.000 12
2023-01-07 00:00:00.000 12
2023-01-08 00:00:00.000 12
2023-01-09 00:00:00.000 12
(9개 행이 영향을 받음)
--drop table #genareated_rate
select dateadd(d,v.number,d.applydate) adate, rate
into #genareated_rate from #new_data d
join master..spt_values v on v.type='P'
and v.number between 0 and datediff(d, applydate, dateadd(DAY, -1,next_row_val))
order by adate;
--drop table invoice
create table invoice(
invoice_date datetime,
amount int
)
insert invoice select
'20230101', 1 union all select
'20230102', 2 union all select
'20230103', 1 union all select
'20230104', 2 union all select
'20230105', 1 union all select
'20230106', 2 union all select
'20230107', 1 union all select
'20230108', 2 union all select
'20230109', 1 union all select
'20230110', 2;
select i.invoice_date, g.rate from invoice i inner join #genareated_rate g on i.invoice_date = g.adate
결과
invoice_date rate
----------------------- -----------
2023-01-01 00:00:00.000 6
2023-01-02 00:00:00.000 6
2023-01-03 00:00:00.000 6
2023-01-04 00:00:00.000 6
2023-01-05 00:00:00.000 12
2023-01-06 00:00:00.000 12
2023-01-07 00:00:00.000 12
2023-01-08 00:00:00.000 12
2023-01-09 00:00:00.000 12