WITH /* enumerate pairs */ cte1 AS ( SELECT ID, EventTime, ReaderNo, COUNT(CASE WHEN ReaderNo = 'In' THEN 1 END) OVER (PARTITION BY ID ORDER BY EventTime) pair FROM test ), /* divide by pairs */ cte2 AS ( SELECT ID, MIN(EventTime) starttime, MAX(EventTime) endtime FROM cte1 GROUP BY ID, pair ), /* get dates range */ cte3 AS ( SELECT CAST(MIN(EventTime) AS DATE) minDate, CAST(MAX(EventTime) AS DATE) maxDate FROM test), /* generate dates list */ cte4 AS ( SELECT minDate theDate FROM cte3 UNION ALL SELECT DATEADD(dd, 1, theDate) FROM cte3, cte4 WHERE theDate < maxDate ), /* add overlapped dates to pairs */ cte5 AS ( SELECT ID, starttime, endtime, theDate FROM cte2, cte4 WHERE theDate BETWEEN CAST(starttime AS DATE) AND CAST(endtime AS DATE) ), /* adjust borders */ cte6 AS ( SELECT ID, CASE WHEN starttime < theDate THEN theDate ELSE starttime END starttime, CASE WHEN CAST(endtime AS DATE) > theDate THEN DATEADD(dd, 1, theDate) ELSE endtime END endtime, theDate FROM cte5 ) /* calculate total minutes per date */ SELECT ID, theDate, SUM(DATEDIFF(mi, starttime, endtime)) workingminutes FROM cte6 GROUP BY ID, theDate ORDER BY 1,2;

Understanding ggplot2 geom_area() and its Connection Issue with Colors Beneath a Single Line ggplot2 is a powerful data visualization library in R that provides a wide range of geometric elements to create complex and informative plots. In this article, we will explore the geom_area() function in ggplot2, specifically focusing on an issue where adding colors beneath a single line using geom_area() produces unwanted connections between points. Background To understand this issue, let’s first review how geom_line() and geom_area() work in ggplot2.

Understanding IBM MobileFirst 7.0 and XCode 6.4 Build Issues IBM MobileFirst 7.0 is a mobile application platform that enables developers to create mobile applications for various platforms, including iOS, Android, and Windows. It provides a set of tools and features that simplify the development process and provide access to various IBM services. However, integrating IBM MobileFirst with XCode 6.4 can be challenging due to compatibility issues. Background XCode 6.4 is an integrated development environment (IDE) for macOS that allows developers to create, test, and deploy iOS applications.

Understanding libPusher: A Deep Dive into Adding Pusher Chat to Your iOS App Introduction In recent years, real-time communication and push notifications have become an essential aspect of modern applications. One popular choice for implementing these features is the Pusher chat app, which offers a robust platform for building scalable and reliable messaging solutions. In this article, we’ll explore how to integrate libPusher into your iOS project, covering the basics of the library, its usage, and common pitfalls.

Understanding and Resolving Errors with the dynGraph Package in R Introduction to dynGraph Package The dynGraph package is a powerful tool for data visualization, particularly useful when working with large datasets or complex relationships between variables. It allows users to create dynamic graphs that can be easily customized and shared. In this article, we will delve into the world of dynGraph, exploring its features, common pitfalls, and solutions to overcome errors.

SQL Subqueries and GROUP BY Clause: Understanding the Limitations Introduction In this article, we will delve into a common issue that arises when using subqueries with the GROUP BY clause in SQL. The problem is often referred to as “more than one row returned by a subquery used as an expression.” This can lead to unexpected results and errors in your queries. The question provided in the Stack Overflow post demonstrates this issue, where the author attempts to execute different queries based on the value of grafana_variable.

Introduction to Naming a Base R Plot (Map) Created Over Multiple Lines Understanding the Problem and Solution Overview In this article, we will delve into the world of base R plots and explore ways to name them, particularly those created using maps. We will examine how to overcome limitations with traditional plot naming methods and discover new approaches using the ggplotify and grid packages. Background: Base R Plotting and Map Creation Base R provides a wide range of plotting functions for creating various types of plots, including maps.

Finding number of r’s in the vector (Both R and r) before the first u Introduction In this post, we will explore a problem that involves finding the number of occurrences of ‘r’ or ‘R’ in a string before a specific character, ‘u’. We’ll use examples from the R programming language to illustrate our points. Problem Statement Given a vector of characters, rquote, which contains strings with both uppercase and lowercase letters, we want to find the number of ‘r’s (both uppercase and lowercase) that appear in each string before the first occurrence of the character ‘u’.

Understanding App Crashes in iOS Simulator with iPhone/iPod Compatibility Introduction As a developer, it’s not uncommon for your app to work seamlessly on an iPod or iPhone but crash when run on an iPad simulator. This phenomenon has puzzled many a developer, and understanding the underlying causes can be quite challenging. In this article, we’ll delve into the world of iOS development, explore potential reasons behind this issue, and discuss solutions to ensure compatibility across various iOS versions.

Understanding SQL Joins and Why They May Not Always Give You the Correct Totals As a data analyst or developer, it’s not uncommon to come across issues with SQL joins that seem to produce incorrect results. In this article, we’ll delve into the world of SQL joins and explore why they might not always give you the correct totals. What Are SQL Joins? Before we dive into the issues with SQL joins, let’s quickly define what a join is.