I had two tables data

Other SQL Server Topics
1

|||table 1|||

|id|voucher no|transid|Amount|date|
|143170|5680|1|5000|04-05-2023|
|146982|6204|1|3000|07-09-2023|
|146983|6204|1|3000|07-09-2023|
|147944|6335|1|3000|11-10-2023|
|147945|6335|1|3000|11-10-2023|
|||table2|||
|id|voucher no|transid|Amount|date|
|143625|5680|2|4961|04-05-2023|
|147426|6204|2|3000|07-09-2023|
|147427|6204|2|3000|07-09-2023|
|148395|6335|2|3000|11-10-2023|
|148396|6335|2|3000|11-10-2023|

|||output|||
|sno|v.num|pri|int|date|
|1|5680|5000|5680|04-05-2023|
|2|6204|3000|3000|07-09-2023|
|3|6204|3000|3000|07-09-2023|
|4|6335|3000|3000|11-10-2023|
|5|6335|3000|3000|11-10-2023|

i have two tables data.
i need output above like table....

2

hi

hope this helps

create data script

declare @Table1 table ( id int , voucher_no int , transid int , amount int , tdate date )
declare @Table2 table ( id int , voucher_no int , transid int , amount int , tdate date )

insert into @Table1 Values
(143170, 5680, 1, 5000, '04-05-2023' )
,(146982, 6204, 1, 3000, '07-09-2023' )
,(146983, 6204, 1, 3000, '07-09-2023' )
,(147944, 6335, 1, 3000, '11-10-2023' )
,(147945, 6335, 1, 3000, '11-10-2023' )

insert into @Table2 Values
( 143625, 5680, 2, 4961, '04-05-2023' )
,( 147426, 6204, 2, 3000, '07-09-2023' )
,( 147427, 6204, 2, 3000, '07-09-2023' )
,( 148395, 6335, 2, 3000, '11-10-2023' )
,( 148396, 6335, 2, 3000, '11-10-2023' )

select 
    row_number() over(order by a.rn ,a.tdate) as rn 
  , a.voucher_no
  , a.amount 
  , case when a.amount <> b.amount then a.voucher_no else a.amount end 
  , a.tdate 
from 
    (select rank() over(partition by tdate order by id ) as rn , * from @Table1 ) a 
        join
    (select rank() over(partition by tdate order by id ) as rn , * from @Table2 ) b 
         on 
		   a.rn = b.rn and a.tdate = b.tdate
order by 
       a.tdate

image
image814×765 16.1 KB

3

Tq very much bro..
its working... :smile:

4

i just change this code
case when a.amount <> b.amount then b.amount _no else a.amount end

now working correctly........

5

why becase same amount to there is no problem comes.
when a.amount and b.amount different...
the problem comes...
my mistake also there in the given output table....

any ways thank you very much...
the query working.....

6
6336 04 May 2022 300000.00 8.50 04 May 2024 2125.00 1 2125.00
6357 03 Apr 2023 200000.00 8.50 03 Apr 2024 1417.00 1 1417.00
6360 02 May 2023 100000.00 8.50 02 May 2024 708.00 1 708.00
6381 31 Dec 2023 500000.00 8.00 31 Dec 2024 3333.00 1 3333.00
6382 05 Jan 2024 400000.00 8.00 05 Jan 2025 2667.00 1 2667.00

how to sort this second column in the ascening order...
in sql.

columns name are
deposit no-depositdate-depositamt,roi,maturitydate,interestamt,noofmonths,totalinterestamt

6336 04 May 2022 300000.00 8.50 04 May 2024 2125.00 1 2125.00
6357 03 Apr 2023 200000.00 8.50 03 Apr 2024 1417.00 1 1417.00
6360 02 May 2023 100000.00 8.50 02 May 2024 708.00 1 708.00
6381 31 Dec 2023 500000.00 8.00 31 Dec 2024 3333.00 1 3333.00
6382 05 Jan 2024 400000.00 8.00 05 Jan 2025 2667.00 1 2667.00
7

my query output above like this..

8

change

order by id

to

order by id , amount

