프로시져 연습

/*매출정보 procedure 1 날짜별 (0,0)
      2.상품별 (A,01) (b,03)
      3.연령별 (인자값,null) */
--1. 날짜별
create proc soldinfo_date @date_from nchar(8),@date_to nchar(8)  
as
 select substring(o.outcode,4,10) as '날짜',
   sum (od.qty*od.price) as '상품별 매출액' from orderdetail od inner join [output] o  --결제가 되어야 매출이 이뤄지므로
   on od.ocode=o.ocode                 --결제시 생성되는 outcode로 일정기간동안 매출을
   where substring(o.outcode,4,10) > convert(datetime,@date_from)                      --처리
         or substring(o.outcode,4,10) < convert(datetime,@date_to) --from에서 to까지 날동안의 매출액
 group by substring(o.outcode,4,10)

exec soldinfo_date '20080205','20080606'

--2. 상품별
create proc soldinfo_pt @pt_category nchar(1),@pt_num nchar(2)  --product 카테고리+넘버
as
select 
       p.pcode as '매출상품코드',
       sum(od.qty*od.price) as '상품별 총 매출액' 
 from [order] o inner join orderdetail od  -- order와ordertail을 조인하여 상품별 총 매출액을 구할 수 있다
  inner join product p                  -- product를 멀티 조인하여 상품 값을 구할수 있다.
  on p.pcode=p.pcode
where o.payment=1 and p.pcode=(@pt_category + @pt_num)
group by p.pcode

exec soldinfo_pt 'd','01'

--3. 연령별
create proc soldinfo_age  @age nvarchar(3)
as
select m.ages as '연령대', 
       sum(od.[sum]) as '매출액'
 from [order] o inner join 
    (select qty*price as 'sum',
      ocode from orderdetail) od --판매된 아이템 총가격과 ocode,를 orderdetail에서 
  on o.ocode=od.ocode                               
  inner join (select (age/10)*10 as 'ages',
      id from member) m --연령대와 id를 멤버테이블에서
  on m.id=o.id
where m.ages=@age
group by m.ages

exec soldinfo_age '20'
-------------------------------------------------------------------
--4.재고정보-재고량(a,01) <현재재고, 안전재고, 여유재고(현재-안전재고)>

create proc stock @pt_category nchar(1), @pt_num nchar(2)
AS
select pcode as '상품코드', 
       pqty as '현재재고',
    safeqty as '안전재고',
    (pqty-safeqty) as '여유재고' from product
where pcode=@pt_category+@pt_num

exec stock 'd','01'
-----------------------------------------------------------------------
/*고객정보 1. 주문정보 (고객id,[오늘,1주일,1개월]) ; 결제 x
    2. 상품구매현황 ; 결제o */

-- 5.주문정보 : 결제 x
create view order_detail
as  
select o.*, od.pcode, od.qty, od.price, od.pay                        --order와 orderdetail의 join 부분을 뷰로 만들었다.
  from [order] o inner join 
     (select *,(qty*price) as 'pay' from orderdetail) od   
     on o.ocode=od.ocode

create proc cs_data 
@id nvarchar(10),
@date nvarchar(6)
as

if @date='오늘'
 begin
  select * from order_detail where id=@id 
   and substring(ocode,2,10)=(convert(nchar(10),Getdate(),120))   -- 오늘
 end
else if @date='1주일'
 begin
  select * from order_detail where id=@id  
   and substring(ocode,2,10)<=(convert(nchar(10),Getdate()+7,120)) -- 1주일
 end
else if @date='1개월' 
 begin
  select * from order_detail where id=@id 
   and substring(ocode,2,10)<=(convert(nchar(10),dateadd(mm,1,Getdate()),120)) -- 1개월
 end
else
 begin
  select '잘못된 날짜 입니다'
 end

exec cs_data 'khdba37-13','1개월'
-- 7.상품구매현황 : 결제o

alter proc cs_data 
@id nvarchar(10),
@date nvarchar(6)
as
if @date='오늘'
 begin
  select od.* from order_detail od inner join output op
                 on od.ocode=op.ocode where od.id=@id                    -- 6번사항에서 만든 view에 output을 조인 시켰다.
   and substring(op.outcode,2,10)=(convert(nchar(10),Getdate(),120))   -- 상품구매현황이기 때문에 payment시 생성되는 outcode를 이용했다.
 end
else if @date='1주일'
 begin
  select od.* from order_detail od inner join output op
                 on od.ocode=op.ocode where od.id=@id  
   and substring(op.outcode,2,10)<=(convert(nchar(10),Getdate()+7,120)) -- 1주일
 end
else if @date='1개월' 
 begin
  select od.* from order_detail od inner join output op
                 on od.ocode=op.ocode where od.id=@id 
   and substring(op.outcode,2,10)<=(convert(nchar(10),dateadd(mm,1,Getdate()),120)) -- 1개월
 end
else
 begin
  select '잘못된 날짜 입니다'
 end

exec cs_data 'khdba37-13','1개월'

-- 8.출고현황 (과거or미래,날짜,날짜)
alter proc shipping
@date bit, 
@from nchar(10),
@to  nchar(10)
as
  if @date=0
    begin
     select * from output where  
       (convert(datetime,@from)<=edate and convert(datetime,@to)>=edate)
    end
  else if @date=1
   begin
    select * from output where edate='미출고' and 
      (convert(datetime,@from)<=reserve and convert(datetime,@to)>=reserve)
   end
 
exec shipping '1','20040204','20080507'