How do I show balance log in a gridview

Question

I have a system in which allows the user to apply for taking leaves. The system also credits leaves to each user's account, twice a year.

Now, the administrator has full authority over this whole system and, can cancel the and grant leaves.

To analyze whether a user has not exceeded all privileges extended to him, it is required that the admin should be shown a record of the credit and debit of leaves taking place in each user account, like a log.

Something like this:

Now, I have made a table as below, to house this data:

My query now is that how do i achieve this? I mean, what should be my sql query and how should i display it? Should I create a Sql Server Agent job and display the data in a gridview? If yes, then how should I specify the steps for that.

My database has an employee table which stores the updated balance for each type of leave for every user. And the leave table houses the details of the requests made by the users. Basically, it stores all the details filled out in the leave application form.

I am confused as to how do I fetch the operations and changes occurring on these tables, for that is what is required to be displayed to the admin.

Answer 1

there is not much you need to do in sql for that. You can fetch the data that is to be displayed, but how you want it to display can be control on server side/Client side coding. So its better you fetch data and user the ItemDataBoud event to control the representational part of the application.

Answer 2

As per my understanding of your query, I have been shared my suggestion below :

1.My point of view ,you can get your expected output by using stored procedure in Database.

2.Because Hopefully You stored the values of employee information, leave type ,leave details are into separate table with the help of primary & foreign key constraints.

3.In the stored procedure, you can perform the following steps:

(1)Pass Input parameter : EmployeeId, FromDate, ToDate, HandledById (AdminId)
(2)Join all corresponding table with the help of Primary & foreign key Id.
(3)Select the appropriate column, Use aggregate function to find the leave.
(4)Use sub query in case of necessary as per your logic & table structure.

4.Then You can bind & display the data in gridview easily.

Do let me know if you need anything else.!