## Logical Inquiry and Calculation

Author
Message
meryemkurs072
Mr or Mrs. 500

Group: General Forum Members
Points: 504 Visits: 868
Hello there,

First of all I thank all the members for their sharing.

I was hanging in the question I mentioned below.
Thank you friends for help.

What I want to do is;

Distribute the data from the Oute table to the DIN table from the first date.

I am adding an Excel file for better understanding of the subject.

`SET NOCOUNT ON IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'DIN') AND OBJECTPROPERTY(ID, N'IsUserTable') = 1) DROP TABLE [dbo].[DIN] ;IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'OUTE') AND OBJECTPROPERTY(ID, N'IsUserTable') = 1) DROP TABLE [dbo].[OUTE];CREATE TABLE [dbo].[DIN] ( [ID] [int] NULL,[LOGI] [int] NULL,[DATE_] DATETIME NULL, [AMOUNT]  FLOAT NULL,[OUT_AMOUNT]  FLOAT NULL,[OUT_DATE] DATETIME NULL,[OUT_LOGI] [int] NULL);INSERT INTO DIN(ID,[LOGI],DATE_,AMOUNT)SELECT '1','150',CONVERT(DATETIME,'05.01.2018',104),'10' UNION ALLSELECT '1','160',CONVERT(DATETIME,'05.01.2018',104),'20' UNION ALLSELECT '1','170',CONVERT(DATETIME,'06.01.2018',104),'30' UNION ALLSELECT '2','190',CONVERT(DATETIME,'05.01.2018',104),'10' UNION ALLSELECT '2','250',CONVERT(DATETIME,'06.01.2018',104),'50' ;CREATE TABLE [dbo].[OUTE] ( [ID] [int] NULL,[LOGI] [int] NULL,[DATE_] DATETIME NULL, [AMOUNT]  FLOAT NULL );INSERT INTO OUTE(ID,LOGI,DATE_,AMOUNT)SELECT '1','52',CONVERT(DATETIME,'05.01.2018',104),'1' UNION ALLSELECT '1','53',CONVERT(DATETIME,'06.01.2018',104),'2' UNION ALLSELECT '1','65',CONVERT(DATETIME,'07.01.2018',104),'4' UNION ALLSELECT '1','75',CONVERT(DATETIME,'08.01.2018',104),'3' UNION ALLSELECT '1','85',CONVERT(DATETIME,'09.01.2018',104),'1' UNION ALL SELECT '2','95',CONVERT(DATETIME,'05.01.2018',104),'8' UNION ALLSELECT '2','98',CONVERT(DATETIME,'06.01.2018',104),'6' UNION ALLSELECT '2','99',CONVERT(DATETIME,'07.01.2018',104),'4' UNION ALLSELECT '2','100',CONVERT(DATETIME,'08.01.2018',104),'6' ;`

I want the final version of the table to be as follows.

 ID LOGI DATE_ AMOUNT OUT_AMOUNT OUT_DATE OUT_LOGI 1 150 2018-01-05 00:00:00.000 1 1 2018-01-05 00:00:00.000 52 1 150 2018-01-05 00:00:00.000 2 2 2018-01-06 00:00:00.000 53 1 150 2018-01-05 00:00:00.000 4 4 2018-01-07 00:00:00.000 65 1 150 2018-01-05 00:00:00.000 3 3 2018-01-08 00:00:00.000 75 1 160 2018-01-05 00:00:00.000 1 1 2018-01-09 00:00:00.000 85 1 160 2018-01-05 00:00:00.000 19 NULL NULL NULL 1 170 2018-01-06 00:00:00.000 30 NULL NULL NULL 2 190 2018-01-05 00:00:00.000 8 8 2018-01-05 00:00:00.000 95 2 190 2018-01-05 00:00:00.000 2 2 2018-01-06 00:00:00.000 98 2 250 2018-01-06 00:00:00.000 4 4 2018-01-06 00:00:00.000 98 2 250 2018-01-06 00:00:00.000 4 4 2018-01-07 00:00:00.000 99 2 250 2018-01-06 00:00:00.000 6 6 2018-01-08 00:00:00.000 100 2 250 2018-01-06 00:00:00.000 36 NULL NULL NULL

Attachments
F.xlsx (28 views, 10.00 KB)
Sergiy
SSC Guru

Group: General Forum Members
Points: 103832 Visits: 14433
Hi,

What is the logical connection between DIN.LOGI and OUTE.LOGI ?
meryemkurs072
Mr or Mrs. 500

Group: General Forum Members
Points: 504 Visits: 868
Hi,
Thank you

Connection between two tables DIN.ID = OUTE.ID

Transfer the data from the Oute table to the DIN table from the first date.
meryemkurs072
Mr or Mrs. 500

Group: General Forum Members
Points: 504 Visits: 868

