Leverage it correctly and it will deliver results immediately.

Generative AI

Data is power. Raise the value of your data and reveal greater potential.  

Gain deeper insights resulting in optimized business operations, attain competitive advantages and anticipate solutions to emerging market opportunities.  

Design and build a scalable data platform that has efficient data pipelines that will deliver data across the business in an efficient and cost-effective manner to enable true business outcomes. From collected internal and external data, we can help design and build custom AI & machine learning frameworks and models that help you generate useful insights to deliver you company real-world benefits. 

Let us be your go-to partner. We are happy to engage in a single, multiple or all project phases. 

Laying down the groundwork by establishing a shared vision through strategy and solution architecture. Test hypotheses with very tangible POC/POV/Pilots. 

Strategy & Discovery 

Making sure to capture your pain-points, needs & wishes and translating them into requirements. 

Business Analysis 

Transforming your vision and goals into a measurable and concrete process. 

Process Design 

Designing, building and managing machine learning frameworks that allow businesses to innovate faster. 

Machine Learning Development 

Making sure to deploy and maintain ML systems in production reliably and efficiently. 

ML Ops 

Testing is an integral part of the SW development process. We believe in continuous testing across all project phases. 

Testing & QA 

Going live to production can be an incredibly stressful and challenging period. Luckily, we are always here to support our clients. 

Post Go-Live Support 

Application management and optimization services that focus on automation and efficiency of processes to reduce total cost of ownership. 

Managed Services 

Drive Your Business Forward!

Sales

iOLAP Experts are Here to Assist You.

experts

Sales CTA

medium_dickeys.jpg

small_dickeys.jpg

thumbnail_dickeys.jpg

dickeys.jpg

## Customer  

Dickey’s Barbecue Pit operates more than 530 locations in 43 states. They still slow smoke all meats on-site just the same way they did in 1941.  


## Challenge  

Like all restaurants, Dickey’s needed better insight into multiple information sources: sales from multiple channels, inventory and food cost, labor and scheduling, and multiple internal operational systems.  With an ever-growing franchise, Dickey’s was also pushing new programs to the store:  Wi-Fi hotspots, loyalty programs, online ordering, customer sentiment systems, and email marketing solutions.  With this treasure trove of new data assets and the need to provide advanced analytics to both corporate and franchise users, Dickey’s needed an innovative solution to scale with their growth plans.  


## Solution  

iOLAP architected a secure and scalable solution on the AWS platform.  Store sales and labor data is pushed to S3 every 5 minutes and processed into Redshift for near real time analytics using Lambda.  End of day sales and labor, inventory, and supply chain sources are integrated nightly using Syncsort DMX running on EC2.  Loyalty transactions are delivered to SQS and processed in micro batches throughout the day.  Using API’s, we pull online ordering and email marketing data on demand.  Yellowfin’s BI platform runs on EC2 and provides a clean and consistent data visualization layer for more than 600 end users via web browser or mobile device.  


## Benefits  

- Dickey’s has a best of breed technology stack at a fraction of the cost of traditional approaches
- They’re able to complete analysis and build applications for customer segmentation, menu optimization, media spend optimization, and site selection
- The framework allows for rapid innovation and ingestion of new information assets.  
- 30% savings in compute costs
- TCO reduced by 54 percent. AWS enabled Dickey’s to scale at a fraction of its on-premises cost
  
