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
| USE [GIFT_MANAGEMENT]
GO
/****** Object: StoredProcedure [dbo].[UP_COMMANDE_GET_DETAIL_CRD] Script Date: 11/20/2012 08:49:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UP_COMMANDE_GET_DETAIL_CRD]
@CMD_ID INT
AS
BEGIN;
WITH T1(TCA_ID, CRD_RECHARGEABLE, MINIMA, MAXIMA)
AS(
SELECT
CRD.TCA_ID,
CRD.CRD_RECHARGEABLE,
MIN(CRD_SERIAL) AS 'MINIMA',
MAX(CRD_SERIAL) AS 'MAXIMA'
FROM
T_GIFT_COMMANDE_GFC GFC
INNER JOIN T_CARD_CRD CRD
ON GFC.GFT_ID = CRD.GFT_ID
WHERE
GFC.CMD_ID = @CMD_ID
GROUP BY
CRD.TCA_ID,
CRD_RECHARGEABLE
),
T2(TCA_ID, CRD_RECHARGEABLE, CRD_SERIAL)
AS(
SELECT
CRD.TCA_ID,
CRD.CRD_RECHARGEABLE,
CRD.CRD_SERIAL
FROM
T_GIFT_COMMANDE_GFC GFC
INNER JOIN T_CARD_CRD CRD
ON GFC.GFT_ID = CRD.GFT_ID
INNER JOIN T1
ON T1.TCA_ID = CRD.TCA_ID
WHERE
GFC.CMD_ID = @CMD_ID
),
T3(TCA_ID, SER_ID)
AS(
SELECT
TCA_ID,
SER_ID
FROM
T_SERIAL_SER, T1
WHERE
SER_ID NOT IN (SELECT CRD_SERIAL FROM T2 WHERE TCA_ID = T1.TCA_ID)
AND SER_ID BETWEEN T1.MINIMA AND T1.MAXIMA+1
)
,
T4(TCA_ID, CRD_RECHARGEABLE, MINIMA, MAXIMA, TROU)
AS(
SELECT
T2.TCA_ID,
CRD_RECHARGEABLE,
MIN(T2.CRD_SERIAL) AS 'MINIMA',
MAX(T2.CRD_SERIAL) AS 'MAXIMA',
MIN(T3.SER_ID) AS 'MIN'
FROM
T2
INNER JOIN T3
ON T2.CRD_SERIAL < T3.SER_ID
AND T2.TCA_ID = T3.TCA_ID
WHERE
T2.CRD_SERIAL < T3.SER_ID
GROUP BY
T2.TCA_ID,
CRD_RECHARGEABLE,
T2.CRD_SERIAL
)
SELECT
TCA_ID,
CRD_RECHARGEABLE,
MIN(MINIMA) AS 'MIN',
MAX(MAXIMA) AS 'MAX'
FROM
T4
GROUP BY
TCA_ID,
CRD_RECHARGEABLE,
TROU
END
GO |