Thank you

DIN.Id = OUTE.Id
meryemkurs072
Mr or Mrs. 500

Group: General Forum Members
Points: 504 Visits: 868
Sergiy
SSC Guru

Group: General Forum Members
Points: 103832 Visits: 14433
Nobody picked this up while I was away, so getting back to it.

First things first.
You need to change your tables design a bit.
The business logic of the records sequence must be reflected in constraints:
`CREATE TABLE [dbo].[DIN] ( [ID] [int] NOT NULL,[LOGI] [int] NOT NULL,[DATE_] DATETIME NOT NULL, [AMOUNT]  FLOAT NULL,[OUT_AMOUNT]  FLOAT NULL,[OUT_DATE] DATETIME NULL,[OUT_LOGI] [int] NULL,primary key clustered (ID, Date_, LOGI))CREATE TABLE [dbo].[OUTE] ( [ID] [int] NOT NULL,[LOGI] [int] NOT NULL,[DATE_] DATETIME NOT NULL, [AMOUNT]  FLOAT NULL ,PRIMARY KEY CLUSTERED (ID, Date_, LOGI))`

Please make sure that the oder of columns in the index corectly reflects the business logic.

Then create queries for running totals on both tables.
There are many ways to do this, here is one:

`select D.*, A.AllocatedAmount - D.AMOUNT PreviouslyAllocated, A.AllocatedAmountfrom DIN DCROSS APPLY (    Select SUM(D2.Amount) AllocatedAmount FROM DIN D2    where D2.ID = D.ID and D2.DATE_ <= D.Date_ and D2.LOGI <=D.LOGI    ) A select O.*, A.AllocatedAmount - O.AMOUNT PreviouslyAllocated, A.AllocatedAmountfrom OUTE OCROSS APPLY (    Select SUM(O2.Amount) AllocatedAmount FROM OUTE O2    where O2.ID = O.ID and O2.DATE_ <= O.Date_ and O2.LOGI <=O.LOGI    ) A `

Sergiy
SSC Guru

Group: General Forum Members
Points: 103832 Visits: 14433
Then we can join these aggregations by the overlapping amount ranges within the same ID's:

`select * FROM (    select D.*,         A.AllocatedAmount - D.AMOUNT PreviouslyAllocated, A.AllocatedAmount    from DIN D    CROSS APPLY (        Select SUM(D2.Amount) AllocatedAmount FROM DIN D2        where D2.ID = D.ID and D2.DATE_ <= D.Date_ and D2.LOGI <=D.LOGI        ) A     ) D1    INNER JOIN (        select O.*, A.AllocatedAmount - O.AMOUNT PreviouslyAllocated, A.AllocatedAmount        from OUTE O        CROSS APPLY (            Select SUM(O2.Amount) AllocatedAmount FROM OUTE O2            where O2.ID = O.ID and O2.DATE_ <= O.Date_ and O2.LOGI <=O.LOGI            ) A     ) O1 ON O1.ID = D1.ID and         (            (O1.PreviouslyAllocated >= D1.PreviouslyAllocated and O1.PreviouslyAllocated < D1.AllocatedAmount)        OR             (O1.AllocatedAmount > D1.PreviouslyAllocated and O1.AllocatedAmount < D1.AllocatedAmount)        )`

Next - we need to calculate OUTE amount allocated to each DIN record:

`SELECT  ...,         CASE               WHEN O1.AllocatedAmount > D1.AllocatedAmount THEN D1.AllocatedAmount - O1.PreviouslyAllocated               WHEN O1.PreviouslyAllocated < D1.PreviouslyAllocated THEN O1.AllocatedAmount - D1.PreviouslyAllocated               ELSE O1.AMOUNT          END  OUT_AMOUNT, O1.Date_ OUT_DATE, O1.LOGI OUT_LOGI`

Finally, we need to add records where DIN amounts are not covered with OUTE ones:

`UNION ALL SELECT OTA.ID, D1.LOGI, D1.DATE_,     D1.AMOUNT,     CASE         --  If TotalAmountPerID is withing the curent range - take only the remainder        WHEN D1.PreviouslyAllocated <= OTA.TotalAmountPerID THEN D1.AllocatedAmount - OTA.TotalAmountPerID         --  Otherwise - display the full DIN.Amount        ELSE D1.AMOUNT END OUT_AMOUNT, NULL OUT_DATE, NULL OUT_LOGIFROM (    -- Total amounts per ID        Select ID, SUM(Amount) TotalAmountPerID         FROM OUTE         GROUP BY ID        ) OTA     INNER JOIN     (        select D.*, A.AllocatedAmount - D.AMOUNT PreviouslyAllocated, A.AllocatedAmount        from DIN D        CROSS APPLY (            Select SUM(D2.Amount) AllocatedAmount FROM DIN D2            where D2.ID = D.ID and D2.DATE_ <= D.Date_ and D2.LOGI <=D.LOGI            ) A         ) D1 ON OTA.ID = D1.ID and --                 D1.AllocatedAmount > OTA.TotalAmountPerID`

