--------------------------------------------
-- 내용 : 매달 배송 물량 체크 (Junior)
--------------------------------------------

--****************
-- Junior 3 개월 배송 물량
--****************
SELECT
	di.dStatus,
	count(di.dCode)
FROM
	DeliveryInfo di
	join (SELECT ssCode FROM SettleInfo WHERE settleStatus in ('1','2') GROUP BY ssCode) si on di.ssCode = si.ssCode
	join SubScription ss on di.ssCode = ss.ssCode
	join Goods g on ss.gCode = g.gCode and g.gTypeCode = 1
WHERE
	di.tYear = '2007' and
	di.tMonth = '02' and
	ss.tMonth = 3 and 
	(ss.mCode not in(44123, 44122, 44124, 44126, 44125, 44127, 41991,41989,41977,32111,46582) or ss.mCode is null) and
	ss.mTypeCode = 19  and ss.gTypeCode = 1
GROUP BY
	di.dStatus

--****************
-- Junior 6 개월 배송 물량
--****************
SELECT
	di.dStatus,
	count(di.dCode)
FROM
	DeliveryInfo di
	join (SELECT ssCode FROM SettleInfo WHERE settleStatus in ('1','2') GROUP BY ssCode) si on di.ssCode = si.ssCode
	join SubScription ss on di.ssCode = ss.ssCode
	join Goods g on ss.gCode = g.gCode and g.gTypeCode = 1
WHERE
	di.tYear = '2007' and
	di.tMonth = '02' and
	ss.tMonth = 6 and 
	(ss.mCode not in(44123, 44122, 44124, 44126, 44125, 44127, 41991,41989,41977,32111,46582) or ss.mCode is null) and
	ss.mTypeCode = 19  and ss.gTypeCode = 1
GROUP BY
	di.dStatus

--****************
-- Junior 12 개월 배송 물량
--****************
SELECT
	di.dStatus,
	count(di.dCode)
FROM
	DeliveryInfo di
	join (SELECT ssCode FROM SettleInfo WHERE settleStatus in ('1','2') GROUP BY ssCode) si on di.ssCode = si.ssCode
	join SubScription ss on di.ssCode = ss.ssCode
	join Goods g on ss.gCode = g.gCode and g.gTypeCode = 1
WHERE
	di.tYear = '2007' and
	di.tMonth = '02' and
	ss.tMonth = 12 and 
	(ss.mCode not in(44123, 44122, 44124, 44126, 44125, 44127, 41991,41989,41977,32111,46582) or ss.mCode is null) and
	ss.mTypeCode = 19  and ss.gTypeCode = 1
GROUP BY
	di.dStatus

--****************
-- Junior 24 개월 배송 물량
--****************
SELECT
	di.dStatus,
	count(di.dCode)
FROM
	DeliveryInfo di
	join (SELECT ssCode FROM SettleInfo WHERE settleStatus in ('1','2') GROUP BY ssCode) si on di.ssCode = si.ssCode
	join SubScription ss on di.ssCode = ss.ssCode
	join Goods g on ss.gCode = g.gCode and g.gTypeCode = 1
WHERE
	di.tYear = '2007' and
	di.tMonth = '02' and
	ss.tMonth = 24 and 
	(ss.mCode not in(44123, 44122, 44124, 44126, 44125, 44127, 41991,41989,41977,32111,46582) or ss.mCode is null) and
	ss.mTypeCode = 19  and ss.gTypeCode = 1
GROUP BY
	di.dStatus