Working with SQL
The course is part of this learning path
This is an intermediate level course suited to DBAs, system analysts, programmers, or anyone wanting to learn how to use SQL to solve business scenarios. It will help to have an understanding of Structured Query Language syntax and functions and regular expressions, or some experience with script-based programming languages. Our learning objectives for this course are to recognize and explain use cases where Structured Query Language, SQL, can be applied in business situations, explain and apply SQL procedures to import and parse text files, define and apply automation processes to parse and sort data to specific databases. We welcome all comments and feedback. So please feel free to reach out to us at email@example.com with any questions or comments.
- [Instructor] Now, Creating the Schedule Records. This is the meat of the job where we use our imported data to update the schedule records. A number of processes have to happen here. We need to identify the record by an ATM ID, the period stamp date and the week number. And then update the appropriate day amount fields based on the delivery date. Then we need to allocate the denomination amounts to the correct canisters for the ATM. For example, an ATM could be set up as having one 50 dollar canister and three 20 dollar canisters. Obviously, each canister has a capacity, and in the case of 20 dollar canister, it's $40,000. Another requirement is that denominations should be spread across available canisters. While $80,000 of 20s could go into two canisters, if the ATM has three, we need to spread it across all three just to add one more level of complication. Notes come in bundles call bricks, that's 1,000 notes in flats, which has 100 notes. Where possible, we don't want to split bricks. So in the case of 80,000 in 20s, split across three canisters, we wanted to be split is 40,000, 20,000 and 20,000. So this is an involved calculation that is going to be repeated many times for different denominations, so we would want to put it in a function. We are going to update a copy of the schedule table first, then update the production table. I'm updating a copy, as it is the most complex and time consuming part of the process, and I want to wrap it in a transaction. It is not advisable to lock production tables and transactions in a multi-user environment, but working on a staging version we can make sure that this part of the process validates and completes before doing a relatively quick update into production. I'm going to call the procedure that does the main work, usp_ATM_Schedule-Load. This will take a file name as it's one argument. I will need to clear a bunch of variables that'll be used during operation. This operation needs to convene successfully in its entirety so I will wrap it and I begin, try, and I begin transaction block. I will stop by getting the data set of older information from schedule, and report on joining it with the ATM reference table to get the ATM configurations.
- See here, I'm just joining with ATMs, where they're stages Then I will create a cursor to loop through this data, data set of denomination amounts and configurations. Firstly, we will need to find the load start period and the week number. To do this, I've created a function called udf_Load_Schedule_Week. Working out the week number is a tedious repetitive task. So let's put that in a function that we can use it over and over again. A scalar function is similar to functions you have in other programming languages. It returns a single value. Return type is to clear it and it returns segment of the function declaration. You want to return an integer value. The function will take the next minimum load start period, lists then the delivery date, and the delivery date is parameters and returns the correct week number. We used the date and function of this QL server to add weeks to the load/start period date. Each clause of the case statement corresponds to one week of the load/start period. Remember, we reached a clause with a number of weeks and into the load/start period date that exceeds the delivery date. Then we have found the week that our delivery date occurs, and this is a good example of how the order of your case statement does make a difference. To find the schedule record we will use the ATM identified delivery date and week number. Couldn't see the other Load Week Schedule function, then search the Load Schedule Table. Once we have our Load Schedule Record we can go through and zero out the cassette on the outfields with Dynamic HQL. We do this as a first precaution just in case this process has run already and the data has been incorrect. We always want to begin the process with a clean slate. We construct the appropriate field names using the DATENAME function, along with substring to get the first three characters of the DATENAME. Then it's the field names, this updates, then it will change depending on the delivery date, you need to use Dynamic HQL to construct the update statement. We do not want to overwrite days that we are not interested in.
About the Author
Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.