gaps-and-islands Questions
1
Solved
Given a simple table with the following data:
id | result | played
----+--------+------------
7 | L | 2012-01-07
6 | L | 2012-01-06
5 | L | 2012-01-05
4 | W | 2012-01-04
3 | W | 2012-01-03
...
Farrison asked 4/4, 2012 at 2:51
2
Solved
The idea is that say you have the following table.
-------------
| oID | Area|
-------------
| 1 | 5 |
| 2 | 2 |
| 3 | 3 |
| 5 | 3 |
| 6 | 4 |
| 7 | 5 |
-------------
If grouping by continuity i...
Hasdrubal asked 20/3, 2012 at 12:47
3
Solved
Purpose & What I've Got So Far
I am attempting to create a view which checks for missing labor transactions. The view will be fed to a Crystal report.
In this case, the view should take all d...
Tucci asked 6/2, 2012 at 20:33
4
I have the following table :
id study_start_time study_end_time
____________________________________________
1234 168 256
2345 175 233
1234 256 300
1234 300 389
2345 400 425
4567 200 225
ı wa...
Dibri asked 8/1, 2012 at 10:57
19
Solved
The following User History table contains one record for every day a given user has accessed a website (in a 24 hour UTC period). It has many thousands of records, but only one record per day per u...
Festoonery asked 24/7, 2009 at 6:21
3
Solved
I have a table:
create table table1 (event_id integer, event_time timestamp without time zone);
insert into table1 (event_id, event_time) values
(1, '2011-01-01 00:00:00'),
(2, '2011-01-01 00:00:1...
Megalocardia asked 23/11, 2011 at 17:41
3
Solved
Using PostgreSQL 9.0.
Let's say I have a table containing the fields: company, profession and year. I want to return a result which contains unique companies and professions, but aggregates (into ...
Avaunt asked 4/11, 2011 at 19:13
5
Solved
I have a database table which holds each user's checkins in cities. I need to know how many days a user has been in a city, and then, how many visits a user has made to a city (a visit consists of ...
Boater asked 17/8, 2011 at 13:32
3
Solved
I have a table with 2 columns of integers. The first column represents start index and the second column represents end index.
START END
1 8
9 13
14 20
20 25
30 42
42 49
60 67
Simple So far. Wha...
Vimineous asked 31/8, 2011 at 14:27
2
Solved
I have Work and Person tables (these are just examples to understand problem).
Structure
Work table
id INTEGER
person_id INTEGER
dt_from DATETIME
dt_to DATETIME
Person table
person_id INTEGER...
Churrigueresque asked 30/8, 2011 at 13:40
2
I've been working at this for a while. I was wondering how I could get this table:
id open_dt ops_hrs
1 10/31/2011 7:00AM - 5:30PM
2 11/1/2011 7:00AM - 5:00PM
3 11/2/2011 7:00AM - 5:00PM
4 11/3/20...
Kegler asked 17/8, 2011 at 21:16
4
Solved
I have a list of user login and logout stamps. Unfortunately a LOGIN entry might not always be followed by a LOGOUT entry.
I wish to delete any row which has the same [event] and [user_id] as previ...
Linseed asked 25/5, 2011 at 11:14
2
Solved
I am not quite sure how to go about doing this. Basically I have have a table like this
UserId DateRequested Approved ApprovedBy Notes
------------ ----------------------- -------- ----------- --...
Gasparo asked 23/5, 2011 at 15:20
7
Solved
Currently i have a table built up like this way
DeviceID Timestamp Value
----------------------------------------
Device1 1.1.2011 10:00:00 3
Device1 1.1.2011 10:00:01 4
Device1 1.1.2011 10:00:02 ...
Velda asked 16/5, 2011 at 13:51
5
I have a table which contains an ID and a Date for an event. Each row is for one date. I am trying to determine consecutive date ranges and consolidate output to show the ID,StartDate,EndDate
ID D...
Steenbok asked 5/5, 2011 at 16:54
3
Solved
Background (Input)
The Global Historical Climatology Network has flagged invalid or erroneous data in its collection of weather measurements. After removing these elements, there are swaths of dat...
Bloodletting asked 5/5, 2011 at 23:2
5
Solved
I know some basic SQL, but this one is beyond me. I have looked high and low but no dice. I need a view of the following data, I can do this in the application layer code. But unfortunately for thi...
Barbellate asked 14/4, 2011 at 11:30
3
Solved
What is a way to find gaps in a set of date spans?
For example, I have these date spans:
1/ 1/11 - 1/10/11
1/13/11 - 1/15/11
1/20/11 - 1/30/11
Then I have a start and end date of 1/7/11 and ...
Fairspoken asked 22/1, 2011 at 1:13
3
Solved
Say I have the following table:
MyTable
---------
| 1 | A |
| 2 | A |
| 3 | A |
| 4 | B |
| 5 | B |
| 6 | B |
| 7 | A |
| 8 | A |
---------
I need the sql query to output the following:
-------...
Bevins asked 1/12, 2010 at 13:2
2
Solved
I have a table of data that looks a bit like this:
Name StartTime FinishTime Work
Bob 2010-08-03 08:00:00 2010-08-03 12:00:00 4
Bob 2010-08-03 13:00:00 2010-08-03 16:00:00 3
Pete 2010-08-04 08:00:...
Filaria asked 31/8, 2010 at 13:55
2
Solved
I have a table with records for every consecutive hour. Each hour has some value. I want a T-SQL query to retrieve the missing records (missing hours, the gaps). So for the DDL below, I should get ...
Stickseed asked 22/4, 2010 at 17:50
3
Solved
I have mysql table called user(id, name, join_on) join on is a date field what I want is to show in each day how many uses has been created I can use group by but it will only give me the dates whe...
Bienne asked 16/4, 2010 at 7:15
2
Solved
I have a table of values like this
978412, 400
978813, 20
978834, 50
981001, 20
As you can see the second number when added to the first is 1 number before the next in the sequence. The last num...
Ruff asked 23/1, 2009 at 12:38
5
We are building a query to count the number of events per hour, per day. Most days there are hours that do not have any activity and therefore where the query is run the count of activities per hou...
Incredible asked 17/12, 2008 at 2:25
© 2022 - 2024 — McMap. All rights reserved.