How to Create Dynamic 'Report by Week' Procedure in SQL Server

Question

I am using MySQL . I want to make Stored Procedure for this report. The query:

select 
concat(us.firstname, ' ', us.lastname) as 'Salesperson',
concat(u.firstname, ' ', u.lastname) as 'Borrower Name',
nc.cid as '#Loan Non-Early',
nc.lsum as 'Amount Non-Early',
ec.cid as '#Loan Early',
ec.lsum as 'Amount Early',
(if(nc.cid is null, 0, nc.cid) + if(ec.cid is null, 0, ec.cid)) as '#Loan Total',
(if(nc.lsum is null, 0, nc.lsum) + if(ec.lsum is null, 0, ec.lsum)) as 'Total Amount'
from users u
join borrowers b on b.user_id = u.id
join contracts c on c.borrower_id = b.id
join loans l on l.borrower_id = b.id
join product_industry pi on pi.product_id = l.product_id
left join borrower_sales bs on bs.borrower_id = b.id
left join sales s on s.id = bs.sales_id
left join users us on us.id = s.user_id
left join (
  select l.borrower_id as bid, count(l.id) as cid, sum(l.amount) as lsum from loans l
    join product_industry pi on pi.product_id = l.product_id
    where pi.industry_id = 4
    and l.is_early = 0
    and date(l.complete_date) between '2022-02-14' and '2022-02-20'
    group by l.borrower_id
  ) nc on nc.bid = b.id
left join (
  select l.borrower_id as bid, count(l.id) as cid, sum(l.amount) lsum from loans l
    join product_industry pi on pi.product_id = l.product_id
    where pi.industry_id = 4
    and l.is_early = 1
    and date(l.complete_date) between '2022-02-14' and '2022-02-20'
    group by l.borrower_id
  ) ec on ec.bid = b.id
where pi.industry_id = 6
and c.status_id = 882
group by b.id
order by salesperson
;

I cannot figure out how to sort it by week. Can someone please help me what should i do?

I am trying to display my data like:

Salesperson Borrowername LoanNonEarly1 AmountNonEarly1 LoanEarly1 AmountEarly1 Loan Total1 Loan Total1  LoanNonEarly2 AmountNonEarly2 LoanEarly2 AmountEarly2 Loan Total2 Loan Total2 so on...
Calvin      Dave               1            100000         0            0         2            100000         0            0

Number 1,2 so on define the number of the week.

Answer 1

SQL Server has the handy DATEPART function.

DECLARE
  @dated  date = Convert( date, '20220218', 112 )
;
SELECT 
  Datepart( yy, @dated ) * 100
  + Datepart( isowk, @dated ) AS yearweek
;

I used the year in conjunction with the week as during year changes the week alone might not sort correctly.

In your query there are two dates, one for early and one for non-early payments. This also requires some business logic from you to handle the correct output. So one year-week field for early, then non-early and last a certain week according to business logic.