Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 сопоставить даты действия договора  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 598
SET NOCOUNT ON
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO


IF OBJECT_ID('[TestDoc].[A]') IS NOT NULL
  DROP TABLE [TestDoc].[A]


IF SCHEMA_ID('TestDoc') IS NULL
  EXEC('
    CREATE SCHEMA [TestDoc]
  ')
GO

IF OBJECT_ID('[TestDoc].[A]') IS NULL
  
CREATE TABLE [TestDoc].[A]
  (
    [Id]        Int           NOT NULL  IDENTITY(1,1),
    [contractID] int    not null,
    [DataFirst]     Date   NULL,
    [DateEnd]  Date       NULL,  
    [value]    float          NOT NULL,
   PRIMARY KEY CLUSTERED([Id])
  )
GO

INSERT INTO [TestDoc].[A]

SELECT 1,null,'2018-03-10',500
UNION
SELECT 1,null,'2018-03-16',600
UNION
SELECT 1,null, '2018-03-19',900
UNION
SELECT 1,'2018-03-09',null,200
UNION
SELECT 1,'2018-03-17',null,300
UNION
SELECT 1,'2018-03-23',null,400
UNION
SELECT 2, '2018-02-01',null,200
UNION
SELECT 2, '2018-02-05',null,350
UNION
SELECT 2,null,'2018-02-07',250


Нужно получить таблицу вида:


contractIDDateFirstDateEndvalue
12018-03-092018-03-10200
12018-03-102018-03-16500
12018-03-162018-03-17600
12018-03-172018-03-19300
12018-03-192018-03-23900
12018-03-23null400
22018-02-012018-02-05200
22018-02-052018-02-07350
22018-02-07null250


Версия SQL2008 R2
19 мар 18, 20:29    [21269543]     Ответить | Цитировать Сообщить модератору
 Re: сопоставить даты действия договора  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 598
и такого вида, т.е. либо действие смотреть по начальной дате, второй запрос по конечной дате


contractIDdateFirstdateEndvalue
12018-03-092018-03-10500
12018-03-102018-03-16600
12018-03-162018-03-17300
12018-03-172018-03-19900
12018-03-192018-03-23400
22018-02-012018-02-05350
22018-02-052018-02-07250
19 мар 18, 20:48    [21269565]     Ответить | Цитировать Сообщить модератору
 Re: сопоставить даты действия договора  [new]
xenix
Guest
получилось как-то громоздко, но вечером тяжело думается
WITH CTE(CONTRACT_ID,DateStart,DateEnd,Value) AS
(
  SELECT 1 ,null,'2018-03-10',500
UNION
SELECT 1,null,'2018-03-16',600
UNION
SELECT 1,null, '2018-03-19',900
UNION
SELECT 1,'2018-03-09',null,200
UNION
SELECT 1,'2018-03-17',null,300
UNION
SELECT 1,'2018-03-23',null,400
UNION
SELECT 2, '2018-02-01',null,200
UNION
SELECT 2, '2018-02-05',null,350
UNION
SELECT 2,null,'2018-02-07',250
),
C2 AS
(
  SELECT C.CONTRACT_ID,
  ISNULL(C.DATESTART,C.DateEnd)DATESTART,
  ISNULL(C.DATEEND,C.DateStart)DATEEND,
  C.VALUE
  FROM CTE C
),
C3 AS
(
SELECT X.CONTRACT_ID,X.DATESTART,X.DATEEND,X.VALUE,
ROW_NUMBER()OVER(PARTITION BY X.CONTRACT_ID ORDER BY X.DATESTART ASC)XCOL
FROM C2 X  
),
OPENED AS
(
  SELECT C.CONTRACT_ID,C.DateStart,C.VALUE,
  ROW_NUMBER()OVER(PARTITION BY C.CONTRACT_ID ORDER BY C.DATESTART DESC,C.DATEEND ASC)XCOL
  FROM C3 C
  
  
),
CLOSEDINTERVALS AS
(
SELECT C.CONTRACT_ID,X.DATESTART,C.DATEEND,X.Value
FROM C3 C
JOIN C3 X ON C.CONTRACT_ID=X.CONTRACT_ID AND C.XCOL=X.XCOL+1

),
PREV AS
(
SELECT CL.CONTRACT_ID,CL.DATESTART,CL.DATEEND,CL.VALUE
FROM CLOSEDINTERVALS CL

UNION ALL

SELECT OP.CONTRACT_ID,OP.DateStart,NULL DATEEND,OP.Value
FROM OPENED OP
WHERE OP.XCOL=1
)
SELECT P.CONTRACT_ID,P.DATESTART,P.DATEEND,P.VALUE
FROM PREV P
ORDER BY P.CONTRACT_ID ASC,P.DATESTART ASC
19 мар 18, 23:46    [21269901]     Ответить | Цитировать Сообщить модератору
 Re: сопоставить даты действия договора  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 598
не много не понял задачу. т.е. нужно получить таблицу вида. DateEnd - Это когда договор прекратил свое действие.
Ну если есть два значения, то приоритет отдаем первой дате т.е. DateFirst.
Мы должны брать значения согласно таблице, если договор на тот момент не действовал, то значение соответственно =0.
Например у нас есть строка, которая действовала до 10-03-2018 и равно она 200, и до 16-03-2018 и равна она 600, то в строке от 10 по 16, будет 600, т.к. 200 было до 10 числа , или строка есть как с 9 числа значение = 200, так и до 10.03. значение равно 500, берем значение равное 200


contractIDDateFirstDateEndvalue
12018-03-092018-03-10200
12018-03-102018-03-16600
12018-03-162018-03-17900
12018-03-172018-03-19300
12018-03-192018-03-23 0
12018-03-23null400
22018-02-012018-02-05200
22018-02-052018-02-07350
22018-02-07null0
20 мар 18, 16:10    [21272024]     Ответить | Цитировать Сообщить модератору
 Re: сопоставить даты действия договора  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 598
я думал обычное смещение, но не тут то было
SELECT a.contractID,a.d as DateFirst,a.r as DateEnd,a.value
FROM
(
select contractID
,coalesce(dataFirst,dateEnd) as d
,LEAD(coalesce(dataFirst,dateEnd)) OVER(PARTITION BY contractID ORDER BY coalesce(dataFirst,dateEnd)) as r
,value 
from TestDoc.A 
) as a

Order by contractID,d
20 мар 18, 16:22    [21272084]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить