Closed. This question needs details or clarity. It is not currently accepting answers. Want to improve this question? Add details and clarify the problem by editing this post. Closed 2 years ago. Improve this question Need to calculate consecutive hours. Here is the data This the client has a limit of the amount of consecutive hours allowed. Here is the
Tag: gaps-and-islands
SQL Function to Look at near-by Intervals of data
I am trying to determine if there is some sort of SQL function that would allow me to look at nearby intervals of data to aggregate them into other aggregate functions. My current data looks something like the following and occurs every 5 minutes with a single entry per day for a given time period per entity. EntityA 20200201 00:10:00
SQL Group by date with multiple same results
How to write a statement that groups data by date but shows when data changed for the first time date – when data changed a,b,c – some data and it can be anything This is should be the result. It might have the same data on different dates, but it missing the next day when data stayed the same. It
Making groups of dates in SQL Server
I have a table contains ids and dates, I want to groups of dates for each id id date —————— 1 2019-01-01 2 2019-01-01 1 2019-01-02 2 2019-01-02 2 …
SQL clean History repeated intermediate values
I’ve noticed that my table has multiple redundant values that should be cleaned, it’s a table that records price changes, so I would like to clean the following way: To this: Also assume that in this case the column id is the same as date. SELECT DISTINCT ON (product, price) won’t work because it would ignore either the product 1
Running total with several conditions in bigquery
I need to calculate a running total but need to reset the total on a condition (when expected reached = 0 and product_group and product changes). Got help here without the two extra fields: Calculate a running total with a condition in BigQuery I have this table and may use product_group and product as integers or strings as below. Any
Using window function in redshift to aggregate conditionally
I have a table with following data: Link to test data: http://sqlfiddle.com/#!15/dce01/1/0 I want to aggregate the items column (using listagg) for each group in gid in sequence as specified by seq column based on the condition that aggregation ends when pid becomes 0 again for a group. i.e. for group g1, there would be 2 aggregations; 1 for seq
How to get time difference Duration totals grouped by Name and when Value changes in certain way?
I’m looking for the Sum of time value differences between rows where Value was initially 1 and is now 0, grouped by Name. Example data for a single Name, but there are many different Names in the real data. This data should return 11 minutes for Row 9–>7 transition and 5 minutes for Row 4–>2 transition; totaling 16 minutes for
Need to group records by consecutive date SQL
I’m building a query in order to build consecutive dates which is create table #consecutivedates ( sgid nvarchar(max), metric nvarchar(max), targetvalue nvarchar(max), startdate …
What is the best way in SQL to combine sequential events based on matching end time to start time?
That database I work in records events based on a part ID and the times in which it is active. The issue I came across is these events are truncated to fit within a single day. If the active time for a part carries over to the next day, the event will be split by the number of days it