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