So the final query would look like this:

`select D1.ID, D1.LOGI, D1.DATE_,     D1.AMOUNT,     CASE         WHEN O1.AllocatedAmount > D1.AllocatedAmount THEN D1.AllocatedAmount - O1.PreviouslyAllocated         WHEN O1.PreviouslyAllocated < D1.PreviouslyAllocated THEN O1.AllocatedAmount - D1.PreviouslyAllocated         ELSE O1.AMOUNT END  OUT_AMOUNT, O1.Date_ OUT_DATE, O1.LOGI OUT_LOGIFROM (    select D.*,         A.AllocatedAmount - D.AMOUNT PreviouslyAllocated, A.AllocatedAmount    from DIN D    CROSS APPLY (        Select SUM(D2.Amount) AllocatedAmount FROM DIN D2        where D2.ID = D.ID and D2.DATE_ <= D.Date_ and D2.LOGI <=D.LOGI        ) A     ) D1    INNER JOIN (        select O.*, A.AllocatedAmount - O.AMOUNT PreviouslyAllocated, A.AllocatedAmount        from OUTE O        CROSS APPLY (            Select SUM(O2.Amount) AllocatedAmount FROM OUTE O2            where O2.ID = O.ID and O2.DATE_ <= O.Date_ and O2.LOGI <=O.LOGI            ) A     ) O1 ON O1.ID = D1.ID and         (            (O1.PreviouslyAllocated >= D1.PreviouslyAllocated and O1.PreviouslyAllocated < D1.AllocatedAmount)        OR             (O1.AllocatedAmount > D1.PreviouslyAllocated and O1.AllocatedAmount < D1.AllocatedAmount)        )UNION ALL SELECT OTA.ID, D1.LOGI, D1.DATE_,     D1.AMOUNT,     CASE WHEN PreviouslyAllocated <= TotalAmountPerID THEN D1.AllocatedAmount - OTA.TotalAmountPerID         ELSE D1.AMOUNT END OUT_AMOUNT, NULL OUT_DATE, NULL OUT_LOGIFROM (        Select ID, SUM(Amount) TotalAmountPerID         FROM OUTE         GROUP BY ID        ) OTA     INNER JOIN     (        select D.*, A.AllocatedAmount - D.AMOUNT PreviouslyAllocated, A.AllocatedAmount        from DIN D        CROSS APPLY (            Select SUM(D2.Amount) AllocatedAmount FROM DIN D2            where D2.ID = D.ID and D2.DATE_ <= D.Date_ and D2.LOGI <=D.LOGI            ) A         ) D1 ON OTA.ID = D1.ID and                 D1.AllocatedAmount > OTA.TotalAmountPerID--I added this only to make it easier to review the result. Not needed for the PROD code.ORDER BY ID, DATE_, LOGI`

Don't forget to replace * with explicit list of the columns in PROD version of the query.
meryemkurs072
Mr or Mrs. 500

Group: General Forum Members
Points: 504 Visits: 868
Hi,

Thank you so much.

You have made me very happy.

If you can not do it with Cursor, your method is good

Thank you
meryemkurs072
Mr or Mrs. 500

Group: General Forum Members
Points: 504 Visits: 868
Hello

Do not blame me for disturbing you.
It does not give me the fact that the table structure changes a little.

`SET NOCOUNT ON IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'DIN') AND OBJECTPROPERTY(ID, N'IsUserTable') = 1) DROP TABLE [dbo].[DIN] ;IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'OUTE') AND OBJECTPROPERTY(ID, N'IsUserTable') = 1) DROP TABLE [dbo].[OUTE];CREATE TABLE [dbo].[DIN] ( [ID] [int] NOT NULL,[LOGI] [int] NOT NULL,[DATE_] DATETIME NOT NULL, [AMOUNT]  FLOAT NULL,[OUT_AMOUNT]  FLOAT NULL,[OUT_DATE] DATETIME NULL,[OUT_LOGI] [int] NULL,primary key clustered (ID, Date_, LOGI));INSERT INTO DIN(ID,[LOGI],DATE_,AMOUNT)SELECT '1','150',CONVERT(DATETIME,'05.01.2018',104),'2' UNION ALLSELECT '1','160',CONVERT(DATETIME,'05.01.2018',104),'40' UNION ALLSELECT '1','170',CONVERT(DATETIME,'06.01.2018',104),'10' UNION ALLSELECT '1','180',CONVERT(DATETIME,'06.01.2018',104),'552' ;CREATE TABLE [dbo].[OUTE] ( [ID] [int] NOT NULL,[LOGI] [int] NOT NULL,[DATE_] DATETIME NOT NULL, [AMOUNT]  FLOAT NULL ,PRIMARY KEY CLUSTERED (ID, Date_, LOGI));INSERT INTO OUTE(ID,LOGI,DATE_,AMOUNT)SELECT '1','52',CONVERT(DATETIME,'05.01.2018',104),'182' UNION ALLSELECT '1','53',CONVERT(DATETIME,'06.01.2018',104),'11' ;`

