SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Logical Inquiry and Calculation


Logical Inquiry and Calculation

Author
Message
meryemkurs072
meryemkurs072
Old Hand
Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)

Group: General Forum Members
Points: 395 Visits: 810
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 ALL
SELECT '1','160',CONVERT(DATETIME,'05.01.2018',104),'20' UNION ALL
SELECT '1','170',CONVERT(DATETIME,'06.01.2018',104),'30' UNION ALL
SELECT '2','190',CONVERT(DATETIME,'05.01.2018',104),'10' UNION ALL
SELECT '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 ALL
SELECT '1','53',CONVERT(DATETIME,'06.01.2018',104),'2' UNION ALL
SELECT '1','65',CONVERT(DATETIME,'07.01.2018',104),'4' UNION ALL
SELECT '1','75',CONVERT(DATETIME,'08.01.2018',104),'3' UNION ALL
SELECT '1','85',CONVERT(DATETIME,'09.01.2018',104),'1' UNION ALL
SELECT '2','95',CONVERT(DATETIME,'05.01.2018',104),'8' UNION ALL
SELECT '2','98',CONVERT(DATETIME,'06.01.2018',104),'6' UNION ALL
SELECT '2','99',CONVERT(DATETIME,'07.01.2018',104),'4' UNION ALL
SELECT '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 (21 views, 10.00 KB)
Sergiy
Sergiy
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98765 Visits: 14268
Hi,

What is the logical connection between DIN.LOGI and OUTE.LOGI ?
meryemkurs072
meryemkurs072
Old Hand
Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)

Group: General Forum Members
Points: 395 Visits: 810
Hi,
Thank you Smile

Connection between two tables DIN.ID = OUTE.ID

Transfer the data from the Oute table to the DIN table from the first date.
meryemkurs072
meryemkurs072
Old Hand
Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)

Group: General Forum Members
Points: 395 Visits: 810

Thank you

DIN.Id = OUTE.Id
meryemkurs072
meryemkurs072
Old Hand
Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)

Group: General Forum Members
Points: 395 Visits: 810
Help Please SadSadSad
Sergiy
Sergiy
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98765 Visits: 14268
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.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

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


Sergiy
Sergiy
SSC Guru
SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)SSC Guru (98K reputation)

Group: General Forum Members
Points: 98765 Visits: 14268
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_LOGI
FROM ( -- 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_LOGI
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)
)

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_LOGI
FROM (
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
meryemkurs072
Old Hand
Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)

Group: General Forum Members
Points: 395 Visits: 810
Hi,

Thank you so much.

You have made me very happy. SmileSmileSmileSmileSmile



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

Thank you
meryemkurs072
meryemkurs072
Old Hand
Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)

Group: General Forum Members
Points: 395 Visits: 810
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 ALL
SELECT '1','160',CONVERT(DATETIME,'05.01.2018',104),'40' UNION ALL
SELECT '1','170',CONVERT(DATETIME,'06.01.2018',104),'10' UNION ALL
SELECT '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 ALL
SELECT '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_LOGI
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)
)

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_LOGI
FROM (
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
meryemkurs072
Old Hand
Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)Old Hand (395 reputation)

Group: General Forum Members
Points: 395 Visits: 810
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search