Left for contract quarter table (short form), the right to contract on a detailed (months), each Contract_ID for a contract number, value_date for contract signing date, every day of the contract (value_date) are the same,
Term will change, the value of the Term is to generate the table number of rows in the contract (the contract signing time/month)
Q: the first table is known, how to build an auxiliary table to get the second schedule of contract months, for help, every answer, grateful,

CodePudding user response:

The CREATE TABLE # T
(
A VARCHAR (10),
B DATE,
C INT,
D INT
)

INSERT INTO # T VALUES (' AAAA ', '2018-12-21', 900, 12)
INSERT INTO # T VALUES (' AAAA ', '2019-03-21', 900, 12)
INSERT INTO # T VALUES (' AAAA ', '2019-06-21', 900, 12)
INSERT INTO # T VALUES (' AAAA ', '2019-09-21', 900, 12)

INSERT INTO # T VALUES (' BBBB ', '2018-12-02', 900, 10)
INSERT INTO # T VALUES (' BBBB ', '2019-02-02', 900, 10)
INSERT INTO # T VALUES (' BBBB ', '2019-06-02', 900, 10)
INSERT INTO # T VALUES (' BBBB ', '2019-09-02', 900, 10)

SELECT A, B, (SELECT SUM (C) the FROM # T WHERE A=B.A AND B<=B.B) AS C, D FROM
(
SELECT A, DATEADD (MONTH, B.n umber - 1, B) AS B, D the FROM
(
SELECT A, MIN (B) AS B, MAX (D) AS D FROM # T GROUP BY A
) A CROSS JOIN master.. Spt_values B WHERE B.t ype='P' AND B.n umber BETWEEN 1 AND D
B)

DROP TABLE # T

CodePudding user response:

Too wow, really, a great god!! thank you thank you,