![center-small](https://iolap-com-strapi-cms-upload.s3.amazonaws.com/dickeys1_0c6ce0a9de.png)



scan_iolap_footer_logo.png

Frisco, Texas

scan_geopoint_footer.png

Austin, Texas

Rijeka, Croatia

Default Footer

iolap-logomark-hob-reversed@2x.png

Homepage top menu

TCO reduced by 54 percent. AWS enables Dickey’s to scale at a fraction of its on-premises cost.

Default Heading Title

large_casestudiesDarkBlue.jpg

medium_casestudiesDarkBlue.jpg

small_casestudiesDarkBlue.jpg

thumbnail_casestudiesDarkBlue.jpg

casestudiesDarkBlue.jpg

TCO reduced by 54 percent. AWS enables Dickey’s to scale at a fraction of its
on-premises cost.

Dickey's Barbecue Pit

medium_whataburger.jpg

small_whataburger.jpg

thumbnail_whataburger.jpg

whataburger.jpg

## Client Description
From two original employees in Corpus Christi to over 36,000 across 10 states, Whataburger is more successful than ever. In 2015, the company achieved $2 billion in sales. Whataburger has been recognized as the top performer in Nation’s Restaurant News’ Social 200 which spotlights companies with the highest social engagement. 

## Challenge
Whataburger had a need to detect cashier fraud from its POS transactions across all its 700+ store locations. It was a manual process and a huge effort to identify suspect cashier transactions and interview the cashiers/supervisors for such locations on regular basis.  
Additionally, Whataburger was experiencing long ETL run times and reporting SLA’s continually ran past target time on the previous database platform.  Whataburger purchased two Netezza Makos to help speed up overall data warehouse usage but did not have any in-house Netezza expertise.

## Solution
iOLAP helped Whataburger architect, build & develop solution for detecting fraud for POS transactions in near real-time basis using machine learning algorithms and models. The solution was developed in Hadoop (IBM BigInsights) platform involving Spark, BigSQL, Hive, HDFS, Netezza & MicroStrategy. Transactions were loaded in micro batches and data was scored to identify potential frauds. Alerts were sent to users whenever such potential fraud transactions were identified.

## Benefits
Whataburger’s loads, which previously ran all-day, now complete in a few hours and all reporting SLA’s are met.  Leveraging the new Netezza and Hadoop platform, Whataburger is able to identify potential fraud transactions across all its locations in near real-time basis through automation.
  
![center-small](https://iolap-com-strapi-cms-upload.s3.amazonaws.com/Whataburger_Logo_wine_e3857b5690.png)



Leveraging the new Netezza and Hadoop platform enables to identify potential fraud transactions across all locations in near real-time basis through automation.

Whataburger

medium_luciaHG.jpg

small_luciaHG.jpg

thumbnail_luciaHG.jpg

luciaHG.jpg

## Client Description
Lucia Health Guidelines is the first clinical decision support tool that leverages machine learning and the power of cloud computing to help maintain clinical guidelines at the point of care. Lucia’s mission is to create a future with better care, better outcomes, and better economics for our healthcare system.

## Challenge
American Heart Association and the American College of Cardiology have made clear-cut guidelines based on scientific evidence and outcomes data for the treatment of atrial fibrillation (‘AF’, ‘Afib’) and prevention of stroke.  Yet, Xian MD and colleagues at Duke University (March 2017) reported that most patients with atrial fibrillation are not being treated as per the guidelines in the community setting.  In fact, they reviewed the charts of ~94,000 strokes victims with atrial fibrillations and 84% (~80,000 patients) did not receive the recommended anticoagulation as per the guidelines prior to their strokes.  Xian believes that an estimated 58,000 to 88,000 strokes might be preventable per year if the Afib treatment guidelines are followed.   As a country we spend about $6Billion a year on Afib/prevention of stroke and ~$83Billion a year on the long-term care of stroke victims.

Here is our opportunity to save lives, prevents strokes, and save tens of billions healthcare dollars by helping clinicians maintain these Afib guidelines.

## Solution
iOLAP designed and implemented a well-architected, HIPAA-compliant, solution that captures and analyzes EKG images using custom computer vision code. These EKG results are compared with patient data to determine a personalized risk assessment.

## Benefits
Lucia can deliver a scalable, fully-automated solution which brings the equivalent expertise of highly skilled clinicians trained in AF to any point of care. This will allow patients to be discharged with optimal therapy and referral to a specialist for continued care.
  
![center-small](https://iolap-com-strapi-cms-upload.s3.amazonaws.com/1597676328115_1d7141685f.jpeg)



Solution creation that captures and analyzes EKG images using custom computer vision code. These EKG results are compared with patient data to determine a personalized risk assessment.

Lucia Health Guidelines

scan_linkedin_footer_logo.png

scan_facebook_footer_logo.png

twitter_footer_logo.png

js.png

Design and build a scalable data platform that has efficient data pipelines that will deliver data across the business in an efficient and cost-effective manner to enable true business outcomes. From collected internal and external data, we can help design and build custom AI & Machine Learning frameworks and models that help you generate useful insights to deliver your company real-world benefits.   
  


Let us be your go-to partner. We are happy to engage in a single, multiple or all project phases. 

AI & Machine Learning 

large_AIDarkBlue.jpg

medium_AIDarkBlue.jpg

small_AIDarkBlue.jpg

thumbnail_AIDarkBlue.jpg

AIDarkBlue.jpg

large_Scientists-studying-neural-connections.jpg

medium_Scientists-studying-neural-connections.jpg

small_Scientists-studying-neural-connections.jpg

thumbnail_Scientists-studying-neural-connections.jpg

Scientists-studying-neural-connections.jpg

AI & Machine Learning

Amazon Web Services released SageMaker Studio at re:Invent 2019. As a fully integrated development environment for machine learning, SageMaker Studio allows storage and collection of all the development facets users need in one place. It promises great assets for established ML practitioners, as well as for those less experienced in the domain. The main purpose of the Studio should be speeding up and simplifying the whole ML process workflow.
  
![1.jpg](https://iolap-com-strapi-cms-upload.s3.amazonaws.com/1_f72f52a617.jpg)
  
What’s important to note is that all tools inside SageMaker Studio are easy to use from the console; it needs to be quick and understandable, especially for users that are not developers. Why is this important?
  
Business owners often go through a dilemma when they need to hire a data scientists or engage their own company in data science projects. They are assessing if they are good candidates for such projects. Would it be beneficial for them to join, what are possible positive or negative side effects? Sometimes they just need more understanding of the whole process to be able to mitigate the possible risks. While all these questions could be answered by the data scientists estimating the situation and analyzing available data, it is useful to have an accessible toolkit that showcases the potential and suitability of a data science project. Utilizing some of the tools available in SageMaker studio is intuitive, even for inexperienced users.
  
This blog will show the tools available in SageMaker Studio and deep-dive into SageMaker AutoPilot, a tool to solve or speed up the biggest part of all data science projects – data preparation and feature engineering.
  
## SageMaker Studio General Tools
  
First, let’s look at the general features of SageMaker Studio Tools:
  
- Notebooks
- Debugger
- Experiments
- Model Monitor
  
Some of the tools available in SageMaker Studio are new, while some are improvements of existing tools.
  
**SageMaker Notebooks** are like Jupyter notebooks in look and feel. They are among the first choices of data scientists when beginning a project, since they offer an option to track code and notes at the same time. However, AWS Notebooks has provided much more – Studio delivers single-click notebooks for the SageMaker environment. The underlying compute resources are fully elastic and integrated with other AWS services, so it’s easy to add or remove additional resources without interrupting work. AWS has had SageMaker Notebooks in their catalog since 2018, but with the release of SageMaker Studio, usage has been simplified and collaboration has been improved as multiple users can interact with the notebooks simultaneously.
  
**SageMaker Debugger** is made to simplify the debugging process. This feature is expected to improve over time. AWS provides in-depth explanations, including code snippets showing how the tool can help developers debug Tensorflow bugs. This feature is probably the most important step to have ML processes automated. The hardest parts of any ML process are preparation and debugging, so this feature is quite welcomed by data scientists, whether debugging fully trained or new models.
  
**SageMaker Experiments** are collections of trials – whether we’re referring to a group of different methods that are applied to the same problem or other similar training jobs. This is handy since you often have no way of knowing how long a job will continue to run, or if it has silently crashed in the background. The Experiments feature should be a useful addition for cloud-based jobs, large data sets, or GPU-intensive projects.
  
**SageMaker Model Monitor** is one of the most important features after the post-deployment phase. Even after a model is put into production, the work is not completely done. Model monitoring is necessary because a model’s performance may worsen over time due to changing trends in the input data or unexpected problems. Model Monitor helps alert model maintainers about input data drift, and consequently monitors the model’s performance. A system that could alert model maintainers to these changes automatically is valuable because it mitigates the consequences of the data change and lowers the cost of post deployment in the long run. That way, data scientists on the team can focus on other important things. Considering all this, SageMaker Monitor presents a clear benefit of standardizing model hosting on SageMaker Endpoints.
  
## SageMaker AutoPilot section
  
**SageMaker AutoPilot** is part of the AutoML category, which automatically trains ML models from CSV data files. The AutoPilot is a great step forward in the AWS’s attempt to accelerate the most tedious part of ML development – the exploratory analysis or preparation phase. Approximately 70% – 90% of the work on ML projects is dedicated to cleaning and preparing the data in order to be optimally used by ML algorithms. Considering this, every tool that can lower the mentioned percentage and amount of time spent on the exploratory analysis will bring a reduction in the costs, and contribute to the faster solution creation, which again provides data scientists with more time to concentrate on something else.
  
![2.jpg](https://iolap-com-strapi-cms-upload.s3.amazonaws.com/2_db9476816f.jpg)
  
At the time of release, AWS SageMaker Studio was available only in the US East (Ohio) region, but now it is also available in S. East (N. Virginia), US West (Oregon), and Europe (Ireland) and in time we expect its availability to spread to even more regions.
  
Here we will show how to start with Studio from the console and provide some useful links for official and more detailed documentation. An AWS SSO or IAM account is required to sign into AWS Studio – starting through the console is the easiest way.
  
![3.png](https://iolap-com-strapi-cms-upload.s3.amazonaws.com/3_f3fdc50164.png)
  
This is the SageMaker Studio GUI after opening the Studio.
  
![4.png](https://iolap-com-strapi-cms-upload.s3.amazonaws.com/4_e933db9abf.png)
  
Here it’s possible to click one button to open a new notebook and start deployment of the model or an AutoPilot experiment.
  
![5.png](https://iolap-com-strapi-cms-upload.s3.amazonaws.com/5_611b3411bf.png)
  
To proceed with the auto pilot experiment, the user needs to enter the name, S3 bucket where the data in csv format is located, and where the results will be stored. The user also needs to define the target they want to classify or predict on. It’s important to choose between different types of ML problems, for now the choice is between classification or regression, but in time AWS will add more options. If unsure, the user can leave Auto option selected and SageMaker studio should recognize the problem it’s facing there. Choosing between having a complete experiment or not, the user decides if they want to have an optimized model after its completion or just a general idea what models are best to try out, given the data. The second option is shorter in terms of execution but doesn’t provide the tuned algorithm.
  
After the setup, AutoPilot starts the process of data analysis, feature engineering, and model tuning if a complete experiment is chosen in the previous step. Data analysis and feature engineering are the most time-consuming steps in the ML workflow, so having those two solved automatically allows data scientists to spend more time on more complex steps. For now, it’s only necessary to have the data ready in CSV format with the recommendation that it contains some dose of structure – i.e. if data is categorical, having different spelling for the same thing is not optimal – “red shirt”, “shirt red”, “red shirts” will be recognized as different categories by the AutoPilot even though they present the same category, due to the non-standardized naming.
  
After the first step of data analysis is finished, the user gets two notebooks generated as information.
  
![6.png](https://iolap-com-strapi-cms-upload.s3.amazonaws.com/6_9d66341fe6.png)
  
The first one is a Data Exploration notebook, and the second one is a candidate generation notebook.
  
The Data Exploration notebook provides insights about the dataset (in our example we use customerTargeting). The notebook is informative and gives ideas on what should be done to optimize the future ML workflow and data usage, starting with “what’s the percentage of missing values” to the more complex “some data seems to be outliers”.
  
![7.png](https://iolap-com-strapi-cms-upload.s3.amazonaws.com/7_0f138d441d.png)
  
The second notebook created for the user by AutoPilot is “SageMaker Autopilot Candidate Definition”. This notebook provides info on the suggested workflow together with the code definition and descriptions, which is helpful if the user doesn’t have much experience with the suggested models.
  

At the Model tuning step, it tries out multiple models and hyperparameters in order to get the best score on different metrics. This step will take more time, but again, it’s not required for the user to know anything about the code that runs in the background. Once finished, it’s possible to click on “Deploy model”.
  
![8.png](https://iolap-com-strapi-cms-upload.s3.amazonaws.com/8_362481fdf1.png)
  
## Conclusion

To conclude, AWS SageMaker Studio provides features that guide the user from end to end in the ML workflow in just a few clicks, while the user doesn’t need to know much of the coding. This approach could be a great starting point for familiarizing the user with the data and the modeling options. It’s a great asset for business users who want to understand the main principles, or data science teams who are starting a project and want to speed up the first phases in order to quickly get to innovative solutions.
  
To see more about topics mentioned in this blog post, stay tuned for the Part 2 of this series, where we will deep-dive into more of Sagemaker’s Studio features and walk through some real use cases.
  
For more details on AWS SageMaker Studio please review the official documentation: https://docs.aws.amazon.com/sagemaker/latest/dg/studio.html


As a fully integrated development environment for machine learning, SageMaker Studio allows storage and collection of all the development facets users need in one place.

What is SageMaker Studio?

large_blogDarkBlue.jpg

medium_blogDarkBlue.jpg

small_blogDarkBlue.jpg

thumbnail_blogDarkBlue.jpg

blogDarkBlue.jpg

large_6-scaled.jpg

medium_6-scaled.jpg

small_6-scaled.jpg

thumbnail_6-scaled.jpg

6-scaled.jpg

[Beyond natural language processing, harnessing voice as a user interface in healthcare](https://medcitynews.com/2018/02/beyond-natural-language-processing-harnessing-voice-user-interface-healthcare/)
  
By [STEPHANIE BAUM](https://medcitynews.com/author/sbaum/)
  
![voice.jpg](https://iolap-com-strapi-cms-upload.s3.amazonaws.com/voice_ca111dbbeb.jpg)
  
Its collaboration with Amazon Web Services on enterprise voice technology is helping to give Dallas-based analytics technology business iOLAP a higher profile than it’s had before in the healthcare space. Cofounder and CEO Chris Jordan talked about the applications for voice in healthcare settings and a new partnership with a health system to bring voice tech into the OR in a phone interview.
  
Although the idea is to support a hands-free environment and limit touch in a sensitive setting like an operating room, similar to what smart glasses were intended to do, voice in healthcare can span simple tasks such as turning lights on and off to requesting specific information for clinical decision support.
  
Jordan said his company began working with Amazon’s ALEXA in 2015 and since then has been developing proof-of-concept and use cases for clients where voice in healthcare can have an impact and drive efficiencies. It also works in other industries beyond healthcare such as energy, retail, and law enforcement.
  
> “We are talking about a new paradigm for interacting with systems,” Jordan said. “Voice is going to quickly start to be used for things that, without it, are difficult. I believe it will become the preferred way for people to interact with their system.”
  
One example Jordan used is procedure checklists for surgery prep, so that assistants could assemble OR tools according to the surgeon’s needs and preferences. It can also help physicians call up images or video they need to view in an OR or other healthcare settings.
  
Another benefit of voice technology is that it can allow people to keep their eyes on the task at hand and still access supplemental information they need. Although Jordan cites an example of a tool it is developing for law enforcement, that rationale could easily apply to the healthcare industry as well.
  
The company’s technology can also be used to support voice messages sent through a chatbot, such as requesting a physician to see a patient in a particular room, checking a calendar, and other self-service information requests.
  
Beyond Amazon, it is collaborating with a medical device manufacturer and a startup on an AI-enabled tool to bring voice to the ER. Although Jordan said iOLAP is developing voice tech applications with technology companies as well as healthcare organizations, he declined to name them.
  
Other companies see applications across [diabetes management,](https://medcitynews.com/2017/09/amazon-alexa-future-diabetes-management-support/) [medication adherence](http://www.prweb.com/releases/2018/02/prweb15243799.htm) and [supporting independent living](https://mhealthintelligence.com/news/how-one-home-health-provider-turned-alexa-into-an-mhealth-assistant), among some examples.
  
Just a few years ago, hands-free clinical decision support was moving in the direction of smart glasses. Now voice-enabled technology is a trend. It will be interesting to see whether it gains enough momentum to break through into the mainstream of healthcare or becomes just a short-term trend.

Applications for voice in healthcare settings and a new partnership with a health system to bring voice tech into the OR in a phone interview.

Beyond natural language processing, harnessing voice as a user interface in healthcare

large_chatGPT-blog-pic.jpg

medium_chatGPT-blog-pic.jpg

small_chatGPT-blog-pic.jpg

thumbnail_chatGPT-blog-pic.jpg

chatGPT-blog-pic.jpg

Data & Analytics

## Introduction  

Since the release of [ChatGPT](https://chat.openai.com/) it has been the number one topic of conversation in the AI/ML community and the feedback has been overwhelming with good reason.  

Lauded as a real threat to Google’s business, its capabilities seem endless. From writing college essays to assisting programmers, it responds to all kinds of queries concisely and informatively.  

As it has been shown to resolve any inconveniences in the user’s life so far, we decided to test it on something troubling our accounting department. Is there a way for ChatGPT to give them a helping hand with an employee’s PTO requests?  

Specifically, we are interested in employees who have requested PTO for ten or more consecutive days. But to make things spicy, we are only looking for intervals that have ten or more workdays, excluding the weekends and national holidays.   
  

## SQL problem “Islands and Gaps”  

“Islands and Gaps” are a classic problem in SQL that involves finding ranges of existing values. The *gap* problem focuses on finding the ranges of missing values, whereas solving the *island* problem involves finding the ranges of existing values.  

In a simple example, let’s see the problem in action to understand it better. Suppose we have a table column with sorted values 1, 2, 5, 6, 7, 10, 11, 12, 14, and 15. Data gaps exist because values are missing: (3, 4), (8, 9), and 13. Data islands occur as data gaps separate data groups. In the example, data islands are (1, 2), (5, 6, 7), (10, 11, 12), and (14, 15).    

The problem may also arise when dealing with temporal data when there is a need to draw answers from periods of activity or inactivity. For example, how frequently has someone logged into a website? Or, how often did the server go offline?   

In our case, the accounting department is facing a real-life example of the gaps and islands problem, focusing on the latter. We want Chatty, short for ChatGPT, to identify PTO islands - groups of continuous sequences that depict consecutive days a person has requested as time off.  

To track Chatty’s progress and check if the returned query makes sense at each step, we set up a [PostgreSQL](https://www.postgresql.org/) table, “time_off_table”, that tracks the first six months of 2022. To keep things simple, we only have one person, “John Doe”, with a couple of short and long periods of request PTO.  
  
 
## Conversation with Chatty  
  
While researching the chat’s ability to understand us and return a meaningful answer, our own linguistic and descriptive skills are also put to the test. The plan is to start with introductory- high-level questions and make our way to the end goal, a query we can proudly present to our accountant. With each interaction, we aim to steer the conversation in the right direction by gradually introducing restrictions on our initial inquiry.   

The paragraph below shows our conversation from start to finish and how we applied the chat's suggestion to our data. As we went through all nine steps, some responses we received worked perfectly. In contrast, others needed corrections or additional modifications to work fully. The final result, which meets all our criteria, is also the last step, but we encourage you to try all steps by yourself.  

Firstly, we started with a simple question to check if Chatty is aware of the SQL problem of islands and gaps.  

**User:** Can you explain why the SQL islands and gaps problem occurs?  

**Chatty:** The "SQL islands and gaps" problem occurs when data is missing or split into non-contiguous intervals, resulting in "islands" of data surrounded by "gaps" of missing values. This can happen in cases where the data is collected or recorded in an inconsistent manner, or when data is deleted or lost. In a relational database, this can lead to inaccuracies in results and unexpected behavior when querying the data. To resolve this problem, data cleaning and data normalization techniques can be applied to ensure data is complete and consistent, or a more robust data collection process can be implemented to prevent the issue from occurring in the first place.  

Great! We can present our problem of tracking employees’ PTO as detecting islands in our data. We can then politely ask for an SQL query that does that for us.  

**User:** If I give you a description of a table, could you give me a SQL query that detects "islands" in data?  

**Chatty:** Sure! Can you please provide me with a description of the table and the criteria for defining an "island"?  

For example, if the table has columns "id", "start_date", and "end_date" and an "island" is defined as a gap in the data of more than 1 day between two consecutive rows with the same "id", a SQL query to detect the "islands" could be:  

```
WITH cte AS (
  SELECT id, start_date, end_date,
         LAG(end_date) OVER (PARTITION BY id ORDER BY start_date) AS prev_end_date
  FROM table_name
)
SELECT id, start_date, end_date
FROM cte
WHERE prev_end_date IS NULL OR start_date > prev_end_date + INTERVAL '1 day'
```
  

This query uses a Common Table Expression (CTE) to find the end date of the previous row with the same "id". The final result only includes the rows where the previous end date is missing or the difference between the start date of the current row and the end date of the previous row is more than 1 day, indicating an "island" in the data.  

So far, so good. Unfortunately, we have our data in a slightly different format. Our next step is to describe as best as possible the table and see if it can manage to return duration in the same manner as before.  

**User:** Please give me a SQL query that finds the duration of the employee's PTO. For example, assume the table "time_off_table" describes how employees took their time off in the past year. The table has three columns. The first column is called "employee_name" and contains a string value representing the employee's name. The second column is called "day_of_year" and includes a timestamp value representing a day in a year. Lastly, the third column, called "PTO", contains an integer value set to 1 if the person in question was on PTO that day and set to 0 if the person was not on PTO that day. Can you give me a SQL query that returns the start date, end date, and the duration of an interval of consecutive days the person was on PTO?  

**Chatty:** Yes, sure! Here's a SQL query that returns the start date, end date, and the duration of an interval of consecutive days the person was on PTO:  

```
WITH cte AS (
  SELECT 
    employee_name,
    day_of_year,
    PTO,
    SUM(PTO) OVER (PARTITION BY employee_name ORDER BY day_of_year) as grp
  FROM 
    time_off_table
)
SELECT 
  employee_name,
  MIN(day_of_year) as start_date,
  MAX(day_of_year) as end_date,
  COUNT(day_of_year) as duration
FROM 
  cte
WHERE 
  PTO = 1
GROUP BY 
  employee_name, grp
ORDER BY 
  employee_name, start_date;
```  

The query uses a Common Table Expression (CTE) to assign a group number to consecutive PTO days for each employee. The main query then selects the start date, end date, and duration of each group.  

![center-big](https://iolap-com-strapi-cms-upload.s3.amazonaws.com/1_table_58f857d122.png)

 
Not really satisfied with the result. Maybe we can suggest modifying the query, so we get consecutive days?  

**User:** This query returns each day as its own interval. Can you give me a query that returns intervals of consecutive days the person was on PTO?  

**Chatty:** Sure, you can modify the query to return intervals of consecutive days by grouping the CTE results by the employee name and the group number assigned to consecutive PTO days:  
  
```
WITH cte AS (
  SELECT 
    employee_name,
    day_of_year,
    PTO,
    SUM(CASE WHEN PTO = 0 THEN 1 ELSE 0 END) 
      OVER (PARTITION BY employee_name ORDER BY day_of_year) as grp
  FROM 
    time_off_table
)
SELECT 
  employee_name,
  MIN(day_of_year) as start_date,
  MAX(day_of_year) as end_date,
  COUNT(day_of_year) as duration
FROM 
  cte
WHERE 
  PTO = 1
GROUP BY 
  employee_name, grp
ORDER BY 
  employee_name, start_date;
```
  
This query will return intervals of consecutive days that an employee was on PTO by grouping the CTE results based on the group number assigned to consecutive PTO days.  
  
![center-big](https://iolap-com-strapi-cms-upload.s3.amazonaws.com/2_table_2b950d528b.png)


 
Now we are talking! As mentioned before, our goal is to get the list of employees whose duration is equal to or exceeds 10 days, so that is our following guidance for Chatty.  

**User:** This is great! Thank you. Could you modify the query where the duration is 10 or more days?  

**Chatty:** Sure! You can modify the query to only return intervals with a duration of 10 or more days by adding a HAVING clause to the main query:  
  
```
WITH cte AS (
  SELECT 
    employee_name,
    day_of_year,
    PTO,
    SUM(CASE WHEN PTO = 0 THEN 1 ELSE 0 END) 
      OVER (PARTITION BY employee_name ORDER BY day_of_year) as grp
  FROM 
    time_off_table
)
SELECT 
  employee_name,
  MIN(day_of_year) as start_date,
  MAX(day_of_year) as end_date,
  COUNT(day_of_year) as duration
FROM 
  cte
WHERE 
  PTO = 1
GROUP BY 
  employee_name, grp
HAVING 
  COUNT(day_of_year) >= 10
ORDER BY 
  employee_name, start_date;
```
  
This query will only return intervals of consecutive days with a duration of 10 or more days.  

![center-big](https://iolap-com-strapi-cms-upload.s3.amazonaws.com/3_table_bcac353583.png)


 
The catch is not to count weekends as part of someone’s PTO. We are only interested if the requested time off has 10 or more strictly workdays. Let’s see if Chatty can modify the query to fulfill our condition.  

**User:** Super! Thank you. By applying this query, I get employees and their start and end days for PTO lasting 10 days or more, but those periods can contain weekends. Can you modify the query to return employees whose PTO contains 10 or more strictly work days?  

**Chatty:** To return intervals of consecutive work days with a duration of 10 or more, you can first create a table that contains a list of work days. You can then join this table with the time_off_table to only include work days in the calculation of duration.  

Here's an example query that implements this logic:  

```
WITH workdays AS (
  SELECT 
    day_of_year
  FROM 
    calendar_table
  WHERE 
    is_weekend = 0
),
cte AS (
  SELECT 
    employee_name,
    day_of_year,
    PTO,
    SUM(CASE WHEN PTO = 0 THEN 1 ELSE 0 END) 
      OVER (PARTITION BY employee_name ORDER BY day_of_year) as grp
  FROM 
    time_off_table t
    JOIN workdays w ON t.day_of_year = w.day_of_year
)
SELECT 
  employee_name,
  MIN(day_of_year) as start_date,
  MAX(day_of_year) as end_date,
  COUNT(day_of_year) as duration
FROM 
  cte
WHERE 
  PTO = 1
GROUP BY 
  employee_name, grp
HAVING 
  COUNT(day_of_year) >= 10
ORDER BY 
  employee_name, start_date;
```
  
The workdays CTE is used to create a table of work days based on a calendar_table that you would need to create beforehand. The time_off_table is then joined with this workdays table so that only workdays are considered in the calculation of duration. The query then calculates the duration of PTO intervals as in the previous examples.  

It seems a bit messy to introduce an additional table just to track workdays. We would like to find a way to detect if it is a working day or not, straight out of date. Hopefully, Chatty is okay with us trying to reduce our scope of work entirely.  

**User:** I am not able to create a table containing work days. Can you modify the query to check the work days from a date?  

**Chatty:** Sure, you can modify the query to check the work days from a specific date. To do this, you can use a subquery in the WHERE clause of the main query that calculates the number of weekdays between a given date and each day in the time_off_table.
  
Here's an example:  

```
WITH cte AS (
  SELECT 
    employee_name,
    day_of_year,
    PTO,
    SUM(CASE WHEN PTO = 0 THEN 1 ELSE 0 END) 
      OVER (PARTITION BY employee_name ORDER BY day_of_year) as grp
  FROM 
    time_off_table t
    WHERE 
      (date_part('dow', day_of_year) NOT IN (0, 6) AND 
      day_of_year >= (SELECT '2022-01-01'::date + (10 || ' work days')::interval))
)
SELECT 
  employee_name,
  MIN(day_of_year) as start_date,
  MAX(day_of_year) as end_date,
  COUNT(day_of_year) as duration
FROM 
  cte
WHERE 
  PTO = 1
GROUP BY 
  employee_name, grp
HAVING 
  COUNT(day_of_year) >= 10
ORDER BY 
  employee_name, start_date;
```
  
This time, the subquery in the WHERE clause calculates the number of weekdays (excluding weekends) between a given date and each day in the time_off_table. The example uses the date '2022-01-01' and adds 10 weekdays to it to obtain the minimum date that is considered in the calculation of duration. You can modify this date as needed to suit your requirements.  

By double-checking the returned query, we can see that it will result in an error, as there is a mashup of integer and interval values. Does “I guess we are all humans” apply to this scenario?  

Anyways, we need to let Chatty know.  

**User:** Thank you! Unfortunately, I get an error saying there is an invalid input syntax for type interval '10 work days'. Can you correct the query?  

**Chatty:** Yes, my apologies for that. The syntax is incorrect. Here's the corrected query:  

```
WITH cte AS (
  SELECT 
    employee_name,
    day_of_year,
    PTO,
    SUM(CASE WHEN PTO = 0 THEN 1 ELSE 0 END) 
      OVER (PARTITION BY employee_name ORDER BY day_of_year) as grp
  FROM 
    time_off_table t
    WHERE 
      (date_part('dow', day_of_year) NOT IN (0, 6) AND 
      day_of_year >= ('2022-01-01'::date + (10 * INTERVAL '1 day')))
)
SELECT 
  employee_name,
  MIN(day_of_year) as start_date,
  MAX(day_of_year) as end_date,
  COUNT(day_of_year) as duration
FROM 
  cte
WHERE 
  PTO = 1
GROUP BY 
  employee_name, grp
HAVING 
  COUNT(day_of_year) >= 10
ORDER BY 
  employee_name, start_date;
```
  
In this corrected query, the 10 * INTERVAL '1 day' expression is used to calculate 10 days from the starting date '2022-01-01'.  

![center-big](https://iolap-com-strapi-cms-upload.s3.amazonaws.com/6_table_f6b5d38476.png)
 
So, after that little hiccup, it looks like we are coming close to a solution! Just as we do not want to include weekends, we want to skip national holidays, as those days are already non-working days.   

**User:** Great! Thank you so much! Could you modify the query to exclude national holidays from the calculation?  

**Chatty: **To exclude national holidays from the calculation, you need to create a table that contains the dates of all national holidays. You can then join this table with the time_off_table and exclude the holiday dates in the calculation.  

Here's an example:  
  
```
WITH cte AS (
  SELECT 
    t.employee_name,
    t.day_of_year,
    t.PTO,
    SUM(CASE WHEN t.PTO = 0 THEN 1 ELSE 0 END) 
      OVER (PARTITION BY t.employee_name ORDER BY t.day_of_year) as grp
  FROM 
    time_off_table t
    LEFT JOIN holidays h
      ON t.day_of_year = h.holiday_date
    WHERE 
      (date_part('dow', t.day_of_year) NOT IN (0, 6) AND 
      t.day_of_year >= ('2022-01-01'::date + (10 * INTERVAL '1 day'))) AND
      h.holiday_date IS NULL
)
SELECT 
  employee_name,
  MIN(day_of_year) as start_date,
  MAX(day_of_year) as end_date,
  COUNT(day_of_year) as duration
FROM 
  cte
WHERE 
  PTO = 1
GROUP BY 
  employee_name, grp
HAVING 
  COUNT(day_of_year) >= 10
ORDER BY 
 	 employee_name, start_date;
```
  
In the final query, the holidays table contains the dates of all national holidays. The LEFT JOIN between the time_off_table and the holidays table will exclude the holiday dates in the calculation. The WHERE clause in the subquery cte includes a condition that only includes the dates where the holiday_date is NULL, effectively excluding the national holidays.
  
![center-big](https://iolap-com-strapi-cms-upload.s3.amazonaws.com/7_table_0128a73f2f.png)

 
In our case, we created a dramatic, dreamy “holidays” table that treats every day in March as a national holiday. We can see that the remaining requested period was successfully narrowed down to February dates.    

  
## Findings 

Not to sugarcoat everything - ChatGPT needed a nudge in the right direction to work in the desired way. The first time, it produced durations that overlapped or just did not make sense for longer intervals. The second time, it had trouble interpreting the PTO flag, and everything went downhill.  

We realized that the input we provide has a great impact on how our intention is interpreted and how the flow goes on from that moment on. Moreover, while it can understand almost everything we ask, it has a limited attention span. We needed to find an optimal way not to overflow it with new information, so it knows our next query is a continuation of the previous one.  

Another setback was directly connected to the fact current wave of massive hype surrounding ChatGPT. The chat’s availability and speed differed during the day. There were moments when we had to wait for the query a long time, ultimately stopping the conversation and continuing sometime later.    

On the other hand, it should get the recognition it deserves. The process of testing the chat’s possibilities was indeed fun. It was interesting to interact with it, both in a conversational sense and by seeing its technical capabilities.  

It showed a vast knowledge of SQL syntax. For example, it proved it could switch the syntax from MS Server to PostgreSQL with ease. Moreover, it understood was able to handle any terminology thrown at it. It corrected itself every single time there was an error. Not only that, but it showed the capability to correct the query even without us mentioning the cause of the error.   
  

## Conclusion 

Have you tried communicating with ChatGPT on your own? Even if you’re not looking to generate content for personal use, you will still be blown away by its capabilities. We encourage everyone to give it a try in their spare time. In the meantime, we will surely occupy ourselves by playing around.  

If you find this reading inspiring, and you want us to cover another topic, [let us know.](https://iolap.com/contact/) Already thinking about saving time by automating tasks such as scheduling meetings? Or maybe requesting personalized recommendations based on our preferences? [iOLAP’s Machine Learning department](https://iolap.com/offering/ai-and-machine-learning/) is very excited to see how tools like this can be utilized in our everyday lives. 


Talk with ChatGPT

ChatGPT has been a number one topic since its release to the public. And the feedback on it has been overwhelming, with a reason.

AI & Machine Learning

Strategy & Discovery

Business Analysis

Process Design

Machine Learning Development

ML Ops

Testing & QA

Post Go-Live Support

Managed Services

We Build With

We Build On

Case Studies

Dickey's Barbecue Pit

Whataburger

Lucia Health Guidelines

Articles

What is SageMaker Studio?

Beyond natural language processing, harnessing voice as a user interface in healthcare

Talk with ChatGPT

Drive Your Business Forward!