Sql Help

Transact-SQL
1

I have CreditDebit tables.
How can i write query for result ?

2

What is the characteristic that makes paperNo C1 to be the RelatedPaperNo for PaperNo D1? It does not appear to be the amount in the Debit Column or Credit Column. Is it the date? Or is it something else in another column that you have not shown?

3

Thank You For Answer.

I write query this way

SELECT
Debit.*,
GetCredit.PaperNo
FROM
(
SELECT
PaperNo
Debit,
PaperDate,
SUM(Debit) OVER (ORDER BY PaperDate) AS DebitBalance
FROM
CreditDebit
WHERE
Credit IS NULL
) AS Debit
OUTER apply
(
SELECT
TOP 1 PaperNo
FROM
(
SELECT
PaperNo,
Credit,
PaperDate,
SUM(Credit) OVER (ORDER BY PaperDate) AS CreditBalance
FROM
CreditDebit
WHERE
Debit IS NULL
) AS Credit
WHERE
Debit.DebitBalance <=Credit.CreditBalance
) AS GetCredit

Maybe Can you help me ?

I write query simple way.

4

Try this:

with debit
  as (select paperno
            ,sum(debit) over(order by paperdate,paperno) as debit_sum
        from creditdebittable
       where credit is null
     )
    ,credit
  as (select paperno
            ,sum(credit) over(order by paperdate,paperno) as credit_sum
        from creditdebittable
       where debit is null
     )
    ,cte
  as (select a.paperno
            ,a.debit
            ,a.credit
            ,a.paperdate
            ,c.paperno as relatedpaperno
            ,row_number() over(partition by a.paperno,a.debit,a.credit,a.paperdate order by c.credit_sum) as rn
        from creditdebittable as a
             left outer join debit as b
                          on b.paperno=a.paperno
             left outer join credit as c
                          on c.credit_sum>=b.debit_sum
     )
select paperno
      ,debit
      ,credit
      ,paperdate
      ,relatedpaperno
  from cte
 where rn=1
 order by paperdate
         ,paperno
;
1 Like
5

Thank You For Answer.
This query is working