0

I have students data for their fees paid for each program, now I want to show the outstanding fees, now since it's possible student could have their outstanding fees for 2018, 2019 and 2020 pending, hence it will have 3 rows (The months will be in columns). Now since the student is same, I will be clubbing the records in the front end, now if I consider pagination and I have 10 per page limit, and in these 10 records 3 records is of the same student (since year was different), in that case I will end up having just 7 records on given page.

Here's the sample data.

Studentname         RollNo    Year    Program     Jan  Feb  Mar  Apr  May  Jun ...
abc                 1         2018    p1          200   50   10   30   88   29
abc                 1         2019    p1          100   10   20   50   12   22
abc                 1         2020    p1          30    77   33   27   99   100
xyz                 2         2020    p2          88    29   32   99   199  200

How could I manage pagination for above case.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Abbas
  • 4,948
  • 31
  • 95
  • 161
  • "Clubbing data" sounds like an unkind, if not illegal, act. I'm guessing that you intend to combine the rows, e.g. by summing the values for each column. If you started with 10 rows and combined 3 years of data for a single user into one row, you'd have 8, not 7, rows to display. (For your sample data you would have 2 rows, not 1.) Correct? You could use a CTE or `view` to `group by Studentname`, then select pages of data. Performance may be an issue. It might be useful to page based on the first and last `Studentname` on a page if you are listing them in alphabetical order. – HABO Jul 21 '20 at 19:41
  • Yeah you're right 8 rows not 7, but i don't want to sum for each year, i want to mention how much for each month (of that year) is pending. I will be having a gridview with list in each row that would show [Month (year)] along with their respective amount. – Abbas Jul 21 '20 at 19:53
  • Okay, that makes sense and shouldn't bruise any data. You are effectively paging by displaying data for `PageSize` students per page. Approach it that way and `inner join` with the fee data to gather up all of the needed rows for the page. – HABO Jul 21 '20 at 19:58

1 Answers1

-1

Assuming your front end is HTML/CSS/Javascript:

You don't need to handle pagination in your query - or even your backend - at all. Everything can and should be done on your frontend. I would suggest using JQuery and Bootstrap to create a paginated table to display your data using Material Design for Bootstrap

stonkilla4
  • 67
  • 1
  • 1
  • 3
  • I am using SQL to return paginated data because i can eventually end up with millions of records and if i don't paginate before getting it on front end, then it will try to load all the records before JS kicks in and paginate. So my page load will badly impacted by this. – Abbas Jul 21 '20 at 19:23
  • Either way you would still be loading all of the records onto the page, if you want to load fewer records at a time you might want to order and limit the number of rows returned by the query (refer to [this question](https://stackoverflow.com/questions/16568/how-to-select-the-nth-row-in-a-sql-database-table)) – stonkilla4 Jul 21 '20 at 19:28