Query

`select D1.ID, D1.LOGI, D1.DATE_,   D1.AMOUNT,   CASE     WHEN O1.AllocatedAmount > D1.AllocatedAmount THEN D1.AllocatedAmount - O1.PreviouslyAllocated     WHEN O1.PreviouslyAllocated < D1.PreviouslyAllocated THEN O1.AllocatedAmount - D1.PreviouslyAllocated     ELSE O1.AMOUNT END  OUT_AMOUNT, O1.Date_ OUT_DATE, O1.LOGI OUT_LOGIFROM (  select D.*,     A.AllocatedAmount - D.AMOUNT PreviouslyAllocated, A.AllocatedAmount  from DIN D  CROSS APPLY (    Select SUM(D2.Amount) AllocatedAmount FROM DIN D2    where D2.ID = D.ID and D2.DATE_ <= D.Date_ and D2.LOGI <=D.LOGI    ) A   ) D1  INNER JOIN (    select O.*, A.AllocatedAmount - O.AMOUNT PreviouslyAllocated, A.AllocatedAmount    from OUTE O    CROSS APPLY (    Select SUM(O2.Amount) AllocatedAmount FROM OUTE O2    where O2.ID = O.ID and O2.DATE_ <= O.Date_ and O2.LOGI <=O.LOGI    ) A   ) O1 ON O1.ID = D1.ID and     (    (O1.PreviouslyAllocated >= D1.PreviouslyAllocated and O1.PreviouslyAllocated < D1.AllocatedAmount)    OR     (O1.AllocatedAmount > D1.PreviouslyAllocated and O1.AllocatedAmount < D1.AllocatedAmount)    )UNION ALL SELECT OTA.ID, D1.LOGI, D1.DATE_,   D1.AMOUNT,   CASE WHEN PreviouslyAllocated <= TotalAmountPerID THEN D1.AllocatedAmount - OTA.TotalAmountPerID     ELSE D1.AMOUNT END OUT_AMOUNT, NULL OUT_DATE, NULL OUT_LOGIFROM (    Select ID, SUM(Amount) TotalAmountPerID     FROM OUTE     GROUP BY ID    ) OTA   INNER JOIN   (    select D.*, A.AllocatedAmount - D.AMOUNT PreviouslyAllocated, A.AllocatedAmount    from DIN D    CROSS APPLY (    Select SUM(D2.Amount) AllocatedAmount FROM DIN D2    where D2.ID = D.ID and D2.DATE_ <= D.Date_ and D2.LOGI <=D.LOGI    ) A     ) D1 ON OTA.ID = D1.ID and       D1.AllocatedAmount > OTA.TotalAmountPerID--I added this only to make it easier to review the result. Not needed for the PROD code.ORDER BY ID, DATE_, LOGI`

The outcome of the question

ID LOGI DATE_ AMOUNT OUT_AMOUNT OUT_DATE OUT_LOGI
1 150 2018-01-05 00:00:00.000 2 2 2018-01-05 00:00:00.000 52
1 180 2018-01-06 00:00:00.000 552 130 2018-01-05 00:00:00.000 52
1 180 2018-01-06 00:00:00.000 552 11 2018-01-06 00:00:00.000 53
1 180 2018-01-06 00:00:00.000 552 411 NULL NULL

I want to be

 ID LOGI DATE_ AMOUNT OUT_AMOUNT OUT_DATE OUT_LOGI 1 150 2018-01-05 00:00:00.000 2 2 2018-01-05 00:00:00.000 52 1 160 2018-01-05 00:00:00.000 40 40 2018-01-05 00:00:00.000 52 1 170 2018-01-06 00:00:00.000 10 10 2018-01-05 00:00:00.000 52 1 180 2018-01-06 00:00:00.000 552 130 2018-01-05 00:00:00.000 52 1 180 2018-01-06 00:00:00.000 552 11 2018-01-06 00:00:00.000 53 1 180 2018-01-06 00:00:00.000 552 411 NULL NULL

Thank you

meryemkurs072
Mr or Mrs. 500

Group: General Forum Members
Points: 504 Visits: 868
My purpose here is to make a current account debts closing transaction. How much time difference is there from this question.

I am currently collecting collections on more than one date and I would like to close the collections from the first bill date.

I am happy if you have a similar query in your hands if you have one.

Help