0

I have 3 tables as Student, Batches and StudentAttendanceRegistry. I managed to join these tables and get a desired result as following,

StudentID       Name        Year    Month        TotalPresentDays
PV/AM/B5/001    Asan        2023    August        12
PV/AM/B5/001    Asan        2023    September     2
PV/AM/B5/001    Asan        2023    October       NULL
PV/AM/B5/001    Asan        2023    November      NULL
PV/AM/B5/001    Asan        2023    December      NULL
PV/AM/B5/001    Asan        2024    January       NULL
PV/AM/B5/001    Asan        2024    February      NULL
PV/AM/B5/001    Asan        2024    March         NULL
PV/AM/B5/001    Asan        2024    April         NULL
PV/AM/B5/001    Asan        2024    May           NULL
PV/AM/B5/001    Asan        2024    June          NULL
PV/AM/B5/001    Asan        2024    July          NULL
PV/AM/B5/001    Asan        2024    August        NULL

Here is my stored procedure for the above result https://pastebin.com/ANn4kSdQ

You can see the Year and Month are not in order since these are dynamically generated using the BatchStartDate and BatchEndDate values from Batches table. I want to get these Year and Month rows into Columns and fill them with their respective TotalAbsentDays accordingly. And get a result like below,

StudentID     Aug 2023     Sep 2023    Oct 2023 ... Jan 2024    Feb 2024 ...
PV/AM/B5/001  12           2           NULL         NULL        NULL
PV/AM/B5/002  6            5           NULL         NULL        NULL
...

I pasted the above mentioned table's create queries here. https://pastebin.com/4Qymt22R

I'm very new to SQL altogether. Just today I discovered the existence of Pivot in SQL Server. I just want to create a report of student attendance in Crystal Reports. If you think I could do that without using pivot, any instructions or advice are much appreciated. Thank you...

1
  • Can you provide script with sampledata.
    – Peter
    Aug 10 at 11:49

1 Answer 1

0

I think I managed to get the result I wanted. I used python to extract the data from the stored procedure, pivot the tabled and exported it to a csv file.

from sqlalchemy import create_engine
import pandas as pd
import pyodbc
import os

server = 'serverName'
database = 'database'

conn_str = 'Driver={SQL Server Native Client 11.0};Server=' + server + ';Database=' + database + ';Trusted_Connection=yes;'

conn = pyodbc.connect(conn_str)

cursor = conn.cursor()
params = ('PV/AM/B5/2023', )  
cursor.execute("{CALL GetStudentAttendanceMonthlyReport (?)}", params)

rows = cursor.fetchall()
df = pd.DataFrame.from_records(rows, columns=[column[0] for column in cursor.description])

unique_months = df['Month'].unique()

pivot_df = pd.pivot_table(df, values='TOTAL', index='StudentID', columns='Month', aggfunc='first')

desired_column_order = sorted(pivot_df.columns, key=lambda col: list(unique_months).index(col))

pivot_df = pivot_df[desired_column_order]

order_df = pd.DataFrame(desired_column_order, columns=['columns'])

order_df['index'] = range(1, len(order_df) + 1)

csv_order_filename = 'column_order.csv'
order_df.to_csv(csv_order_filename, index=False)

csv_filename = 'attendance_report.csv'
pivot_df.to_csv(csv_filename, index=True) 

The thing is now I'm stuck at creating the report I want. That's another problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.