9
DepositNo DepositDate DepositAmount ROI MaturityDate InterestAmt NoOfMonths TotalInterest
6336 04-May-22 300000 8.5 04-May-24 2125 1 2125
6357 03-Apr-23 200000 8.5 03-Apr-24 1417 1 1417
6360 02-May-23 100000 8.5 02-May-24 708 1 708
6381 31-Dec-23 500000 8 31-Dec-24 3333 1 3333
6382 05-Jan-24 400000 8 05-Jan-25 2667 1 2667

iam unable to sort data by depositdate or maturity date..
can u help out above output data...

10

add that to the order by clause

11

it didnt work bro..

is this below query
select rank() over(partition by tdate order by id ) as rn , * from @Table1

helpful to given rank by depositdate....

12

hi bro
i had data....
RN,VoucherNo, CollectionDate, Principle, ReceivedInterest, Particulars,ROI

NULL 2022-04-26 NULL NULL NULL 500000.00 NULL NULL
3385 2022-05-10 4000.00 4275.00 Demand 496000.00 1 13.50
3566 2022-06-10 5000.00 5580.00 Demand 491000.00 2 13.50
3828 2022-07-08 5000.00 5524.00 Demand 486000.00 3 13.50
3910 2022-08-16 2754.00 5468.00 Demand 483246.00 4 13.50
4053 2022-09-09 7246.00 5437.00 Demand 476000.00 5 13.50
4169 2022-10-11 5000.00 5355.00 Demand 471000.00 6 13.50
4727 2022-11-08 5000.00 5299.00 Demand 466000.00 7 13.50
4944 2022-12-08 5000.00 5243.00 Demand 461000.00 8 13.50
5107 2023-01-04 5000.00 5186.00 Demand 456000.00 9 13.50
5346 2023-02-03 5000.00 5130.00 Demand 451000.00 10 13.50
5447 2023-03-03 5000.00 5074.00 Demand 446000.00 11 13.50
5538 2023-04-05 5000.00 5018.00 Demand 441000.00 12 13.50
5680 2023-05-04 5000.00 4961.00 Demand 436000.00 13 13.50
6204 2023-09-07 3000.00 3000.00 Demand 430000.00 15 13.50
6204 2023-09-07 3000.00 3000.00 Demand 433000.00 14 13.50
6335 2023-10-11 3000.00 3000.00 Demand 424000.00 17 12.00
6335 2023-10-11 3000.00 3000.00 Demand 427000.00 16 12.00
6456 2023-11-08 3000.00 3000.00 Demand 418000.00 19 12.00
6456 2023-11-08 3000.00 3000.00 Demand 421000.00 18 12.00
6584 2023-12-12 3000.00 3000.00 Demand 412000.00 21 12.00
6584 2023-12-12 3000.00 3000.00 Demand 415000.00 20 12.00
6745 2024-01-08 3000.00 3000.00 Demand 406000.00 23 12.00
6745 2024-01-08 3000.00 3000.00 Demand 409000.00 22 12.00
6843 2024-02-08 3000.00 3000.00 Demand 400000.00 25 12.00
6843 2024-02-08 3000.00 3000.00 Demand 403000.00 24 12.00
6969 2024-03-06 3000.00 3000.00 Demand 394000.00 27 12.00
6969 2024-03-06 3000.00 3000.00 Demand 397000.00 26 12.00
7110 2024-04-08 0.00 14714.00 Demand 388000.00 30 11.00
7110 2024-04-08 3000.00 3000.00 Demand 388000.00 29 11.00
7110 2024-04-08 3000.00 3000.00 Demand 391000.00 28 11.00
7210 2024-05-10 0.00 38618.00 Demand 382000.00 33 11.00
7210 2024-05-10 3000.00 3000.00 Demand 382000.00 32 11.00
7210 2024-05-10 3000.00 3000.00 Demand 385000.00 31 11.00
7389 2024-06-14 33401.00 3667.00 Demand 348599.00 34 11.00
7456 2024-07-04 36599.00 3360.00 Demand 312000.00 35 11.00

