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;

GROQ Query: Retrieve Documents by Highest Reference Count In this article, we will explore how to use Sanity’s GROQ query language to retrieve documents with the highest reference count. This involves understanding the basics of GROQ and how to construct queries that filter data based on complex conditions. Understanding GROQ Basics GROQ is a powerful query language used in Sanity to interact with your documents. It allows you to filter, sort, and transform data using a simple syntax.

Analysing Multiple CSV Files in R: A Step-by-Step Guide =========================================================== In this article, we will explore how to analyze multiple CSV files imported into R. We will cover the steps involved in reading and processing these files, as well as some common issues that may arise during analysis. Introduction R is a popular programming language for statistical computing and graphics. One of its strengths is its ability to easily import and manipulate data from various file formats, including CSV (Comma Separated Values).

Understanding the SUM Function in SQL The Problem at Hand In this blog post, we’ll explore a common phenomenon in SQL queries where the SUM function seems to only return individual results instead of aggregating multiple rows into a single value. The query provided by the Stack Overflow user appears to be attempting to calculate the total amount for a specific account number and date range. However, despite correctly grouping the data by various columns, the SUM function is not producing the expected aggregated result.

Understanding the Error: ‘configure’ Exists but is Not Executable The error message “‘configure’ exists but is not executable” can be a puzzling issue for users of Linux distributions, particularly Ubuntu, Linux Mint, and Debian. In this article, we will delve into the causes of this error, explore its consequences, and provide solutions to resolve it. Causes of the Error The “R Installation and Administration Manual” explains that when you try to install packages using install.

Avoiding Data Show by List when Group By is Not Included in the Data When working with data, especially in SQL queries, it’s common to encounter situations where we need to group data and aggregate values. However, there are scenarios where we might see data displayed as a list instead of being grouped correctly. In this article, we’ll explore one such situation: when using GROUP BY without including all necessary columns.

Understanding Dapper SQL Joins Introduction Dapper is a popular .NET library for interacting with databases. One of its key features is the ability to perform SQL joins, which allow you to combine data from multiple tables in a single query. In this article, we’ll explore how to use Dapper to join two tables: Albums and Songs. The Problem Let’s assume we have two tables: Albums and Songs. We want to retrieve all albums that belong to the “Freedom” album, along with their corresponding songs.

Handling Row Numbers in SQL Queries with Grouping As we delve into the world of database management, one common requirement arises when working with grouped data: assigning unique row numbers to each row within a group. This can be achieved using various SQL techniques, including window functions and aggregations. In this article, we’ll explore how to achieve sequential row numbers for each group in a query. Understanding the Problem Suppose you’re working with a dataset that needs to be grouped by one or more columns, but you also require a unique identifier (row number) within each group.

Understanding the Difference Between seq() and sequence() in R As a newcomer to the world of R programming, it’s essential to grasp the fundamental concepts and syntax. One common question that arises is the difference between seq() and sequence() functions. In this article, we’ll delve into the details of these two functions, exploring their origins, usage, and implications on the output. Introduction to seq() and sequence() R is a powerful language for statistical computing and graphics.

Executing ‘If’ Statements in PhpMyAdmin ============================================== In this article, we will explore how to execute IF statements in PhpMyAdmin. We will delve into the differences between stored procedures and normal queries, and discuss how to use PHP’s if statement equivalents in a MySQL query. Understanding Stored Procedures vs Normal Queries When working with databases, you may come across two types of queries: stored procedures and normal queries. Stored procedures are pre-written blocks of SQL code that can be executed multiple times from within your application.