Logical Inquiry and Calculation

  • 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.

    IDLOGIDATE_AMOUNTOUT_AMOUNTOUT_DATEOUT_LOGI
    11502018-01-05 00:00:00.000112018-01-05 00:00:00.00052
    11502018-01-05 00:00:00.000222018-01-06 00:00:00.00053
    11502018-01-05 00:00:00.000442018-01-07 00:00:00.00065
    11502018-01-05 00:00:00.000332018-01-08 00:00:00.00075
    11602018-01-05 00:00:00.000112018-01-09 00:00:00.00085
    11602018-01-05 00:00:00.00019NULLNULLNULL
    11702018-01-06 00:00:00.00030NULLNULLNULL
    21902018-01-05 00:00:00.000882018-01-05 00:00:00.00095
    21902018-01-05 00:00:00.000222018-01-06 00:00:00.00098
    22502018-01-06 00:00:00.000442018-01-06 00:00:00.00098
    22502018-01-06 00:00:00.000442018-01-07 00:00:00.00099
    22502018-01-06 00:00:00.000662018-01-08 00:00:00.000100
    22502018-01-06 00:00:00.00036NULLNULLNULL

  • Hi,

    What is the logical connection between DIN.LOGI and OUTE.LOGI ?

    _____________
    Code for TallyGenerator

  • This was removed by the editor as SPAM

  • Hi
    Thank you

    DIN.Id = OUTE.Id

  • Help Please 🙁🙁🙁

  • 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

  • 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

  • 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

  • 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

    IDLOGIDATE_AMOUNTOUT_AMOUNTOUT_DATEOUT_LOGI
    11502018-01-05 00:00:00.000222018-01-05 00:00:00.00052
    11602018-01-05 00:00:00.00040402018-01-05 00:00:00.00052
    11702018-01-06 00:00:00.00010102018-01-05 00:00:00.00052
    11802018-01-06 00:00:00.0005521302018-01-05 00:00:00.00052
    11802018-01-06 00:00:00.000552112018-01-06 00:00:00.00053
    11802018-01-06 00:00:00.000552411NULLNULL

    Thank you

  • 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

  • Hi,

    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

  • 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

  • This was removed by the editor as SPAM

  • Hi,

    Thank you very much for your interest 🙂

    I will check the queries and provide information

  • 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