SELECT '2022-04-26', '500000, null FROM LN_Disbursement WHERE LoanNO = 'C-698'
SELECT NULL VocherNum, @DisbursalDate [Date], NULL Principle, NULL ReceivedInterest, NULL Particulars, @DisbursedAmount Outstanding
--, NULL [Days]
,NULL [RN],NULL ROI
UNION
SELECT VoucherNo, CollectionDate, Principle, ReceivedInterest, Particulars
, @DisbursedAmount - SUM(Principle) OVER(ORDER BY CollectionDate,RN ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
--, DATEDIFF(DAY, LAG(CollectionDate, 1, @DisbursalDate) OVER(ORDER BY CollectionDate, rn DESC), CollectionDate)
,RN ,ROI
FROM #TempData

when i run this above query the output is

VoucherNo, CollectionDate, Principle, ReceivedInterest, Particulars,ROI

NULL 2022-04-26 NULL NULL NULL 500000.00 NULL NULL
3385 2022-05-10 4000.00 4275.00 Demand 496000.00 1 13.50
3566 2022-06-10 5000.00 5580.00 Demand 491000.00 2 13.50
3828 2022-07-08 5000.00 5524.00 Demand 486000.00 3 13.50
3910 2022-08-16 2754.00 5468.00 Demand 483246.00 4 13.50
4053 2022-09-09 7246.00 5437.00 Demand 476000.00 5 13.50
4169 2022-10-11 5000.00 5355.00 Demand 471000.00 6 13.50
4727 2022-11-08 5000.00 5299.00 Demand 466000.00 7 13.50
4944 2022-12-08 5000.00 5243.00 Demand 461000.00 8 13.50
5107 2023-01-04 5000.00 5186.00 Demand 456000.00 9 13.50
5346 2023-02-03 5000.00 5130.00 Demand 451000.00 10 13.50
5447 2023-03-03 5000.00 5074.00 Demand 446000.00 11 13.50
5538 2023-04-05 5000.00 5018.00 Demand 441000.00 12 13.50
5680 2023-05-04 5000.00 4961.00 Demand 436000.00 13 13.50
** 6204 2023-09-07 3000.00 3000.00 Demand 430000.00 15
** 6204 2023-09-07 3000.00 3000.00 Demand 433000.00 14
** 6335 2023-10-11 3000.00 3000.00 Demand 424000.00 17
** 6335 2023-10-11 3000.00 3000.00 Demand 427000.00 16
** 6456 2023-11-08 3000.00 3000.00 Demand 418000.00 19
** 6456 2023-11-08 3000.00 3000.00 Demand 421000.00 18
** 6584 2023-12-12 3000.00 3000.00 Demand 412000.00 21
** 6584 2023-12-12 3000.00 3000.00 Demand 415000.00 20
** 6745 2024-01-08 3000.00 3000.00 Demand 406000.00 23
** 6745 2024-01-08 3000.00 3000.00 Demand 409000.00 22
** 6843 2024-02-08 3000.00 3000.00 Demand 400000.00 25
** 6843 2024-02-08 3000.00 3000.00 Demand 403000.00 24
** 6969 2024-03-06 3000.00 3000.00 Demand 394000.00 27
** 6969 2024-03-06 3000.00 3000.00 Demand 397000.00 26
** 7110 2024-04-08 0.00 14714.00 Demand 388000.00 30
** 7110 2024-04-08 3000.00 3000.00 Demand 388000.00 29
** 7110 2024-04-08 3000.00 3000.00 Demand 391000.00 28
7210 2024-05-10 0.00 38618.00 Demand 382000.00 33 11.00
7210 2024-05-10 3000.00 3000.00 Demand 382000.00 32 11.00
7210 2024-05-10 3000.00 3000.00 Demand 385000.00 31 11.00
7389 2024-06-14 33401.00 3667.00 Demand 348599.00 34 11.00
7456 2024-07-04 36599.00 3360.00 Demand 312000.00 35 11.00
13

WHEN COLLECTION DATE IS MORE THAN ONE RECORD...
I GOT THE THIS OUTPUT
||6204|2023-09-07|3000.00|3000.00|Demand|430000.00|15|
||6204|2023-09-07|3000.00|3000.00|Demand|433000.00|14|

||6204|2023-09-07|3000.00|3000.00|Demand|433000.00|14|
||6204|2023-09-07|3000.00|3000.00|Demand|430000.00|15|