Shrikant Shingade

My name is Shrikant Chandrasekhar Singade. I have a total of 18 plus years of experience in software development. Currently, I'm working with Citicorp Services India Private Limited for more than 10 years now. My primary role is software development. I'm working as a senior developer, where my primary skill is Microsoft SQL Server development, which includes writing stored procedures, user-defined functions, views, triggers, and performance tuning as well. I also have experience in data modeling, database development, and data analysis using Crystal Reports and SSRS. This is my primary skill set. In addition, in my previous organizations, I have worked on accurate reporting tools for report development. My primary skill set for this includes Microsoft SQL Server, writing stored procedures, functions, SQL programming, and performance tuning. I also worked on C# (.NET) and ASP.NET (around 15 years ago) in my first organization. This is all my background.

I have not worked on a health care software environment yet. I have worked on the financial domain, specifically in fund accounting team. Before that, I worked on a resource planning and project management application. I can give you an example from the current finance organization, which has a finance domain. There, we received a critical and complex requirement with very complex calculations and logic to be applied, on an urgent basis. What I designed was to communicate with the customer, receive all the required information, and propose a solution. We could create a new report template for this. We would use Microsoft SQL Server as the database and Crystal Report as the front end. The business logic involved almost 50 fields with a lot of calculations, aggregations, and everything. The input parameters were only five. Based on those five input values, we had to generate a report for more than 50 columns. I used SQL programming to write a stored procedure for this. Once it was approved, I received another request to enhance it, not just for one particular group but for multiple groups. The input parameters were changed to accommodate a list of groups. I modified the stored procedure by using temporary tables to store temporary data and perform calculations in the temporary table, then return a result set for all the groups required to show the output. We had to deliver this critical task in less than seven days, and we were able to give it on the 6th day in the UAT environment.

Data discrepancy in general report generated in SQL. I have come across a couple of situations where data was not generating correctly. The first situation is we have created a new report where we have to give data for daily NVs. The report will be generated on a daily basis. So over there, we have the UA team. They received feedback from the client or user that some particular data is not coming properly. Not coming properly means there are supposed to be records for 10 different funds, around 10 to 15 funds, but the report was showing only 80% of it, around 10 to 12 funds, the report was showing data. I observed what is the issue with this. I checked in the back end in SQL. This is actually missing. I found some of the master data fund setup was not having the proper values in it. I asked the user to update some of these values in the fund setup, and then the report got generated for all the required funds. This is the first thing. The other scenario was the report was running from many years, but suddenly I received a mail from the customer that for some of the funds. There are actually the report was showing data for around 50 funds, out of which they had added 2 new funds. So for those 2 new funds, data was not generating in the report. Again, I have checked why the data is not appearing. There was a very little typo mistake which had happened by the user. Those 2 funds were set up in the database. Those funds were missing one of the hyphen character, which was required while giving the fund name because we were using that fund name in the crystal report to get the child data of child fund. So in the naming convention, that hyphen was required in the fund setup level. I asked the user to update the fund name and put a hyphen after the name, and then those 2 funds got displayed in the report. This is how I saw the issue.

Web application functionality is breaking. So, from the SQL perspective, if any web application is breaking, that might be because of some errors occurring while processing any data at the SQL level. So, what I will do is check the error and check where at what point the application is breaking. First, I will identify that event. And then based on that event, I will check what actual code is written. And if this is related to the SQL thing, I will check which particular SQL is not running properly or whether it is giving any error. For example, there was a web form getting generated from a view. The thing is that there had been changes at the database level. We had updated one column name, and that name was not reflected in the underlying view. It was a proper mistake from the developers. And your testers also couldn't identify that particular thing. We updated the view underlying view with the correct updated column name, and then the application was running properly.

