February 14, 2018 at 1:25 pm
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 |
February 14, 2018 at 6:53 pm
Hi,
What is the logical connection between DIN.LOGI and OUTE.LOGI ?
_____________
Code for TallyGenerator
February 14, 2018 at 11:01 pm
This was removed by the editor as SPAM
February 15, 2018 at 2:11 am
Hi
Thank you
DIN.Id = OUTE.Id
February 16, 2018 at 1:07 pm
Help Please 🙁🙁🙁
February 19, 2018 at 3:56 am
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
_____________
Code for TallyGenerator
February 19, 2018 at 4:25 am
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.
_____________
Code for TallyGenerator
February 19, 2018 at 11:48 am
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
February 23, 2018 at 10:08 am
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
February 24, 2018 at 1:39 pm
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
February 25, 2018 at 10:21 pm
Yes, my JOIN condition did not include te case when OUTE renge covers more than 1 DIN range.
The correct "range overlap" condition would be R2.End < R1.Begin AND R2.Begin < R1.End
And the CASE statement would need an extra condition to cover another possible combination.
This is how the query looks after the modification:
select D1.ID, D1.LOGI, D1.DATE_,
D1.AMOUNT,
CASE
WHEN O1.AllocatedAmount > D1.AllocatedAmount AND O1.PreviouslyAllocated < D1.PreviouslyAllocated THEN D1.Amount
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
-- 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.AllocatedAmount and O1.AllocatedAmount > D1.PreviouslyAllocated )
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
ORDER BY ID, DATE_, LOGI
_____________
Code for TallyGenerator
February 25, 2018 at 10:28 pm
And there is a couple of comments about your data.
1. Using FLOAT data type for Amount is quite questionable.
One of DECIMAL data types would be more appropriate.
FLOAT better to be used for rate kind of data.
2. Avoid inserting literal constants ('1', '106', etc.) into numeric fields.
It must be numeric values: 1, 106, etc.
If it's how it comes frm yor application then it's lacking data type control and a user can possibly enter a value which will cause a run-time error.
Raise the issue and see if the front end may be improved.
_____________
Code for TallyGenerator
February 25, 2018 at 11:17 pm
This was removed by the editor as SPAM
February 26, 2018 at 12:52 pm
Hi,
Thank you very much for your interest 🙂
I will check the queries and provide information
March 8, 2018 at 10:17 am
Hello there,
Thank you very much for your interest.
The code works correctly.
Is there an alternative way to a running balance?
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply