USE [HWDB]
GO
/****** 개체:  StoredProcedure [dbo].[SP_PM3000_S]    스크립트 날짜: 06/18/2009 07:58:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  <개쉛기,,>
-- Create date: <2009,6,16>
-- Deion: <2009,6,16>
-- =============================================
ALTER PROCEDURE [dbo].[SP_PM3000_S]
@TYPE INT,
@START_DATE SMALLDATETIME,
@END_DATE SMALLDATETIME

AS

BEGIN
/* FIND ALL */
 IF(@TYPE < 0)
 BEGIN

 DECLARE @SUM_AMOUNT1 VARCHAR(100)
 SET @SUM_AMOUNT1 = \'전체조회\'

 SELECT
 PM3000.IN_DATE  AS \'발생일자\',
 PM3101.TEXTVALUE AS \'폐기물종류\',
 Convert(varchar(5), PM3000.POLLUTANT_STATUS) AS \'성상\',
 PM3000.POLLUTANT_AMOUNT AS \'발생량\',
 @SUM_AMOUNT1   AS \'발생누계\',
 PM3000.ISDISPOSED AS \'위탁처리\'

 FROM PM3000

 INNER JOIN PM3101
 ON PM3000.POLLUTANT_TYPE = PM3101.AUTO_KEY

 WHERE PM3000.IN_DATE BETWEEN @START_DATE AND @END_DATE
/* FIND ALL _ END */
END

ELSE
BEGIN

/* FIRST DATE OF YEAR */
DECLARE @DATE1 SMALLDATETIME
SET @DATE1 = CONVERT(SMALLDATETIME, CONVERT(CHAR(4),YEAR(@START_DATE))+\'-1-1\')

/* FIRST DATE IN PERIOD */
DECLARE @DATE2 SMALLDATETIME
SET @DATE2 = (SELECT TOP 1 IN_DATE FROM PM3000
WHERE IN_DATE > @START_DATE ORDER BY IN_DATE ASC)

/* TOTAL \'POLLUTANT_AMOUNT\' & DISPOSED */
DECLARE @TOTAL_P NUMERIC(14,2)
SET @TOTAL_P =
(SELECT SUM(POLLUTANT_AMOUNT) FROM PM3000
WHERE POLLUTANT_TYPE = @TYPE AND IN_DATE <= @DATE2)

DECLARE @TOTAL_D NUMERIC(14,2)
IF((SELECT DISPOSE_AMOUNT COUNT FROM PM3100
WHERE POLLUTANT_TYPE = @TYPE AND IN_DATE <= @DATE2)>0)
BEGIN
 SET @TOTAL_D =
 (SELECT SUM(DISPOSE_AMOUNT) FROM PM3100
 WHERE POLLUTANT_TYPE = @TYPE AND IN_DATE <= @DATE2)
END
ELSE
 SET @TOTAL_D = 0

DECLARE @POLLUTANT_STOCK NUMERIC(14,2)
SET @POLLUTANT_STOCK = @TOTAL_P - @TOTAL_D

DECLARE @SUM_AMOUNT2 NUMERIC(12,2)
SET @SUM_AMOUNT2 =
(SELECT SUM(POLLUTANT_AMOUNT) FROM PM3000
WHERE IN_DATE BETWEEN @DATE1 AND @DATE2
AND POLLUTANT_TYPE = @TYPE)

SELECT
PM3000.IN_DATE   AS \'발생일자\',
PM3101.TEXTVALUE  AS \'폐기물종류\',
Convert(varchar(5), PM3000.POLLUTANT_STATUS) AS \'성상\',
PM3000.POLLUTANT_AMOUNT AS \'발생량\',
@SUM_AMOUNT2   AS \'발생누계\',
PM3100.DISPOSE_AMOUNT AS \'위탁량\',
@POLLUTANT_STOCK  AS \'보관량\'
FROM PM3000

INNER JOIN PM3101
ON PM3000.POLLUTANT_TYPE = PM3101.AUTO_KEY

LEFT OUTER JOIN PM3100
ON PM3000.POLLUTANT_TYPE = PM3100.POLLUTANT_TYPE
AND PM3000.IN_DATE = PM3100.IN_DATE

WHERE PM3000.POLLUTANT_TYPE = @TYPE
AND PM3000.IN_DATE BETWEEN @START_DATE AND @END_DATE

END
END

USE [HWDB]
GO
/****** 개체:  StoredProcedure [dbo].[SP_PM3100_S]    스크립트 날짜: 06/18/2009 11:56:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  <이재훈,,Name>
-- Create date: <2009,6,16>
-- Deion: <2009,6,16>
-- =============================================
ALTER PROCEDURE [dbo].[SP_PM3100_S]

@TYPE INT,
@START_DATE SMALLDATETIME,
@END_DATE SMALLDATETIME

AS
BEGIN

IF(@TYPE < 0)
BEGIN

SELECT
PM3100.IN_DATE   AS \'처리일자\',
PM3101.TEXTVALUE  AS \'폐기물종류\',
PM3102.TEXTVALUE  AS \'운반자\',
PM3103.TEXTVALUE  AS \'처리자\',
PM3104.TEXTVALUE  AS \'처리방법\',
PM3100.DISPOSE_AMOUNT AS \'처리량\'
FROM PM3100

INNER JOIN PM3101
ON PM3100.POLLUTANT_TYPE = PM3101.AUTO_KEY

INNER JOIN PM3102
ON PM3100.CONVAYANCER = PM3102.AUTO_KEY

INNER JOIN PM3103
ON PM3100.DISPOSER = PM3103.AUTO_KEY

INNER JOIN PM3104
ON PM3100.DISPOSE_SYSTEM = PM3104.AUTO_KEY

WHERE PM3100.IN_DATE BETWEEN @START_DATE AND @END_DATE
END
ELSE
BEGIN

SELECT
PM3100.IN_DATE   AS \'처리일자\',
PM3101.TEXTVALUE  AS \'폐기물종류\',
PM3102.TEXTVALUE  AS \'운반자\',
PM3103.TEXTVALUE  AS \'처리자\',
PM3104.TEXTVALUE  AS \'처리방법\',
PM3100.DISPOSE_AMOUNT AS \'처리량\'
FROM PM3100

INNER JOIN PM3101
ON PM3100.POLLUTANT_TYPE = PM3101.AUTO_KEY

INNER JOIN PM3102
ON PM3100.CONVAYANCER = PM3102.AUTO_KEY

INNER JOIN PM3103
ON PM3100.DISPOSER = PM3103.AUTO_KEY

INNER JOIN PM3104
ON PM3100.DISPOSE_SYSTEM = PM3104.AUTO_KEY

WHERE POLLUTANT_TYPE = @TYPE
AND PM3100.IN_DATE BETWEEN @START_DATE AND @END_DATE
END
END



여섯시간동안 짠건데 쉬박 ㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠ

무결성은 유지되지만 제약조건도 안걸려있고 중복된키가 많아서 6테이블 리모델링 ㅠㅠㅠㅠㅠㅠㅠㅠㅠ

ㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠㅠ 엉ㅎㅎ힣ㅇ허흥ㅎ