To implement a test plan to validate the accuracy of an ETL process, okay, so to prepare a test plan to get the accuracy of the retail process, first of all, I will go through a few of the steps. I will first check the requirements of what the ETL process should do. So based on the requirements, we can gather the sample data. And once we do that, we can generate sample data for which the ETL process should work, and then I will check what are the logic or transformations on the sample input data. So I will go step by step, checking the transformation, if there are any filtration required, and if any more calculations are required. I can do a unit testing on each transformation to check whether filtering is working properly, whether the calculations are being done properly for all fields. And once all the business logic and business rules are working correctly, then we will load the data. And once we load the data, I can check the loaded data against the required output. So if that matches, then we can say the ETL process is working correctly.

How would you use SQL to create a report that shows trends over time for a particular dataset? Okay. In SQL, when we want to show a trend, showing a trend over time means it could be a graphical thing which will be generated on data. I would use the time as one access and the value as the other access. What I will do is I will use aggregation, roll up totals during a time frame. As time goes, the amounts will be rolled up and will give the proper output. That means for the 1st year, the sales is 1 lakh INR. Then on the 2nd year, if it is again 1.5 lakh, then for the year 2, the roll up total would be 1 lakh plus 1.5 lakh, which comes to around 2.5 lakh. This is how I will roll up the values, and I will generate the output from SQL. Using window functions in SQL, I will use the sum of sales over time in ascending order. This is how we can get the required data to show the trend, how the sales is moving for each year.

Click login button. Check if user is redirected to the dashboard page. Verify successful login is very fast. Whenever user presses the login button, check that the username and password are not blank. Check the length of the password and ensure it meets the minimum requirement. Verify the password contains a combination of characters, such as uppercase and lowercase letters, numbers, and special characters. Check that the username is unique and not already in use when creating a new user. In the test case, the following checkpoints are missing: - Checking for blank username and password - Verifying password length and complexity - Ensuring unique usernames when creating new users

I'll select ID, first name, last name from users where active code is 1 and created date is greater than 1st January 2023 and updated date is less than 1st January 2022. And, again, this logic is misleading because when we say created is greater than 1st November 2023, then the updated date less than 1st January 2022 is a contradictory to the first. And since we are using our operation, it will return all the user details who are created after 1st January 2023 and who are either created or updated before 1st January 2022. So this logic is not correct.

Can you talk about a time when you had to ensure compliance with security principles by working with sensitive data? I haven't come across situations where I have to give any justification or any reason to the compliance regarding any security principles. We follow all the security principles by working with any kind of data.

Share an example of how you used ETL tools to announce data analysis and reporting in your previous projects. Okay, so for ETL, in my previous projects, in the current project, in my first organization, when I was in Anand ERP, we had an in-house developed tool that read input files. The flat files were either comma-separated files or delimited files with raw data that we needed to load into our database. So, there was a code written in C# that read the file, and once we read the file, we loaded those data into our database directly. This was in my first organization. Later on, I didn't have much time to work on the ETL part. But, in my current project, around two years ago, they had done a small ETL package that we are currently using for three different instant applications. These applications are the same, but the instances are different according to the region. So, there was one client whose data was coming to all three regions, and he wanted some aggregate, consolidated report from all three regions. So, we generated flat files from all three regions, and we inbounded those into a separate database. We created a separate database for it, which was a very small database for that particular client. So, we loaded the data received from three different regions to that single database using an SSIS package, and then we generated a report from that consolidated database. This is how we did the detailed thing.

I would use Excel's advanced features to organize and analyze large datasets effectively by leveraging its mathematical operations, sorting, calculated columns, and lookup functions. One way to organize the data is to sort it in ascending order based on date-related columns, so that the records loaded earlier appear first. We can also create new columns using arithmetic operations such as sum, multiplication, and other mathematical functions to calculate new fields. Additionally, we can use Excel's lookup functions, such as VLOOKUP or HLOOKUP, to filter and analyze the data. For instance, if we want to find specific data, we can use the VLOOKUP function to look up a value in a table and return a value from another column. This allows us to efficiently filter and analyze large datasets in Excel.