sql para agrupar fechas que esten en rango de 3 fechas
DECLARE @TBL_Fechas TABLE
(
TF_Fecha DATE
,TN_Monto MONEY
)
INSERT INTO @TBL_Fechas
SELECT
CAST('2014-10-12' AS DATE)
,10
INSERT INTO @TBL_Fechas
SELECT
CAST('2014-10-13' AS DATE)
,5
INSERT INTO @TBL_Fechas
SELECT
CAST('2014-10-14' AS DATE)
,20
INSERT INTO @TBL_Fechas
SELECT
CAST('2014-10-15' AS DATE)
,1
INSERT INTO @TBL_Fechas
SELECT
CAST('2014-10-16' AS DATE)
,70
SELECT
*
,(SELECT SUM(TL.TN_Monto) FROM @TBL_Fechas TL WHERE TL.TF_Fecha BETWEEN CTE.TF_Fecha AND CTE.TF_FIN)
FROM
(
SELECT
TF_Fecha
,DATEADD(DAY,2,TF_Fecha ) AS TF_FIN
,ROW_NUMBER() OVER (ORDER BY TF_Fecha) AS 'TN_Num_Fila'
FROM @TBL_Fechas
WHERE DATEADD(DAY,2,TF_Fecha ) <= (SELECT MAX(TF_Fecha) FROM @TBL_Fechas)
) AS CTE
INNER JOIN @TBL_Fechas TF
ON TF.TF_Fecha = CTE.TF_Fecha
WHERE (SELECT SUM(TL.TN_Monto) FROM @TBL_Fechas TL WHERE TL.TF_Fecha BETWEEN CTE.TF_Fecha AND CTE.TF_FIN) >= 30
DECLARE @TBL_Fechas TABLE
(
TF_Fecha DATE
,TN_Monto MONEY
)
INSERT INTO @TBL_Fechas
SELECT
CAST('2014-10-12' AS DATE)
,10
INSERT INTO @TBL_Fechas
SELECT
CAST('2014-10-13' AS DATE)
,5
INSERT INTO @TBL_Fechas
SELECT
CAST('2014-10-14' AS DATE)
,20
INSERT INTO @TBL_Fechas
SELECT
CAST('2014-10-15' AS DATE)
,1
INSERT INTO @TBL_Fechas
SELECT
CAST('2014-10-16' AS DATE)
,70
SELECT
*
,(SELECT SUM(TL.TN_Monto) FROM @TBL_Fechas TL WHERE TL.TF_Fecha BETWEEN CTE.TF_Fecha AND CTE.TF_FIN)
FROM
(
SELECT
TF_Fecha
,DATEADD(DAY,2,TF_Fecha ) AS TF_FIN
,ROW_NUMBER() OVER (ORDER BY TF_Fecha) AS 'TN_Num_Fila'
FROM @TBL_Fechas
WHERE DATEADD(DAY,2,TF_Fecha ) <= (SELECT MAX(TF_Fecha) FROM @TBL_Fechas)
) AS CTE
INNER JOIN @TBL_Fechas TF
ON TF.TF_Fecha = CTE.TF_Fecha
WHERE (SELECT SUM(TL.TN_Monto) FROM @TBL_Fechas TL WHERE TL.TF_Fecha BETWEEN CTE.TF_Fecha AND CTE.TF_FIN) >= 30
No hay comentarios:
Publicar un comentario